CODEWITHSUNDEEP
sql-server
Eric
Eric

Reputation: 2380

Can multiple people update the same record in SQL Server at the same time?

I have a SQL statement similar to the one below. Basically, it assigns an item to a user with an assigned status, if the item was in an unassigned status. If two people call this statement at exactly the same time, can they both update the same record with their user id, or does the database automatically lock the record as it is happening, because it is an update? I am currently not running the statement in any kind of transaction. Do I need to to ensure that only one person can set the row to assigned?

Update top (1) QUE.[Queue] set
                QueueStatusId = 2 -- set to assigned
                , QueueAssignedUserId = @QueueAssignedUserId
                , QueueAssignedDateTimeUTC = getutcdate()
from QUE.[Queue] updateq
where updateq.QueueStatusId = 1 -- only select unassigned

Update:

Here is a slightly closer representation of my code. You can see that I do not actually have a where clause in here to ensure that I only get items with a status of 1. Instead, I join to the table, taking into account the status and an assignment expiration date. Is it possible for that inner join query to be snapshotted, so that multiple users are joining off of a dirty copy of the data?

Update top (1) QUE.[Queue] set
                QueueStatusId = 2 -- set to assigned
                , QueueAssignedUserId = @QueueAssignedUserId
                , QueueAssignedDateTimeUTC = getutcdate()
from QUE.[Queue] updateq
inner join (select * from QUE.[Queue] 
    where QueueStatusId = 1 
        or (QueueStatusId = 2 and QueueAssignmentExpirationDateTimeUTC > getutcdate()) qStatuses 
    on qStatuses.QueueId = updateq.QueueId

Upvotes: 3

Views: 8902

Answers (1)

Brian Pressler
Brian Pressler

Reputation: 6713

Every statement is wrapped in an implicit transaction. So if the statement was executed twice at nearly the same time... SQL Server would pick one of them, execute the update, and then the second update execution would not be able to pick that record because it would have had it's QueueStatusId field changed in the first query.

The sub-query in your second version however can cause a race condition if not wrapped in an explicit transaction. It could be possible that two users both run the sub-query before either one updates and then both try to update the same record. You can structure your update without the sub-query and eliminate the possibility of a race condition:

Update top (1) QUE.[Queue]
set
    QueueStatusId = 2 -- set to assigned
    , QueueAssignedUserId = @QueueAssignedUserId
    , QueueAssignedDateTimeUTC = getutcdate()
from QUE.[Queue] updateq
where QueueStatusId = 1 
    or (QueueStatusId = 2 and QueueAssignmentExpirationDateTimeUTC > getutcdate())

This question also includes some helpful information and links: Transaction isolation levels and subqueries

Upvotes: 3

Related Questions