Tag Archives: SQL Server

Scary SQL Server 2005 & 2008 bug

Leave a reply

Watch out when using @@IDENTITY and SCOPE_IDENTITY() in your applications. Below is a quote from a Microsoft SQL Server engineer.

… whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistently and can’t be relied upon.

You can find more about the bug and workarounds at the SQL Server bug  report.

UPDATE 28MAR2009: Pinal Dave has a more detailed blog post about the bug with workaround.

How to check for and enable read committed snapshot isolation

9 Replies

Run the below query to see which databases that uses read committed snapshot isolation:

SELECT name, is_read_committed_snapshot_on FROM sys.databases

To enable it on a specific database run the below query:

ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON

As a developer I am in love with read committed snapshot isolation feature for SQL Server 2005+. Transactions can read and write simultaneously the same piece of data – no read/write contention due to row versioning. Writers will block other writers though.

Normal conflicting scenarios:

Simultaneously write and read of the same data.

  • Read committed isolation:  The reader will wait for the writer to release the exclusive lock.
  • Read committed snapshot isolation:  The reader will not block, but receive the latest committed data (latest row version).

Simultaneously writes of the same data.

  • Read committed isolation: The second writer will wait until the first transaction completes because of an exclusive lock.
  • Read committed snapshot isolation: Same as read committed isolation.

Read more about read committed snapshot isolation and isolation levels in the Isolation Levels in SQL Server 2005 article.

Update 02FEB2011: Amended the article due to Tom’s comments.