SQL Server deadlock clarity?

Question

After reading this interesting article I have some questions.

This table shows a deadlock situation :

T1 holds X lock on all rows with c1=5 on table t_lock1 while T2 holds X lock on all rows with C1=1 on table t_lock2.

Now each of these transactions wants to update the rows previously locked by the other. This results in a deadlock.

Question #1

• Do transactions obtain locks? I know that reading from table is done by a shared lock, and write to a table is done using an exclusive lock (I'm talking about the default locking settings).

So it seems from this example that transaction also holds a lock ....is it correct?

Question #2

...T1 holds X lock on all rows with c1=5 on table t_lock1...

• IMHO as I've said the locking is not per row (although it can be made, but the author didn't mentioned it) - so why does he say : on all rows with C1=5 ?

Answer 1

Do transactions obtain locks?

No. The statement that you execute - a SELECT or an UPDATE will acquire the locks. Depending on your transaction isolation level setting, the duration of how long the (shared) locks (for a reading SELECT) will be held differs - that's all. Shared locks normally are held only very briefly, while update and exclusive locks are held until the transaction ends. The transaction might hold the locks - but it's not the transaction that acquires the locks...

*...T1 holds X lock on all rows with c1=5 on table t_lock1...*
IMHO as I've said the locking is not per row ( although it can be made , but the author didn't mentioned it) so why does he say : on all rows with C1=5 ?

The locking is per row - by default. But why do you think there's only a single row with C1=5? There could be multiple - possibly thousands - and the UPDATE statement will lock all those rows affected by the UPDATE statement.