Sql server update query locking stages? 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...
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: