Tag Archives: SQL Server

Scary SQL Server 2005 & 2008 bug

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

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.