Looking at

update MyTable
set Status = 1
where Status = 0

of course- the where is calculated before the actual update.

but does this filtering (where Status = 0) is also inside the lock ?

I mean where is the lock ?

here : update ...

or here : filter and update...

  • 4
    An update lock will be taken for the first portion of the search operation. Then if/when data needs to be updated the update lock will be converted into an exclusive lock. Take a look at here to get more info on the update lock (sqlblog.com/blogs/kalen_delaney/archive/2009/11/13/…). – Thomas Stringer Dec 24 '12 at 14:33
  • 2
    @ThomasStringer why didn't you post this as answer? – bummi Dec 24 '12 at 23:26
  • I strongly suggest using DMV's to see for yourself what is really locked. start with this (inside a transaction) and then choose your columns of interest and filtering. {the code in the next comment}. – Roi Gavish Dec 26 '12 at 15:06
  • SELECT * FROM sys.dm_tran_locks AS DTL INNER JOIN sys.dm_exec_sessions AS DES ON DES.session_id = DTL.request_session_id WHERE DES.[status] != N'sleeping' AND DES.session_id > 50 AND DTL.request_owner_type != N'SHARED_TRANSACTION_WORKSPACE' – Roi Gavish Dec 26 '12 at 15:07

There are two stages to the lock of updated data. The first is an update lock (U) and the second, provided there is data that needs to be modified, is an exclusive lock (X). It's a two stage operation in the sense that there is data that needs to be searched in order to determine what/if data needs to be modified. The update lock will exist for that (or attempt to be attained for the duration of the operation). Then when data needs to be modified, the update lock will convert into an exclusive lock.

The reason behind this locking mechanism is to prevent deadlocking from the updating data scenario. Please see Kalen Delaney's post on more specifics surrounding this.

Take the below for example:

Execute this for connection 1...

-- connection 1, leaves an open tran to prevent connection 2 from progressing

use AdventureWorks2012;

begin tran

    update HumanResources.Department
    set name = 'This is a new name'
    where DepartmentID = 11;

--commit tran

Then in another session, attempt the following update:

use AdventureWorks2012;

update HumanResources.Department
set name = 'This is a conflicting tran'
where DepartmentID = 11;

What we have here is an update lock that is waiting because there is already an exclusive lock on the resource that the update lock is trying to get (an update lock is not compatible with an exclusive lock). The above scenario is used to "halt" the update lock conversion to exclusive lock.

You can see this by looking at the sys.dm_tran_locks DMV:

use AdventureWorks2012;

from sys.dm_tran_locks
where resource_database_id = db_id();

You should have similar results for the output of the above query:

enter image description here

  • But if the second query of yours got an update lock and now it is waiting for the first query to complete , and now ( after the first one is finished) - the where clause does not match any rows ? I mean : let's say that the first query of yours is update HumanResources.Department set name = 'This is a new name' , Departmentid=12 where DepartmentID = 11;.....so now the second query thinks it is going to update some rows(it's waiting to get execlusive lock !) , but now there arent any rows of departmentid=11.....will it scan the table it again ? – Royi Namir Dec 28 '12 at 13:30
  • Can you please answer my last comment – Royi Namir Dec 28 '12 at 18:18
  • What do you mean, "scan"? I think you might be thinking procedurally here. Can you explain what you perceive to be happening? – Thomas Stringer Dec 28 '12 at 23:14
  • "scan"= will it have to recalculate the where clause in the second query ? – Royi Namir Dec 29 '12 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.