SQL Server - Lock

  • Thread starter Thread starter Sylvain Lafontaine
  • Start date Start date
S

Sylvain Lafontaine

You should ask your question to the m.p.sqlserver.programming newsgroup.

S. L.
 
Hi,
I'm trying to lock a record with with(ROWLOCK) on a select clause.
Opening 2 differents SQL Query Analizer (QA1 y QA2) and trying with the
following query

on QA1

1- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2- BEGIN TRANSACTION aa
3- SELECT * FROM equipo WITH(ROWLOCK) WHERE equ_id = 134
4- UPDATE equipo
SET
equ_tecnico = 'a',
equ_tipo = 1
WHERE equ_id=134 AND equ_tipo = 0
5- COMMIT TRANSACTION aa


and on QA2

1- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2- BEGIN TRANSACTION aa
3- SELECT * FROM equipo WITH(ROWLOCK) WHERE equ_id = 134
4- UPDATE equipo
SET
equ_tecnico = 'b',
equ_tipo = 1
WHERE equ_id=134 AND equ_tipo = 0
5- COMMIT TRANSACTION aa

I'd readed that with(rowlock) block until the commit is performed for the
transaction but if I execute this queries step by step in both Query
Analizers (1 1, 2 2, 3 3, 4 4), when I execute the sentence 4 on QA2 I'm in
DeadLock. I'd tried with "with(HOLDLOCK, ROWLOCK)" and the same happend.

Why is that? Shouldn't be blocked the second transaction if the executios is
serializable and isolated until the first commit?
Thank's.,

jsaviola

Gracias,

jsaviola
 
Back
Top