CODEWITHSUNDEEP
sqlsql-servertransactions
Tim Rudnevsky
Tim Rudnevsky

Reputation: 1294

SQL Server delete nowait doesn't work

I've got 3 tables:

Schema: enter image description here

For example, I have data like:

1> select id, iddep, idservice from transactions where id = 22
2> go
 id          iddep       idservice
 ----------- ----------- -----------
          22           6          12

I run the following queries, and result is predictable:

First connection queries:

1> begin tran
2> go
1> select id from transactions with (updlock) where id = 22
2> go
 id
 -----------
          22

Second connection queries:

1> begin tran
2> go
1> delete from transactions with (nowait) where id = 22
2> go
SQL Server Error: 1222 Lock request time out period exceeded

This is normal behavior for NOWAIT hint, what is described here

But! If I do the following queries, result is very strange!

First connection queries are the same as in the first example:

1> begin tran
2> go
1> select id from transactions with (updlock) where id = 22
2> go
 id
 -----------
          22

Second connection queries:

1> begin tran
2> go
1> delete from services with (nowait) where id = 12
2> go

I just try to delete parent row and.. Nothing happens! It just waits for the row to release in despite of nowait hint. And when I release that row, parent row deletes.

So, why I don't just receive the 1222 error, as in the first example?

Upvotes: 3

Views: 2757

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

It's right there in the page you link to, but maybe not obvious. NOWAIT:

Instructs the Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying SET LOCK_TIMEOUT 0 for a specific table.

Emphasis added

In the final case in your question, the DELETE isn't waiting for a lock on services (the table) - it's waiting for a lock on transactions so that it can verify that the foreign key constraint will not be violated.

And the same quote points out the way to solve it: Specify SET LOCK_TIMEOUT 0 on your second connection, and it won't wait for locks on any table.

Upvotes: 3

Related Questions