Transactions, isolation, and SQL Azure

I was trundling through my SQL Azure database today, looking for interesting behaviors. Found one. A "select * from sys.databases"  reveals that both "snapshot_isolation_state" and "is_read_committed_snapshot_on" return 1 (on) for all databases. Because ALTER DATABASE isn't supported, these cannot be changed.

So READ COMMITTED SNAPSHOT is the default transaction isolation level, which may come as a surprise to those applications that depend on the read committed locking behavior of SQL Server. Although so far there's been no big hue and cry. The readcommittedlock query hint works as expected, but if you're expecting read locks, you won't get them by default. And the other three locking-based isolation levels are available, SQL Azure is SQL Server after all, just with the two snapshot isolation switches turned on.

Remember also that the SQL Azure session timeout will rollback uncommitted transactions in progress (as it should). I was reminded of that while testing isolation levels and forgetting to commit a transaction.

There isn't much reference to this in the SQL Azure Books Online, and although I did find a reference to this in the SQL Azure FAQ, the FAQ says "snapshot isolation" is the default. Technically it's "read committed snapshot" (known also as "statement-level snapshot") that's the default, although the SQL Server snapshot isolation level (known as "transaction-level snapshot") is available and works as advertised.

This may be for the best, because you can't use either the dynamic management views or sp_lock to observe the locks in your instance/database in any case. A final point of interest is that application locks are supported, but lack of visibility would mean it may be difficult to troubleshoot these.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.