Transaction Isolation Level

[qhjghz]

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

[iburyak]

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:

Begin Tran
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:

Begin Tran
Insert into some_table values (……..)
Select * from some_table.

Good Luck.

[qhjghz]

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:

Begin Tran
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:

Begin Tran
Insert into some_table values (……..)
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)

[iburyak]

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.

[qhjghz]

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 )

[iburyak]

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:

Begin Tran
Insert statements here 
If @@Error > 0
Roolback Tran
Else
Commit Tran

In this case until everything is over no other process can even get to the table and read any data.

Irina.

[qhjghz]

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:

Begin Tran
Insert statements here 
If @@Error > 0
Roolback Tran
Else
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