Default select queries with (readcommitted) just don't work reliably in real life (because they cause deadlocks on regular basis).
It was hard to ignore scary stuff from SQL theoreticians about "dirty data", but eventually I've learned to use "with (nolock)" hint for most of my SELECT queries.
That doesn't mean that all queries should be written with nolock, but most of SQL queries should.
Especially if I'm writing web app.
In some situations I want to be sure that the data is consistent no matter what. In this case I use different locking hints. Which hints to use heavily depends on particular situation.
It's important to clearly understand what exactly locking does.
The locking model is relatively simple, however most of SQL Server locking tutorials are just terrible.
How-To learn SQL Server locking Tutorial
The best approach to learn about locking is to experiment:1) Open three separate windows in SQL Management Studio.
Window One: for lock diagnostic sp_lock
Window Two: for pending transaction with Update/Delete/Insert query.
Window Three: for SELECT statements with different locking hints.
2) In Window One write and execute:
exec sp_lock
Take a look at the results.
Note what locks are there (these locks are generated by sp_lock itself).
Learn about what these locks mean (Google is your friend).
3) In Window Two (that's separate connection to SQL server) write and execute:
============
begin transaction
update MyTestTable set MyColumn = 1
-- rollback transaction
============
Note, that "rollback transaction" is commented out, so transaction won't complete.
4) Return back to Window One and execute sp_lock.
Note what additional locks you see in sp_lock results.
5) In Window Three execute:
select * from MyTestTable with (updlock)
The query won't complete, because it's locked by pending transaction in Window Two.
6) Switch to Window Two and cancel transaction by executing "rollback transaction"
Make sure that SELECT transaction in Window Three is completed now.
7) Try different combinations of locks (readcommitted), (nolock), (updlock), (repeatableread), (serializable), ... in Window Three.
Try other different update/insert/delete statements in Window Two.
8) Try to use "commit transaction" instead of "rollback transaction" in Window Two and see how it would affect SELECT results in Window Three.
9) Try to run:
begin transaction
select * from MyTestTable with (updlock)
in Window Two and:
select * from MyTestTable with (serializable)
in Window Three.
10) Keep monitoring locks using sp_Lock in Window One.
11) Do free-style experimenting and research problem on Google when you are getting unexpected results.
No comments:
Post a Comment