469,578 Members | 1,849 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

Transaction Isolation Level

26
Hi All,
The transaction isolation level for my stored procedure is READ COMMITTED. As far as I read, in this isolation level, "In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run." But, I observed a deviation from this. In my session, I am explicitly starting a transaction and inserting a row into the Property table (say). This property belongs to the City Maine(say). However, I am not committing or rolling back the transaction and in this stage, from my application I am searching those properties which belong to the City Maine. The application comes to a standstill. However, when I rollback or commit the transaction, the application fetches the data. Now, why does this happen? according to the materials I read, I should get a snapshot of the data till the last committed transaction. Someone please explain
Nov 3 '07 #1
6 1723
iburyak
1,017 Expert 512MB
As far as I know if you do select within the same transaction you will see uncommitted records. Uncommitted records within other transactions can not be seen.
Try this test:
Open SQL Query Analyzer and start this statement:

Expand|Select|Wrap|Line Numbers
  1. Begin Tran
  2. Insert into some_table values (..)
Open another session within Query Analyzer and select from this table.
You should not be able to see new record until you will issue Commit Tran in the first session
But If you will do following within the same transaction it should return new uncommitted record:
Expand|Select|Wrap|Line Numbers
  1. Begin Tran
  2. Insert into some_table values (..)
  3. Select * from some_table.
Good Luck.
Nov 4 '07 #2
qhjghz
26
As far as I know if you do select within the same transaction you will see uncommitted records. Uncommitted records within other transactions can not be seen.
Try this test:
Open SQL Query Analyzer and start this statement:

Expand|Select|Wrap|Line Numbers
  1. Begin Tran
  2. Insert into some_table values (..)
Open another session within Query Analyzer and select from this table.
You should not be able to see new record until you will issue Commit Tran in the first session
But If you will do following within the same transaction it should return new uncommitted record:
Expand|Select|Wrap|Line Numbers
  1. Begin Tran
  2. Insert into some_table values (..)
  3. Select * from some_table.
Good Luck.


Yes .. it was exactly the test I performed. But it surprised me. I expected that the I should get the snapshot of the database before the transaction began. But the select statement comes to a standstill. This is not what I expected and not a favorable behaviour also (IMHO)
Nov 5 '07 #3
iburyak
1,017 Expert 512MB
To get data before you started within the same transaction save everything to a temp table do necessary manipulations and use temporary data as original.

In my procedures I use exactly opposite. I want to see data I just inserted to my advantage. For example I received data in XML format and just inserted it into the table and want to use newly inserted data for future processing. At this point other processes can't see newly inserted data but I can evaluate what was just inserted and can make a decision do I want this data to stay or remove it before process ended.

Good Luck.
Nov 5 '07 #4
qhjghz
26
To get data before you started within the same transaction save everything to a temp table do necessary manipulations and use temporary data as original.

In my procedures I use exactly opposite. I want to see data I just inserted to my advantage. For example I received data in XML format and just inserted it into the table and want to use newly inserted data for future processing. At this point other processes can't see newly inserted data but I can evaluate what was just inserted and can make a decision do I want this data to stay or remove it before process ended.

Good Luck.
Well, the scenario is a bit different. The application is up and running 24*7, but the batch loading is via Informatica. The loading is done in a single transaction. I have no access to this feed process. But, suppose, while the data is getting loaded, the application fires a query on the particular row which is uncommitted, then the application comes to a standstill. It would have been much better for me if dirty reads could have been avoided and the database would have showed me snapshot of the dtabase before the transaction began. (Also, this is default Oracle behaviour and myself, being from Oracle background, expected the same in SQL Server also )
Nov 6 '07 #5
iburyak
1,017 Expert 512MB
I know people are having multiple problems with Informatica I am not even going that way for these particular reasons. I usually upload data within transaction and when it is happening all processes are waiting for upload to finish.
If you do something like this:
Expand|Select|Wrap|Line Numbers
  1. Begin Tran
  2. Insert statements here 
  3. If @@Error > 0
  4. Roolback Tran
  5. Else
  6. Commit Tran
In this case until everything is over no other process can even get to the table and read any data.

Irina.
Nov 6 '07 #6
qhjghz
26
I know people are having multiple problems with Informatica I am not even going that way for these particular reasons. I usually upload data within transaction and when it is happening all processes are waiting for upload to finish.
If you do something like this:
Expand|Select|Wrap|Line Numbers
  1. Begin Tran
  2. Insert statements here 
  3. If @@Error > 0
  4. Roolback Tran
  5. Else
  6. Commit Tran
In this case until everything is over no other process can even get to the table and read any data.

Irina.
The problem is that the application isn't ready to wait for the BULK UPLOAD to finish. So, I wanted a transaction isolation level where dirty read won't occur, but the application should not hang at the same also
Nov 7 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Eric Porter | last post: by
3 posts views Thread by joshsackett | last post: by
2 posts views Thread by Christian Stooker | last post: by
6 posts views Thread by Bob Stearns | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.