SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / Isolation levels - Database Engine / Latest Posts

RE: Isolation levels - Database Engine

rtelgenhoff — Mon, 11 Oct 2010 09:24:39 GMT

Great question and very relevant to concurrency and data integrity issues.

RE: Isolation levels - Database Engine

Lempster — Thu, 12 Mar 2009 09:56:38 GMT

Another poorly worded and ill-thought out question!:angry:

RE: Isolation levels - Database Engine

raymond lew — Fri, 06 Mar 2009 10:41:45 GMT

Hi Derek, I agree with you and Irish Flyer hat this a plain read committed isolation level scenario. By the way, I like your scripts. Nicely contained, but detailed. Kinda like that Tom Kite stuff.:)raymond

RE: Isolation levels - Database Engine

Derek Dongray — Fri, 06 Mar 2009 08:45:04 GMT

[quote][b]Ray Herring (3/6/2009)[/b][hr]I agree with BitBucket. The results described at what you would expect in "normal" operations (assuming the typo is corrected)[/quote]I understand the sections BitBucket quoted, but can't see how the answer SNAPSHOT is correct and READ COMMITTED isn't when the scenario has [b]no commits[/b]!As far I can see, since nothing has been committed, it doesn't matter which isolation level is in effect, transaction 1 won't see any change.Now if the scenario was changed so that transaction 2 committed it's change before 1's 2nd select then the described behaviour is SNAPSHOT isolation level. But the question explicitly stated[quote]The first transaction continues and again executes before the 2nd transaction committs[/quote]In this situation, it's impossible to tell whether it's SNAPSHOT or READ COMMITTED, as my test scripts show!

RE: Isolation levels - Database Engine

Ray Herring — Fri, 06 Mar 2009 08:18:47 GMT

I agree with BitBucket. The results described at what you would expect in "normal" operations (assuming the typo is corrected)

RE: Isolation levels - Database Engine

Derek Dongray — Fri, 06 Mar 2009 06:07:33 GMT

Bitbucket,Irish Flyer has already stated the fact that was confusing me.From the way your scenario was stated, the second script [b]did not commit its changes[/b]. Hence I wrote my tests using 'ROLLBACK TRAN' to cancel the commits.As a result, I can't see any way to distinguish between READ COMMITTED and SNAPSHOT (as my tests showed) [b]in the given scenario[/b].They are only distinguishable if something actually gets commited.If script 2 actually commited it's change before script 1's second select and script 1 got the same result then that would be SNAPSHOT, but since that's not how you stated the problem I don't see how you can choose between the two.

RE: Isolation levels - Database Engine

Cliff Jones — Thu, 05 Mar 2009 13:05:52 GMT

Got it. Thanks for the clarification.

RE: Isolation levels - Database Engine

Irish Flyer — Thu, 05 Mar 2009 13:01:56 GMT

Yes, that is exactly why. As I said, the issue of the correct answer relates to the specific phrasing and setup of the scenario. If there were either commits or transaction ends between the queries and updates, the result would be very much influenced by the high order database setting of READ_COMMITTED_SNAPSHOT.

RE: Isolation levels - Database Engine

Cliff Jones — Thu, 05 Mar 2009 12:56:50 GMT

Does it work this way because the original select and final select are within a transaction? Or does that matter?

RE: Isolation levels - Database Engine

Irish Flyer — Thu, 05 Mar 2009 12:45:23 GMT

No. Only if there was a commit and then it go locked by another transaction. Within a transaction, changes made but not committed are immediately available. The changed row is not locked by the transaction that made the change when it is referencing within itself. The uncommitted row is only unavailable and invisible to other transactions.

RE: Isolation levels - Database Engine

Cliff Jones — Thu, 05 Mar 2009 12:39:53 GMT

Wouldn't the last select hang waiting for the pending commit or rollback?

RE: Isolation levels - Database Engine

Irish Flyer — Thu, 05 Mar 2009 12:35:30 GMT

Cliff,You are right that READ_COMMITTED_SNAPSHOT being on or off changes outcomes, but just not in the case stated, because of the WAY it is stated. As long as the two overlapping transactions do not commit and also do not end, they will see the original data row values unless they change the values within themselves, a la Transaction 2. The READ_COMMITTED_SNAPSHOT only makes a difference if there is an intervening commit.

