4

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
  • 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/…). Dec 24, 2012 at 14:33
  • 2
    @ThomasStringer why didn't you post this as answer?
    – bummi
    Dec 24, 2012 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, 2012 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, 2012 at 15:07

1 Answer 1

6

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;
go


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;
go

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;
go

select
    resource_type,
    resource_description,
    resource_associated_entity_id,
    request_mode,
    request_status
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

4
  • 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, 2012 at 13:30
  • Can you please answer my last comment
    – Royi Namir
    Dec 28, 2012 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? Dec 28, 2012 at 23:14
  • "scan"= will it have to recalculate the where clause in the second query ?
    – Royi Namir
    Dec 29, 2012 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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