Suppose there is a procedure that contains a query of the form:

UPDATE TOP (1) table SET SessionId=@SessionId Where SessionId is null

If this procedure will be run in parallel by several users, will there not be a collision in that the same user proapdeytit one and the same record or MS SQL server will take care that such a situation does not arise?

If this is the place to be, then how to avoid it?

1 answer 1

This design is thread safe.

or MS SQL server will take care that such a situation does not arise?

In short, yes. If one session (meaning SQL session) has selected an entry in the table for updating, then a parallel session cannot (due to the established U-lock session) select and update the same entry (if I understood correctly, this is what interested in). An example with explanations is below.

Let there is a table:

 CREATE TABLE LockTest ( Id int identity not NULL primary key, SessionId uniqueidentifier NULL );

Add some data to it:

 INSERT INTO LockTest (SessionId) VALUES (NULL), (NULL), (NULL), (NULL), (NULL);

Run the query a couple of times:

 DECLARE @sessionId uniqueidentifier = newid(); UPDATE TOP (1) LockTest SET SessionId = @sessionId WHERE SessionId IS NULL;

We will execute the update request for the third time by running the pre-profiler, and see which locks occur:

update top (1) locks

(for a complete understanding of what is happening, it is desirable, of course, to have an idea of ​​the modes , granularity and compatibility of locks).

Something like this happens. SqlServer scans the clustered index in search of a record for which the SessionId is NULL . In addition, for each record in turn:

  • U-lock on write is set (read with possible data change)
  • the SessionId IS NULL condition is SessionId IS NULL
  • if the entry does not satisfy the condition, then the lock is removed and the next entry takes place (in the screenshot it is Acquired - Released pairs of U-lock entries with the hash values ​​of the key (8194443284a0) and (61a06abd401c) )
  • if the entry satisfies the condition (entry with the key hash value (98ec012aa510) ), then the lock is raised to the exclusive (X-lock), the entry is updated, after which the locks are released

A parallel session will not be able to update the same record, since for this, it will also have to set the U-lock on the same entry as well. However, the two U-locks are incompatible, so the parallel session will have to wait until we update the record and / or remove the lock. At the same time, when the blocking by us is already removed, the SessionId the record will not be NULL . As a result, a parallel session will have to look for the next “free” entry.

The picture of what is happening may vary somewhat depending on the presence or absence of an index on the SessionId column. On whether lock-escalation will occur (for TOP (1) , probably, it should not). However, the nature of what is happening - setting the U-block on the candidate and releasing it, if the record did not fit, or converting to X-blocking, if the record is selected, followed by releasing, is most likely to be preserved.