READ COMMITTED Isolation levels: A comparison between Oracle and SQL Server
Published:
Browse All Articles > READ COMMITTED Isolation levels: A comparison between Oracle and SQL Server
Introduction
I sat in on a session at the PASS Summit 2012 in Seattle in November listening to a session delivered by Randy Knight talking about Isolation Levels in SQL Server and explaining how they are implemented.In my job, I support a large ERP product that runs on both SQL Server and Oracle. The development of this suite is done on an Oracle database so it seems that a lot of the design decisions are based on the way that Oracle handles things. Over the years I've seen that Oracle handles things differently to SQL Server - this made me want to research things a little closer.
As a DBA who spends time in both Oracle and SQL Server, it's important to understand Isolation Levels in these two RDBMS's and how they work, because their default behavior is very different.
Isolation Levels
According to the ANSI Standard, there are 4 default Isolation Levels in a Relational Database Management System, ordered below from least isolated to most isolated.
Read Uncommitted
Read Committed
Repeatable Read
Serializable
Going into the details of what each of these mean is outside the scope of what I want to write about today. There's a good write up at Wikipedia or any of a dozen or more blogs, just search "Isolation Levels" on your favorite search engine.
It should be noted that SQL Server implements all 4 of the ANSI Standard Isolation Levels plus one of its own (SNAPSHOT Isolation - which has 2 different levels in itself - Statement level and Transaction Level). As with the basic tenets, going into the details of snapshot isolation is outside of the scope of what I want to talk about here, but I will cover it briefly later in this article.
To learn more about SNAPSHOT Isolation, refer to Books Online.
Oracle, on the other hand, does not implement Read Uncommitted and also does not implement Repeatable Read. Oracle adds in their own extra option called Read Only Isolation. You can read more about this option in the Oracle Documentation
Because I work daily in both Oracle and SQL Server, what I really wanted to find out was what are the core effects of how SQL Server approached the READ COMMITTED Isolation level vs how Oracle handles it.
Since READ COMMITTED is the default isolation level for both database systems, it seemed to be a good place to base the comparison on.
The Difference
In basic terms, SQL Server has a "pessimistic" view of data concurrency by default. When in the middle of a transaction, SQL Server will block readers on any row that has changed until that row is committed or rolled back.Oracle, on the other hand has an "optimistic" view of concurrency by default. When in the middle of a transaction, Oracle will have readers see a version of the row as it existed at the start of the update transaction, thus not blocking readers.
I found an article written by Tom Kyte, Oracle's technology expert, where he talked about possible data inaccuracies when Read Committed is implemented the way that Microsoft does it in SQL Server. I'll use his example here as it is what I used for my own testing.
(He also covers some of the other isolation levels but you can read his article for more on those).
Example Setup
To begin with, I should note that this is a very specific example of how this situation could occur. If you were just looking at transactional data, you would see both updates looking just fine. This is a very specific example of something that could occur if you were running a query spanning a large range of values and doing a sum or an average.The basic setup is like this:
A bank account database has a table for balances that contains two columns, account and balance. The table has many hundreds of thousands rows.
A query is started to sum balances. The database begins reading data and accumulating balances. After 200,000 records are read a transaction is started in the database in another session. An account that has already been read is decremented by $400 and an account that has not yet been read (row number 300,000) is incremented by $400. The second account is then read in the first session before the second session is committed.
How do both database systems handle this ?
The timeline would go like this (I am going to represent the summing of balances by selecting one row at a time, and we'll put the values as they are "read" into a temporary table):
Transaction 1: select balance where account = 1;
Transaction 1: continue to select balances, working through the table...
Transaction 2: Increment the value of account 300000 by $400;
Transaction 1: select balance where account = 300000;
Transaction 2: Decrement the value of account 1 by $400;
Transaction 2: Commit
Oracle's Way
Let's start by looking at how Oracle handles it.I load a table with two rows representing either end of a list of accounts. Account 1 has a balance of $500 and Account 300000 has a balance of $100.
Oracle's SQL*Plus, differently from SQL Server Management Studio, has an implicit transaction by default. You don't need to specify BEGIN TRANSACTION - a transaction is assumed from the moment you start until you quit SQL*Plus (implicit commit) or explicitly issue a commit or rollback.
As suggested in the above, we will need two sessions for this test.
First start a new SQL*Plus session and do the setup:
declare v_table_name user_tables.table_name%type := 'ACCOUNTS';
begin
execute immediate 'drop table ' || v_table_name;
exception
when others then
null;
end;
/
create table accounts (account int, balance number);
insert into accounts values (1, 500);
insert into accounts values (300000,100);
declare v_table_name user_tables.table_name%type := 'TEMP_ACCOUNTS';
begin
execute immediate 'drop table ' || v_table_name;
exception
when others then
null;
end;
/
create global temporary table temp_accounts (account int, balance number)
on commit preserve rows;
In Session 1, we execute the following:
SQL> insert into temp_accounts select * from accounts where account = 1;
1 row created.
SQL>
This represents the row has been read by the query that's underway (sum(balance) from accounts) and the value is stored in the temporary table.
We go to session 2 and perform the first half of our transaction update.
SQL> update accounts set balance = 500 where account = 300000;
1 row updated.
SQL>
We do not commit the transaction.
We return to session 1. The sum() query gets to account 300000 and adds the value it finds to the sum (represented by another insert into the temporary table). Note, in Oracle, this is fetched immediately, there is no lock held on the updated value because Oracle is showing you a view of the data as it existed when the transaction started.
SQL> select sum(balance) from accounts;
SUM(BALANCE)
------------
600
SQL> insert into temp_accounts
2 select * from accounts where account = 300000;
1 row created.
SQL>
Returning to Session 2, we finish the other half of our transaction and commit the change to the database.
SQL> update accounts set balance = 100 where account = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL>
Finally, returning to Session 1, we check what we "accumulated" in our sum() by checking the values stored in temp_accounts.
SQL> select * from temp_accounts;
ACCOUNT BALANCE
---------- ----------
1 500
300000 100
SQL>
This is what we would expect to see.
Finally, we clean up our tables used for testing. Because we're using a global temporary table that is in use by a session, we need to quit the session and start a new one in order to drop the global temporary tables (temporary tables perform differently in Oracle than SQL Server, and this behavior is irrelevant to this particular article).
drop table accounts;
drop table temp_accounts;
SQL Server's Way
Having seen how Oracle does it (the optimistic view), let's have a look at SQL Server (the pessimistic view).On SQL Server, the default is that we need to explicitly tell SQL Server Management Studio that we want a transaction.
We will replicate all of the steps performed in the Oracle example again here for SQL Server.
Since what we're looking at here is default behavior, we'll create a new database and setup the data structures we'll be using.
use tempdb
go
create database ee_test_accounts
go
use ee_test_accounts
go
if object_id('accounts','U') is not null drop table accounts
go
create table accounts (account int, balance money)
go
insert into accounts values (1, 500)
insert into accounts values (300000,100)
go
if object_id('tempdb..#accounts','U') is not null drop table #accounts
create table #accounts (account int, balance money)
insert #accounts
select * from accounts where account = 1
Our sum() has started and the value as read by the query has been stored in #accounts.
We go over to session 2 now, and begin the transaction and perform the first half of our update. We do not commit the transaction at this point.
use ee_test_accounts
go
begin transaction
update accounts set balance = 500 where account = 300000
Back over in Session 1, the sum() function reaches account 300000 and attempts to query it.
select sum(balance) from accounts
insert #accounts
select * from accounts where account = 300000
The session sits and spins. The database has detected an uncommitted change on the rows trying to be read. In SQL Server's READ COMMITTED implementation, a lock is placed on any row that has been updated but not yet committed which blocks readers. Session 1 is being blocked by Session 2 and must wait.
At this point in time, session 2 now performs the other half of the transaction and commits the transaction to the database, releasing the lock.
update accounts set balance = 100 where account = 1
commit
Returning now to Session 1, we see that the above query, which was waiting, has now completed.
It shows us a balance of $1,000.
Wait a minute! That's not right!
Let's check the #accounts table to see what was accumulated by the sum() function:
select * from #accounts
account balance
----------- ---------------------
1 500.00
300000 500.00
(2 row(s) affected)
Now, since a clean database is a good database, we'll clean up our mess before we continue on and analyze what we found here.
if object_id('accounts','U') is not null drop table accounts
go
use tempdb
go
drop database ee_test_accounts
go
From the SQL Server Read Committed Isolation level and the way it's implemented what we are seeing here is the "correct" data - the value for account 300000 was read after it was committed to the database but the net result is that the $400 involved in the transaction has been summed twice - once when it was still in account 1 and then again after it was put into account 300000.
Under this very specific set of circumstances, our query has, in fact, returned incorrect data!
(Again, it should be noted that this is a very specific example that has to be executed under these very specific circumstances in order to replicate, but the behavior does exist in this specific circumstance).
SQL Server and Snapshot Isolation
If I was wanting an accurate picture of my account balances under this scenario, it appears to be that Snapshot Isolation is what's needed, but even then it can be a little tricky.The setup is just a little different in this example. After we create the ee_temp_accounts database, we need to turn on ALLOW_SNAPSHOT_ISOLATION.
create database ee_test_accounts
go
alter database ee_test_accounts set allow_snapshot_isolation on
If we run through the exact same scenario again, but this time, before we do anything in either session we issue SET TRANSACTION ISOLATION LEVEL SNAPSHOT we notice that the behavior of the transaction performs exactly like the Oracle example.
The beginning of the code for the two sessions would look like this:
-- Session 1
set transaction isolation level snapshot
insert #accounts
select * from accounts where account = 1
-- Session 2
use ee_test_accounts
go
set transaction isolation level snapshot
begin transaction
update accounts set balance = 500 where account = 300000
If we do not set the transaction isolation level in session 1, it behaves exactly like it did before. Why do we need to set the transaction isolation level in session 1 when all we're doing is reading from the accounts table?
The SET TRANSACTION statement changes the default isolation level of the transaction. By default, a session will still behave under the standard READ COMMITTED logic. If we tell the querying session to use SNAPSHOT isolation, then instead of trying to read directly from the database table, it will read from the version store in tempdb to get the version of the row as it existed at the start of the transaction.
There are two different versions of SNAPSHOT isolation - the one we've gone through above and Read Committed Snapshot Isolation.
Microsoft provides an excellent example of the different behavior under snapshot isolation and read committed snapshot isolation on their website (that uses vacation days instead of a bank account as the example).
So, there's a definite "Gotcha!" sitting in there. If this kind of level of accuracy is important to you and your application, make sure you completely understand how Isolation levels work in the database system you're using.
Credits/References
I should re-iterate that the demonstration scenario used in this post was used in Tom Kyte's article as linked above and replicated here in order to compare and contrast the differences between the default behavior in both Oracle and SQL Server and how it can be modified by using other options.I also wish to acknowledge EE Page Editor mark_wills for his assistance in making the above examples much clearer to understand.
Conclusion
If you are a DBA (or developer for that matter), it is important to understand the way that your database system behaves under various settings. The default behavior is not necessarily the same between vendors and you should make sure that the applications you develop or support work the way you expect them two under whatever behavior your database system is configured for, otherwise you may find yourself facing some unexpected results.The original version of this article was first published on by blog: http://www.dba-in-exile.com/2012/11/isolation-levels-in-oracle-vs-sql-server.html
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)