![]() ![]() 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.Īs suggested in the above, we will need two sessions for this test.įirst start a new SQL*Plus session and do the setup:ĭeclare 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 Oracle's SQL*Plus, differently from SQL Server Management Studio, has an implicit transaction by default. ![]() Account 1 has a balance of $500 and Account 300000 has a balance of $100. I load a table with two rows representing either end of a list of accounts. Oracle's Way Let's start by looking at how Oracle handles it. 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): How do both database systems handle this ? The second account is then read in the first session before the second session is committed. 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. After 200,000 records are read a transaction is started in the database in another session. The database begins reading data and accumulating balances. The table has many hundreds of thousands rows.Ī query is started to sum balances. 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.Ī bank account database has a table for balances that contains two columns, account and balance. If you were just looking at transactional data, you would see both updates looking just fine. (He also covers some of the other isolation levels but you can read his article for more on those).Įxample Setup To begin with, I should note that this is a very specific example of how this situation could occur. I'll use his example here as it is what I used for my own testing. 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. 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. Oracle, on the other hand has an "optimistic" view of 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. ![]() The Difference In basic terms, SQL Server has a "pessimistic" view of data concurrency by default. Since READ COMMITTED is the default isolation level for both database systems, it seemed to be a good place to base the comparison on. You can read more about this option in the Oracle Documentationīecause 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. Oracle adds in their own extra option called Read Only Isolation. Oracle, on the other hand, does not implement Read Uncommitted and also does not implement Repeatable Read. To learn more about SNAPSHOT Isolation, refer to Books Online. 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. 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). 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. Going into the details of what each of these mean is outside the scope of what I want to write about today.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |