June 10, 2008 at 4:02 pm
Hello,
If one user does
[font="Courier New"]begin tran
delete * from t[/font]
and then the other does
[font="Courier New"]select count(*) from t[/font]
then the 2nd user needs to wait for the first to commit or roll back.
How can I change the locking mode so that the second user doesn't have to wait and sees the row count that existed before the first user started the transaction (since the first user has no committed changes at this point)?
Thanks!
Gabor
June 10, 2008 at 4:32 pm
Use either:
ALTER DATABASE {yourDB} SET READ_COMMITTED_SNAPSHOT ON;
Or,
ALTER DATABASE {yourDB} SET ALLOW_SNAPSHOT_ISOLATION ON;
And then have the Deleting proc start its transaction like this:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
The first is usually preferred.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 10, 2008 at 7:01 pm
Gabor,
If you change the isolation level, you do realize that user 2 is going to see the deleted records? You can also manage the isolation level at the query level without having the Database set to snapshot. You can issue a set Isolation Level REad Uncommitted before your select or do a Select From table with(Nolock) hint.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 10, 2008 at 8:01 pm
Thanks guys for the responses! I haven't had time to test this yet, but I will tomorrow.
In the mean time, could you please direct me to some reading material (for dummies) about this subject?
(Isolation levels, snapshot mode, etc.)
Thanks,
Gabor
June 11, 2008 at 7:26 am
Jack is right, of course. So I wanted to take a minute to highlight the difference between these two suggestions.
My two suggestions (called Snapshots or Row Versioning) allow users to see a view of the data from before another user's transaction began making changes without having to wait for conflicting transactions to resolve, which technically is what you said:
grevesz (6/10/2008)
How can I change the locking mode so that the second user doesn't have to wait and sees the row count that existed before the first user started the transaction (since the first user has no committed changes at this point)?
Jack's suggestions (called Read Uncommitted or NoLock) also free the users from having to wait for conflicting transactions to complete by showing the changes that have been made but not committed. Since the transaction might rollback instead of commit, these changes might not be "real". It is also possible to see data from different stages of the transactional changes which might be an integrity issue.
For example, a Money Transfer transaction first subtracts $X from one account and then adds $X to another account. With Snapshots, other user will only see a before or after picture, however, with NoLock or Read Uncommitted it is possible for a user to see the data after the subtraction but before the addition. This may or may not be a problem for you. (Neither will have to wait)
The flip side of this is that Jack's approach is far easier to implement and my approach does require more resources from your server: CPU, memory and disk space. Usually the CPU and memory commitment is not too much, the disk space requirements, however, require some analysis and thought and is the usualy reason why DBA may be reluctant to implement this option.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2008 at 7:27 am
grevesz (6/10/2008)
Thanks guys for the responses! I haven't had time to test this yet, but I will tomorrow.In the mean time, could you please direct me to some reading material (for dummies) about this subject?
(Isolation levels, snapshot mode, etc.)
SQL Server BOL (Books On-Line) is the best source for this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2008 at 7:41 am
RBarryYoung is correct in each of his posts. You do need to understand the differences between Snapshot Isolation and Read Ucommitted (nolock) and BOL is the best place to start reading on it. Most SQL Server books will also have at least a short section on Isolation Levels. Microsoft SQL Server 2005 Unleashed from SAMS has a section on it as does Inside Microsoft Sql Server 2005 Query Tuning and Optimization from Microsoft Press. There are several books in the Inside Microsoft SQL Server 2005 series and each is good.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 11, 2008 at 2:33 pm
Hello All,
Thanks again for your responses. I did the testing today which generated some conclusions (please confirm if they are correct) and further questions.
Here is the scenario:[font="Courier New"]
Table t has 10 records (committed).
Transaction A:
begin tran
delete from t
Transaction B:
select count(*) from t[/font]
The conclusions:
1. ALLOW_SNAPSHOT_ISOLATION has no effect if READ_COMMITTED_SNAPSHOT is OFF. Transaction B is waiting fot transaction A to finish (commit or roll back).
If READ_COMMITTED_SNAPSHOT is ON:
2. ALLOW_SNAPSHOT_ISOLATION OFF results in transaction B returning 0.
3. ALLOW_SNAPSHOT_ISOLATION ON results in transaction B returning 10.
The questions:
1. What is the purpose of ALLOW_SNAPSHOT_ISOLATION if it has no effect?
2. For RBarryYoung: I had to set both (not either or), but did not use the set transaction command. Is this correct, or did you have something else in mind?
3. For Jack:
You can also manage the isolation level at the query level without having the Database set to snapshot.
Since I could only achieve what I want at the database level, if anyone changes the database level settings, I am screwed. Is there a transaction level setting that I could use, which overrides the database level setting?
Thanks a lot!
Gabor
June 11, 2008 at 2:44 pm
Please check out "Row Versioning-based Isolation Level Example" in Books On-Line. It should answer a lot of your questions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2008 at 2:55 pm
Excellent explanation! Thanks for the pointer!
One question only: Can I set the default isolation level somewhere such that a begin tran is automatically understood as set transaction isolation level read committed; go; begin transaction;?
Gabor
June 11, 2008 at 3:25 pm
Gabor,
I actually misread your original post. In order to insure that the second user sees the data prior to the delete while the delete is on-going you need to use SNAPSHOT ISOLATION like RBarry suggests. The solution I put forth will read the data, but show the AFTER results. Here is some test scripts to show what I mean, make sure you have 2 sessions in SSMS:
[font="Courier New"]-- run this in one SSMS session
-- drop test table id it exists
IF OBJECT_ID('test.test') IS NOT NULL
BEGIN
DROP TABLE test.test
END
-- insert 100 rows into test table
SELECT TOP 100
IDENTITY(INT, 1,1) AS test_id
INTO
test.test
FROM
sys.columns C1,
sys.columns C2
BEGIN TRANSACTION
--delete first 10 rows
DELETE FROM test.test WHERE test_id < 11
-- pause 10 seconds to run query in another session
WAITFOR Delay '00:00:10'
-- rollback to show that nolock will not show deleted rows
ROLLBACK TRANSACTION
[/font]
And in the second session run this:
[font="Courier New"]-- this will run but not show deleted rows
SELECT * FROM test.test WITH (nolock)
-- ensure that the delete is done and rolled back
WAITFOR Delay '00:00:10'
-- this will show all rows
SELECT * FROM test.test[/font]
Also the WaitFor in the second session will make SSMS return both results at once instead of immediately showing the first resultset and then pausing.
You will get the same results if you remove the With(nolock) hint from the second session and instead start with Set Transaction Isolation Level Read Uncommitted.
So as you can see if you want to show the data as it was before the delete transaction then you need SNAPSHOT ISOLATION.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 11, 2008 at 3:29 pm
READ COMMITTED is the default Isolation Level in SQL Server. This is why a select will not finish until any exclusive locks are released.
For example in my example, if I were to do a select * from test.test where test_id = 100, I may get the result immediately because SQL Server may take page locks and I could be on a different page (unlikely with 100 4 byte rows) or row locks so that the row I want is not locked.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 11, 2008 at 3:45 pm
Thanks, that's quite clear now!
Gabor
June 11, 2008 at 5:00 pm
Not sure. We're already a little beyond me specific knowledge. I'd suggest checking some of the "see also" links from that article, or Synch to the Table of Contents and check out some of the articles in the same section.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2008 at 5:19 pm
Thanks!
Gabor
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply