I got this question in an interview where the interviewer asked me to Define any three locks I used in my career and their practical purpose.

I answered him, I have used UPDLOCK,HOLDLOCK AND NOLOCK in my career and started elaborating as follows,

UPDLOCKS are associated with DML statements like Update. It is a shared lock and needs to be converted to an Exclusive lock before the original operation occurs. Also the lock occurs at row level. When a user executes an Update statement it assigns a Shared lock on all the scanned rows in the table, when it finds the row to be updated it converts it to an Exclusive lock and update operation occurs.

HOLDLOCKS are used at table level it prevents the table from being updated by any other DML transactions like an insert or an update. HOLDLOCKS are preferable to use in situations where we need only COMMITTED READS.

NOLOCKS are opposite of HOLDLOCKS and when they are issued at a table level, it allows the user to access/read the data from a table whilst some update/ insert operations are running at the back. NOLOCKS may produce UNCOMMITTED READS and are preferred to use at the Production Environments so that the real time update is not hindered.

The Interviewer asked, How do you see the current locks in a Server?

I told him I will use the Stored proc SP_LOCK

Then he asked me how will you kill the locks?

I answered, I will use KILL Session_ID command.

Then he asked me, Is there a report you can see the status of the Locks?

I answered him, There is one in the SSMS at database level, when you right click the database and view Reports.

He asked me, Whats its name?

I told him, I can’t think the name on top of my head but will be able to tell it when I open SSMS.

He said, Its “Top Transactions By Lock Count”

I was like Gosh!

#JunaithHaja