RE: Isolation levels - Database Engine

Cliff Jones — Thu, 05 Mar 2009 12:25:09 GMT

I chose the correct answer because I felt it was most correct. But the way I read it was that if you had READ_COMMITTED_SNAPSHOT set to on, it would produce the same result? But that is not the case?From BOL:If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

RE: Isolation levels - Database Engine

Irish Flyer — Thu, 05 Mar 2009 12:24:48 GMT

Bitbucket,I understand your explation and I read the same information from BOL, etc. That doesn't change the fact that the way you framed the scenario, it makes no difference whether snapshot isolation is on or not, you should get the same result. You specifically state that Transaction 1 makes its second query before Transaction 2 commits, hence it does not see the change Transaction 2 made. Transaction 2, however, will see the change made with itself when it queries the second time, even though it hasn't committed, because it hasn't ended yet.This is why a simple answer of READ COMMITTED should be valid.

RE: Isolation levels - Database Engine

bitbucket-25253 — Thu, 05 Mar 2009 12:01:38 GMT

In an effort to further explain what occurs with the SNAPSHOT and READ COMITTED Isolation using Row versioning - the following quotes were taken from [url]http://technet.microsoft.com/en-us/library/ms345124(SQL.90).aspx[/url]. The use of italics and bold facing were added by this poster. Under the headingSnapshot Isolation (Transaction-Level Read Consistency)[quote]When snapshot isolation is set, it guarantees transaction-level read consistency where every statement within a snapshot isolation transaction [b]sees only committed changes that occurred [i]before the start of the transaction.[/i][/b] Effectively, each statement in the transaction sees the same set of data while the data is available for modification outside of this transaction. Concurrent modifications are not prevented and this "snapshot" transaction is unaware of the changes that are made by other transactions. The version "refresh" occurs only in the start of each transaction as long as you run under snapshot isolationUnder the heading:Read Committed Isolation Using Row Versioning (Statement-Level Read Consistency)[quote]When set, statement-level read consistency guarantees that each statement under read committed isolation sees only committed changes that occurred before the start of the statement. [i][b]Each new statement within the transaction picks up the most recent committed changes.[/i][/b] In other words, this version of read committed is semantically similar to traditional read committed in that only committed changes are visible, but the timing of when those changes committed differs. Each statement sees the changes that were committed before the statement began instead of when the resource is read.[/quote][quote]In SQL Server 2005, to use row versioning–based isolation, one of the following [b]database options[/b] must already be set (and not pending): Read committed isolation using row versioning for statement-level read consistency Snapshot isolation for transaction-level read consistency[/quote] Hope this clarifies the situation

RE: Isolation levels - Database Engine

Irish Flyer — Thu, 05 Mar 2009 09:17:12 GMT

It seems that READ COMMITTED gives the same result. Transaction 2 cannot change the row until transaction 1 finishes, but within transaction 2 it sees any updates it has made as done, even though they have not yet been committed so others can see them. This is regardless of the snapshot isolation setting.

RE: Isolation levels - Database Engine

Cliff Jones — Thu, 05 Mar 2009 08:36:58 GMT

[quote][b]William Vach (3/5/2009)[/b][hr]Shouldn't the where clause of the 3rd statement be "where id = 2"?[/quote]A great question that makes you think. But we need to fix the typo in the 3rd statement.

RE: Isolation levels - Database Engine

Derek Dongray — Thu, 05 Mar 2009 08:10:06 GMT

Can someone clarify the question and answers here please?The question is asking what isolation the [b]database[/b] is set to, but the answers are transaction isolation levels.Firstly, where is the isolation level for the [b]database[/b] set?Secondly, I ran a number of tests using the following 2 scripts, with variations.Script 1:[code]use scratchdrop table tdrop table #tdrop table #ucreate table t (id int, val varchar(20))insert t values(2,'Original')goalter database scratch set allow_snapshot_isolation onalter database scratch set read_committed_snapshot offgoset transaction isolation level read committedset nocount onbegin transelect Val, getdate() from t where id=2declare @i intset @i=0while @i<5beginselect a.* into #t from sysobjects a, sysobjects bselect top 1 * into #u from #tdrop table #tdrop table #uset @i = @i+1endselect Val, getdate() from t where id=2rollback tran[/code]Script 2:[code]use scratchset transaction isolation level read committedset nocount onbegin transelect val, getdate() from t where id=2update t set val='gotcha' where id = 2select val, getdate() from t where id=2declare @i intset @i=0while @i<5beginselect a.* into #t from sysobjects a, sysobjects bselect top 1 * into #u from #tdrop table #tdrop table #uset @i = @i+1endrollback tran[/code]Obviously, in each case, the loop is merely to slow down the transaction so that script 2 can start execution while script 1 is running and then script 1 completes before script 2.The results were as follows:Run 1:[quote]Script 1:alter database scratch set allow_snapshot_isolation onalter database scratch set read_committed_snapshot ongoset transaction isolation level snapshotVal -------------------- -----------------------Original 2009-03-05 14:45:28.533Original 2009-03-05 14:45:45.800Script 2:set transaction isolation level snapshotval -------------------- -----------------------Original 2009-03-05 14:45:30.770gotcha 2009-03-05 14:45:30.770[/quote]Run 2:[quote]Script 1:alter database scratch set allow_snapshot_isolation onalter database scratch set read_committed_snapshot ongoset transaction isolation level read committedVal -------------------- -----------------------Original 2009-03-05 14:49:38.690Original 2009-03-05 14:49:55.517Script 2:set transaction isolation level read committedval -------------------- -----------------------Original 2009-03-05 14:49:40.893gotcha 2009-03-05 14:49:40.893[/quote]Run 3:[quote]Script 1:alter database scratch set allow_snapshot_isolation onalter database scratch set read_committed_snapshot offgoset transaction isolation level read committedVal -------------------- -----------------------Original 2009-03-05 14:53:53.733Original 2009-03-05 14:54:08.673Script 2:set transaction isolation level read committedval -------------------- -----------------------Original 2009-03-05 14:53:58.280gotcha 2009-03-05 14:53:58.280[/quote]Run 4:[quote]Script 1:alter database scratch set allow_snapshot_isolation onalter database scratch set read_committed_snapshot offgoset transaction isolation level snapshotVal -------------------- -----------------------Original 2009-03-05 15:12:49.790Original 2009-03-05 15:13:09.010Script 2:set transaction isolation level snapshotval -------------------- -----------------------Original 2009-03-05 15:12:58.633gotcha 2009-03-05 15:12:58.633[/quote]In each case the results described in the question were returned. I.e. Script 1 read 'Original', Script 2 updated and read 'gotcha', then Script 1 read 'Original' again.As far as I can see it is not possible to tell from the results described either whether the transaction isolation level is snapshot or read committed or whether the database has read_committed_snapshot set on or off.Are my scripts wrong or have I missed the point of the question entirely?[Edit: Added Run 4, isolation level snapshot with read_committed_snapshot off]

RE: Isolation levels - Database Engine

BHansen — Thu, 05 Mar 2009 07:08:45 GMT

I got this one, but just wanted to clarify a couple of questions in my own mind ....(1) If Read Committed Snapshot Isolation had been an option, that would have been correct as well, right?(2) The question asks what isolation level is being used by the database. Isn't it more correct to have asked what isolation level is being used by the first connection?

RE: Isolation levels - Database Engine

Andeavour — Thu, 05 Mar 2009 06:18:51 GMT

Excellent question. Fooled me, but then it's fooled 82% of us so far...:P

RE: Isolation levels - Database Engine

William Vach — Thu, 05 Mar 2009 05:34:07 GMT

Shouldn't the where clause of the 3rd statement be "where id = 2"?

RE: Isolation levels - Database Engine

Ninja's_RGR'us — Thu, 05 Mar 2009 04:13:43 GMT

That question kicks ***... more please.

Isolation levels - Database Engine

bitbucket-25253 — Wed, 04 Mar 2009 22:09:25 GMT

Comments posted to this topic are about the item [B]Isolation levels - Database Engine[/B]