lock on select

Question

lock on select

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

Oliiii (10/4/2010)[hrIt looks like it knows that even though the record is held by an exclusive lock, it hasn't been modified yet so is not yet dirty and can then read it safely.
So i guess getting a lock is not a good trick to keep someone from doing a select.
I'll keep looking.

The issue is that readers don't take shared locks when running under a row-versioning isolation level. Since no shared lock is required, there's nothing to conflict with the exclusive lock.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 2

gheinze,

Can I ask *why* you want to block query 2? It seems an unusual requirement. Why would it be bad for query 2 to run without blocking in this scenario?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Oliiii SSCertifiable Points: 5328 More actions · Answer 3

Paul White NZ (10/4/2010)
Oliiii (10/4/2010)[hrIt looks like it knows that even though the record is held by an exclusive lock, it hasn't been modified yet so is not yet dirty and can then read it safely.
So i guess getting a lock is not a good trick to keep someone from doing a select.
I'll keep looking.
The issue is that readers don't take shared locks when running under a row-versioning isolation level. Since no shared lock is required, there's nothing to conflict with the exclusive lock.

I've ran my test on a DB without any row-versioning, the second query select still goes trough no problem even though there is an exclusive lock on the row (same thing using your queries).

Changing the second query to also use WITH (XLOCK) does the trick though.

abitguru Ten Centuries Points: 1170 More actions · Answer 4

Paul White NZ (10/4/2010)
gheinze (10/4/2010)
I tested this on a test Db with isolation level "read committed snapshot"
Right well that's different - now you are using the isolation level correctly.
The point of the row-versioning isolation levels (snapshot and read committed snapshot) is to allow readers to access data without taking Shared locks, which might block if someone else already holds an incompatible lock.
Because your query 2 is not taking shared locks, it won't block. There are a number of ways to workaround that - the most natural of which is to use a WITH (READCOMMITTEDLOCK) hint on query 2 so it does take shared locks. You would still need to take a incompatible lock in query 1 - XLOCK for example. (I misread your original question, so UPDLOCK is not what you need here.)
Example:
Query 1:
SELECT * FROM table_tp WITH (XLOCK) WHERE ID = 106435;
Query 2:
SELECT * FROM table_tp WITH (READCOMMITTEDLOCK) WHERE ID = 106435;

I´ll try this case,

Thanks!

abitguru Ten Centuries Points: 1170 More actions · Answer 5

Paul White NZ (10/4/2010)
gheinze,
Can I ask *why* you want to block query 2? It seems an unusual requirement. Why would it be bad for query 2 to run without blocking in this scenario?

Of course, I'll try to explainme the best way I could.

It would be bad for query 2 to run without blocking, because I do inserts in base of the results of query 1 and other queries.

If another query starts at the time when query 1 is running both queries get the same rows of the temporal table and I get duplicate rows.

1 - Query 1 selects data of table_tp

2 - In base of the results do other queries to find references in other tables,

3 - inserts the references in table_tp

If other query begin at the time when query one is running and if I don't block the rows selected by query one, this query do the same work of query 1 and get duplicated rows.

In table_tp I don´t have an unique key, because in some cases it would be duplicated data, I know is a bit complicated, I hope I expleinme. Tell me if i'm not clear, i'm lack of practice with my english

Thanks to all!

abitguru Ten Centuries Points: 1170 More actions · Answer 6

Oliiii (10/5/2010)
Paul White NZ (10/4/2010)
Oliiii (10/4/2010)[hrIt looks like it knows that even though the record is held by an exclusive lock, it hasn't been modified yet so is not yet dirty and can then read it safely.
So i guess getting a lock is not a good trick to keep someone from doing a select.
I'll keep looking.
The issue is that readers don't take shared locks when running under a row-versioning isolation level. Since no shared lock is required, there's nothing to conflict with the exclusive lock.
I've ran my test on a DB without any row-versioning, the second query select still goes trough no problem even though there is an exclusive lock on the row (same thing using your queries).
Changing the second query to also use WITH (XLOCK) does the trick though.

Thanks to all. You were helpfull to me to understand a lot of things about locks.

Now I will test on my production system and check how it behaves.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

gheinze (10/5/2010)
Thanks to all. You were helpfull to me to understand a lot of things about locks.
Now I will test on my production system and check how it behaves.

Yikes! What? nononono, you want to test on your QA system.... um... *watches the mushroom cloud*... nevermind.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

abitguru Ten Centuries Points: 1170 More actions · Answer 8

Craig Farrell (10/5/2010)
gheinze (10/5/2010)
Thanks to all. You were helpfull to me to understand a lot of things about locks.
Now I will test on my production system and check how it behaves.
Yikes! What? nononono, you want to test on your QA system.... um... *watches the mushroom cloud*... nevermind.

lol, sorry for not explainme-

the part of the system witch use sql server is in test.

actually in our system we have other database, so I dont affect anyone with my test.

Paul White SSC Guru Points: 150468 More actions · Answer 9

Oliiii (10/5/2010)
I've ran my test on a DB without any row-versioning, the second query select still goes trough no problem even though there is an exclusive lock on the row (same thing using your queries). Changing the second query to also use WITH (XLOCK) does the trick though.

This is a good example of why it is bad design to rely on the blocking behaviour of locks to implement a queue.

In theory, readers at read committed take shared locks, but there is an (undocumented) optimization in SQL Server, which allows a query reading records under read committed isolation to skip acquiring shared row locks under certain circumstances.

We can force query 2 to acquire shared locks by running it at repeatable read:

SELECT * FROM table_tp WITH (XLOCK) WHERE ID = 106435; -- Query 1

SELECT * FROM table_tp WITH (REPEATABLEREAD) WHERE ID = 106435; -- Query 2

The 'skipping shared locks' optimization can be defeated in a number of ways (not all of which are under our control).

As you say, using XLOCK on both queries also works:

SELECT * FROM table_tp WITH (XLOCK) WHERE ID = 106435; -- Query 1

SELECT * FROM table_tp WITH (XLOCK) WHERE ID = 106435; -- Query 2

...as does UPDLOCK (which does not block 'regular' readers which do not specify UPDLOCK):

SELECT * FROM table_tp WITH (UPDLOCK) WHERE ID = 106435; -- Query 1

SELECT * FROM table_tp WITH (UPDLOCK) WHERE ID = 106435; -- Query 2

Nevertheless, as I said above, relying on locking features in this way is not recommended. Locking is there to provide consistency guarantees, not to provide blocking functionality. In the rare cases that custom blocking semantics are required, we can use application locks via sp_getapplock and sp_releaseapplock.

In the context of this thread, however, I think gheinze needs a queuing system.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 10

gheinze (10/5/2010)
If another query starts at the time when query 1 is running both queries get the same rows of the temporal table and I get duplicate rows.

Have a read of:

http://rusanu.com/2010/03/26/using-tables-as-queues/

It might give you some ideas for an alternative design.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi