2

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
Eric
  • 2,120
  • 1
  • 17
  • 34
  • It'd be very problematic for two processes to update a given resource, so there will be a lock for that record to prevent simultaneous updates from occurring. – Anthony Forloney May 13 '15 at 16:25
  • 2
    Do you realize that using top in an update means there is no way to know which row will be updated? This is not the way to isolate a single row. Take a look at this question here. http://stackoverflow.com/questions/20539095/update-top-1-record-in-table-sql-server – Sean Lange May 13 '15 at 16:29
  • Don't worry, in my real query I am updating into a temp table, so I know which one was updated. – Eric May 14 '15 at 18:25
  • an `update` is an atomic operation in most databases. You can have a zillion people firing off the `update`, but in the end, there will be only one update running at any given point in time. depending on the actual update, you may be trashing data. e.g. `update foo set bar=bar+1` wouldldn't "lose" any counts, since you'd always have the most recent value of `bar` to work with. but `update account set balance=5000` would lead to a million different balances flying around. – Marc B May 15 '15 at 18:18

1 Answers1

3

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

Community
  • 1
  • 1
Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • So, this is the answer I expected to see, but the problem is that I have an environment where this seems to not be what is happening. Is anyone aware of any configuration that would allow SQL Server to not behave this way? – Eric May 14 '15 at 23:30
  • Hmmm... that would be disturbing to me. Can you add to your answer what evidence you have that it's updating the record more than once? And maybe some more context to your code? – Brian Pressler May 15 '15 at 04:11
  • I updated my original post with some more details about my query. We have a situation where as many as 4 different users all end up working on the same item at the same time. We have been unable to find any way that the application could allow this, so we are down to examining this update query. – Eric May 15 '15 at 13:52
  • The addition of the sub-query is the issue. I've updated my answer. – Brian Pressler May 15 '15 at 18:09