Recently I have gone through with Hints and Locks in SQL server. While google about this topic I have read one blog where some query have been written which I am not bale to understand. Here it is
BOL states: Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. I have some trouble translating this. Does this mean that the update locks are released after the execution of the SELECT statement, unless the SELECT statement in within a transaction?
I other words, are my assumptions in the following 2 scenario's correct?
Scenario 1: no transaction
SELECT something FROM table WITH (UPDLOCK)
/* update locks released */
Scenario 2: with transaction
BEGIN TRANSACTION
SELECT something FROM table WITH (UPDLOCK)
/* some code, including an UPDATE */
COMMIT TRANSACTION
/* update locks released */
Example for scenario 2 (referred for stackoverflow blog)
BEGIN TRAN
SELECT Id FROM Table1 WITH (UPDLOCK)
WHERE AlertDate IS NULL;
UPDATE Table1 SET AlertDate = getutcdate()
WHERE AlertDate IS NULL;
COMMIT TRAN
Please help to understand the above query.
My second question is: once execution of select statement completed at same time UPDLOCK get released or not?