sp_who2, the life of a lock…

Locks are a incredible resource in a RDMS, but a nightmare where concurrency is high and lock retention is time consuming.

SQL Server let sysdba exploring locks using sp_who2 stored procedure.

EXEC sp_who2 return the list of active sessions and some information related to them, e.g. the related locks (who blocks and who is blocked).

Looking to the output, we have same interest for:

• SPID: is the process ID (Server Process ID), IDs less than 50 are system related and usually they not affect performances; IDs greater than 50 are user connections

• BlkBy: id the SPID in charge for the lock related current SPID; it means that current process is part of a chain in wait until BlkBy ID will release the lock;

• CPUTime: is the workload of the process, expressed in milliseconds;

• Disk IO: is the number of Bytes read or written from/to the disk;

When the SPID making the lock is identified, the following question is often: “What the hell is it doing?”

The answer can be found in DBCC INPUTBUFFER (<SPID>), that is able to display the SQL Statement in execution in the column named “EventInfo”.

Lets look to an example where we will create a lock with a couple of sessions:

In the first session I will run this code:

BEGIN TRANSACTION;
UPDATE AccessiUtente_Log SET AZIONE = 'Update' WHERE ID = 33;

In the second session I’ll run this code:

UPDATE AccessiUtente_Log SET AZIONE = 'Accesso' WHERE ID = 33;

The second command will stay suspended (locked) by the previous, waiting a commit or a rollback freeing the lock on record.

In a third session we can explore the lock info (who is locking my session):

Now we can go deeper on SPID 76 content (what the hell is SPID 76 doing?):