SQL Azure row lock behaving differently to local SQLExpress

Question

Hi

Hopefully somebody can help. I've some code which inserts a row into a table within a transaction (TransactionScope + Entity Framework).
When I run it locally, if I query for that row on another thread (or indeed through management studio) then the query execution blocks until the transaction is completed.
This is exactly the behaviour I need. Unfortunately I've found this not to be the case when testing on SQL Azure! The query returns an empty recordset rather than blocking.

What can I do to restore this functionality?
Thanks a lot!
Paul

Answer 1

I've figured it out. Here's the info for anybody finding this later.

Every SQL Azure databases is configured with the database options to enable snapshot isolation. Both READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options are set to ON. The default isolation level in both SQL Server and SQL Azure is READ COMMITTED, and because of the database option READ_COMMITTED_SNAPSHOT, your SQL Azure transactions run in optimistic concurrency. You cannot change the database options in a SQL Azure database. You may be able to control the isolation level explicitly on a connection using the SET TRANSACTION ISOLATION LEVEL command prior to beginning a transaction. However, you cannot change the default setting, READ COMMITTED with optimistic concurrency, to the SQL Server default setting, READ COMMITTED with pessimistic concurrency. The only way to duplicate the default SQL Server behavior is to use the locking hint WITH (READCOMMITTEDLOCK) with every table in every transaction.

Using the locking hint does indeed restore default behaviour.

Answer 2

Hello Paul,

By default SQL Azure databases uses snapshot isolation, therefore you get a different behaviour to you SQL Server Express database, see also http://www.sqlskills.com/BLOGS/BOBB/post/Transactions-isolation-and-SQL-Azure.aspx

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 3

For anyone who was wondering, this behavior is described in the SQL Azure FAQ: http://social.technet.microsoft.com/wiki/contents/articles/995.sql-azure-faq.aspx This FAQ contains a lot of interesting information on SQL Azure and how it differs from a regular SQL Server.

---

Sandrino Di Mattia | Twitter: http://twitter.com/sandrinodm | Azure Blog: http://fabriccontroller.net/blog | Blog: http://sandrinodimattia.net/blog