Read Committed Snapshot Isolation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Read Committed Snapshot Isolation

12 posts, Page 1 of 2

1 2 »»

Read Committed Snapshot Isolation

Rate Topic

Display Mode

Topic Options

Author

Message

IT researcher

Posted Saturday, February 09, 2013 2:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 183, Visits: 948

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 ...)

Post #1417993

GilaMonster

Posted Saturday, February 09, 2013 2:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,728, Visits: 29,989

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1417994

IT researcher

Posted Monday, February 11, 2013 5:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 183, Visits: 948

Thanks for the reply.

Post #1418347

IT researcher

Posted Monday, February 11, 2013 5:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 183, Visits: 948

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.

Post #1418361

GilaMonster

Posted Monday, February 11, 2013 6:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,728, Visits: 29,989

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1418371

IT researcher

Posted Monday, February 11, 2013 6:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 183, Visits: 948

ok, got it.

Thanks

Post #1418382

Vedran Kesegic

Posted Monday, February 11, 2013 7:09 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 342, Visits: 1,074

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).

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1418402

GilaMonster

Posted Monday, February 11, 2013 7:13 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,728, Visits: 29,989

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1418404

Vedran Kesegic

Posted Monday, February 11, 2013 8:26 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 342, Visits: 1,074

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!)".

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1418473

GilaMonster

Posted Monday, February 11, 2013 8:39 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,728, Visits: 29,989

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1418482

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions