February 9, 2013 at 2:33 am
when Read Committed Snapshot Isolation is ON,
1. whether Phantom Reads possible ?
2. whether tempdb needs to managed ? (how?)
3. when a row(snapshot) will be added and removed from tempdb automatically? (compared to 'Snapshot' isolation level)
4. any problem if 'Serializable' isolation level is used for update query
5. Any other performance issues ? (speed,CPU ...)
February 9, 2013 at 2:54 am
1) Yes. It's Read committed isolation level using row versions, it allows all the same data anomalies as read committed does.
2) You don't need to manage the version store, just make sure you have space and IOs spare
3) Only difference is that read committed snapshot the row version needs to be retained until the oldest running statement can no longer need it, snapshot isolation the row version needs to be retained until the oldest running transaction can no longer need it
4) If you set serializable on, that session runs in serializable (which uses locks), no different from any other time using serializable. Row versions still have to be stored though
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2013 at 5:41 am
Thanks for the reply.
February 11, 2013 at 5:52 am
under 'snapshot' transaction , whether the entire database snapshot is taken? (during begin transaction statement is run) or only required rows snapshot is taken ?(during select statement)
thanks.
February 11, 2013 at 6:07 am
Err... think about whether the first option is even feasible. Imagine a 2TB database with 100 transactions/sec....
http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2013 at 6:32 am
ok, got it.
Thanks
February 11, 2013 at 7:09 am
When you enable read committed snapshot on a database level, it starts to use row versioning instead of locks to maintain transaction consistency and isolation. Locks are still there, but much less:
ALTER DATABASE MyDb SET READ_COMMITTED_SNASPHOT ON
WITH ROLLBACK AFTER 5
It is a statement-level isolation. That means, if a transaction consists of multiple statements, each statement "sees" the database as it was at the beginning of that statement (not transaction!). No phantoms are possible, so it is a very good thing, and is on by default on most of my productions.
You can additionally enable "allow snapshot isolation":
ALTER DATABASE MyDb SET ALLOW_SNASPHOT_ISOLATION ON
That enables you to use SNAPSHOT isolation level, you explicitly have to enter:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
...
COMMIT TRAN
Snapshot isolation level is transaction-level (not statement-level) isolation. That means all the statements in that transaction see the database as it was at the beginning of the transaction. It can be useful for reporting purposes where each statement in a transaction has to see exactly the same data (not to see changes from other users).
February 11, 2013 at 7:13 am
Vedran Kesegic (2/11/2013)
No phantoms are possible, so it is a very good thing, and is on by default on most of my productions.
Phantoms are indeed possible under read committed snapshot, it allows the same data anomalies as read committed does, ie non-repeatable reads and phantoms. It's snapshot (and serializable) that doesn't allow phantoms.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2013 at 8:26 am
Phantoms are indeed possible under read committed snapshot, it allows the same data anomalies as read committed does, ie non-repeatable reads and phantoms
It's not the same, and there are two kinds of "phantoms". Under the default, read committed isolation level, locks are placed on the rows as they are processed, not upfront.
So, it might happen that your statement starts reading a table, and after that other transaction starts and commits a row your query didn't reach yet. Your query will see that row despite other transaction begun AFTER your transaction. That is a statement-level phantom and it is not possible in "read committed snapshot" isolation level.
Even worse, in "read committed" isolation level your transaction will release row lock as soon it processes that row. Other transaction may update that row placing it in front of your query reading pointer, and your transaction will read that same row twice.
That cannot happen in "read committed snapshot".
In "read committed" isolation, after your query started, someone might update a row that is in front of your query reading pointer (you would read it, but didn't arrived to it yet, and thus didn't lock it). The update of that other transaction moved the row before your query reading pointer and commits. Your query goes on and does not see that row at all, despite the row is in the table all the time, and despite the fact the other transaction started after your transaction and finished before your transaction finished. That cannot happen in "read committed snapshot".
The phantom you refer to is transaction level phantom, where you run the same query multiple times in the same transaction and new records appear. That can happen in "read committed snapshot" but that case is covered with my words "each statement sees the database as it was at the beginning of that statement (not transaction!)".
February 11, 2013 at 8:39 am
Vedran Kesegic (2/11/2013)
So, it might happen that your statement starts reading a table, and after that other transaction starts and commits a row your query didn't reach yet. Your query will see that row despite other transaction begun AFTER your transaction. That is a statement-level phantom and it is not possible in "read committed snapshot" isolation level.
The definition of Phantom Read is a row or set of rows that appears in a query's results when it is executed a second (or more) times within a transaction. What you describe there may be considered a data anomaly (but in most cases probably won't be), but it's not the data anomaly 'phantom reads'
ANSI:
Process P1 reads the set of rows N that satisfy some search condition. Process P2 then executes statements that generate one or more rows that satisfy the search condition. If P1 repeats the query it obtains a different collection of rows.
That's a phantom read and that can happen in any isolation level other than snapshot or serialisable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2013 at 4:36 pm
Thanks Gail for the definition. But that does not change the fact that read committed snapshot is not the same as read committed even in terms of "anomalies". "Read committed snapshot" protects from more anomalies than "read committed" as I described several examples. I understand what is happening inside, but don't know how these are called, is there a special name for that cases or everything is in one big bucket called "anomaly".
February 12, 2013 at 12:21 am
They're all data anomalies as far as I know, and the 3 that define the ANSI standards for isolation levels have specific names and meanings, the isolation levels are defined by which of the 3 the levels allow. Read committed is defined as an isolation level that allows phantoms and non-repeatable reads and does not allow dirty reads, no matter how it's implemented.
The ones that you mentioned are not ANSI-standard definitions, they're side effects of Microsoft's implementation of the isolation levels (as is the dup-read/miss-read on read uncommitted) and hence they're subject to change across versions and across changes in how the isolation levels are implemented
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply