SQL Server Driver for PHP Connection Options: Snapshot Isolation
In the post I wrote last week about transaction isolation levels I left out an important option: the snapshot isolation level for database transactions. I’ll cover the snapshot isolation level in this post, and (as in my earlier post) I’ll look at what happens at the database level when using a particular isolation level (rather than concentrating on PHP data access code). I should also point out that in my earlier post I covered the ANSI standard isolation levels, which are implemented by most relational database management systems (SQL Server, MySQL, Oracle, DB2, etc.). However, the snapshot isolation level is not an ANSI standard and is not implemented by all database management systems (including MySQL), so this post isn’t necessarily as database-agnostic as my earlier post. That said, the concepts in this post are broadly applicable to many (just not all) RDBMS.
I’ll build on the concepts I presented in my earlier post about isolation levels, so I highly recommend reading that post (which is here) before continuing with this one. I also think the Wikipedia entry for snapshot isolation (here) is great background reading.
What is Snapshot Isolation and Why Use It?
Snapshot isolation is an alternative to the standard ways of implementing transactions and maintaining ACID guarantees in relational databases. Whereas the traditional ways for maintaining data consistency and integrity rely on some form of locking, snapshot isolation essentially makes a snapshot of data available to other users (this is also known as multiversion concurrency control or row versioning). For example, suppose a transaction is in progress that is transferring $10 from my checking account to my savings account. Depending on the transaction isolation level, someone else may or may not be able to access my account balances while the transaction is in progress (because the data is locked). If the transfer is executed under the snapshot isolation level, however, a copy of the pre-transaction data is made and is available to other users while the transaction is in progress. For a given application, this might not be the desired behavior, but if it is, performance may be improved in a multi-user scenario since data locking is minimized. In many ways, this only makes sense: if one person is prevented from accessing data until someone else has finished working with the same data (as is the case with locking), we should expect slowdowns in performance when many people are accessing large datasets at the same time. If a copy of data is available to concurrent users (therefore minimizing locks), we should expect better performance.
One scenario that snapshot isolation helps with is the following one: Suppose I transfer $50 (of $100) from checking to savings in one transaction. And, further suppose that while the transaction is in flight, my wife is withdraws $60 from checking at an ATM. To make sure my checking account isn’t overdrawn, I’d want the client to retrieve the current balance, make sure the balance was sufficient for the transaction, then check the balance again to make sure it hadn’t changed since it was originally checked before committing the transaction (and if the funds weren’t sufficient, roll back the transaction.) This is what snapshot isolation does. I get a snapshot of the balance, but when I try to commit data that has changed while my transaction is open, the transaction aborts automatically and rolls back. This is clearer in the example below…
Using Snapshot Isolation
Now that we know what snapshot isolation is, let’s see it in action. As in my earlier post, I’ll use SQL Server Express, SQL Server Management Studio (SSMS) and the test database (TestDB) that I used in that post.
The first thing we need to do is turn on snapshot isolation. Without doing this at the database level, we won’t be able to set the transaction isolation level to snapshot for any transaction. Here’s the code for doing this:
USE master;
GOALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION ON
Note that after we’ve turned on snapshot isolation levels, the other isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) are still available and their behavior is unchanged (and the default level is still READ COMMITTED). However, we can now set the isolation level for a transaction to SNAPSHOT_ISOLATION for any transaction. Let’s look at what happens when we try to read data under this isolation level, and what happens when we try to insert or update data under this level.
On the surface, reading data under the SNAPSHOT isolation level looks very much like what we’d expect to see under the REPEATABLE READ level. If we start a transaction (T1) and leave it open (i.e. we don’t commit it)…
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT * FROM BankAccount
--COMMIT TRANSACTION
…now begin a transaction (T2) that transfers $10 from checking to savings…
BEGIN TRANSACTION
DECLARE @money INT
DECLARE @accountId INT
SET @money = 10
SET @accountId = 1UPDATE BankAccount SET Checking = Checking - @money WHERE AccountId = @accountId
UPDATE BankAccount SET Savings = Savings + @money WHERE AccountId = @accountId--COMMIT TRANSACTION
…we can make a couple of observations. The first is that T2 was not blocked (as it would have been in under the REPEATABLE READ level). The second is that we see the same data from repeated reads in T1 (return to T1, highlight SELECT * FROM BankAccount, and press F5). This is because a copy of the data was made at the beginning of T1. This copy is then returned to subsequent reads from within the transaction. You can actually see that SQL Server is keeping a copy by looking at the “version store”…just execute this: SELECT * FROM sys.dm_tran_version_store. (Note that this stored version of the data will eventually disappear after the transaction is committed.)
Be sure to commit each transaction above by highlighting COMMITT TRANSACTION and pressing F5 before moving on.
The behavior when updating or inserting data under the SNAPSHOT isolation level is unlike any of the other levels. When under the SNAPSHOT level, if you try to update or insert data that has been modified by another transaction, your transaction will be aborted and rolled back. To see this, start a transaction (T3) and leave it open…
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT * FROM BankAccount
--COMMIT TRANSACTION
…then execute an update transaction (T4) and commit it…
BEGIN TRANSACTION
DECLARE @money INT
DECLARE @accountId INT
SET @money = 10
SET @accountId = 1UPDATE BankAccount SET Checking = Checking - @money WHERE AccountId = @accountId
UPDATE BankAccount SET Savings = Savings + @money WHERE AccountId = @accountIdCOMMIT TRANSACTION
…and finally, return to T3 and execute an update statement (modeling a $1000 deposit into checking)…
UPDATE BankAccount SET Checking = Checking + 1000 WHERE AccountId = 1
At this point, you will get an error stating that the transaction has been aborted:
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.BankAccount' directly or indirectly in database 'TestDB' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
The SNAPSHOT isolation level is a form of optimistic concurrency control. Note that, like the SERIALIZABLE isolation level, the SNAPSHOT level does not allow for dirty reads, non-repeatable reads, or phantom reads, but it does this without locking data. Again, this behavior may be right for some applications, not for others. Fortunately, there is another option: read committed snapshot isolation.
Using Read Committed Snapshot Isolation
When transactions are run under the READ COMMITTED SNAPSHOT isolation level, data is still read from a snapshot of the data, but there are two notable differences from the behavior above, both of which are best illustrated by working through the examples above, but running them under the READ COMMITTED SNAPSHOT isolation level
As with SNAPSHOT, you must first turn on READ COMMITTED SNAPSHOT at the database level:
USE master;
GOALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT ON
Now, queries that run under the READ COMMITTED level (which is the default isolation level) will automatically leverage snapshot data. Repeating the scenario above for transactions T1 and T2, but with the isolation level set to READ COMMITTED will result in similar behavior (data is read from the version store). However, when T2 commits, subsequent reads in T1 will read the newly committed data (whereas under the SNAPSHOT isolation level, the data from the version store is returned for subsequent reads).
Repeating the scenario above for transaction T3 and T4 (but this the isolation level set to READ COMMITTED) will also produce somewhat different results. Instead of the T4 being aborted when it tries to modify data that was modified by T3, T4 is not aborted. Instead, it is blocked until T3 commits. Once T3 commits, T4 can make the update.
Setting the Isolation Level via PHP
As noted in the examples above, both SNAPSHOT and READ COMMITTED SNAPSHOT must be turned on at the database level. You an do this in SQL Server Management Studio (as I did above), or by connecting to the master database via PHP and executing the appropriate query. For example…
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"master", "UID"=>"uid", "PWD"=>"pwd");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$stmt = sqlsrv_query($conn, "ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION ON");
Either way, once you have turned on the desired functionality, then the transaction isolation level is set at the connection level:
SQLSRV
$serverName = "bswanlaptop\sqlexpress";
$connectionInfo = array( "Database"=>"TestDB",
"UID"=>"uid",
"PWD"=>"pwd",
"TransactionIsolation" => SQLSRV_TXN_SNAPSHOT);
$conn = sqlsrv_connect( $serverName, $connectionInfo);//execute queries under snapshot isolation here using $conn
PDO
$serverName = "serverName\SQLEXPRESS";
$databaseName = "TestDB";
$isolationLevel = PDO::SQLSRV_TXN_SNAPSHOT;
$UID = "uid";
$PWD = "pwd";$conn = new PDO("sqlsrv:Server=".$serverName.
"; Database=".$databaseName.
"; TransactionIsolation=".$isolationLevel,
$UID, $PWD);//execute queries under snapshot isolation here using $conn
For more information about connection options in the SQL Server Drivers for PHP, see Connection Options in the product documentation.
That’s it for today.
Thanks.
-Brian