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
6 1921
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.
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)
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.
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 )
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: extmb |
last post by:
Hi,
I am quite puzzled how SQLServer manages transactions.
Whatever the isolation level I set when performing an insertion, other
connections do not have access to the table in select mode.
...
|
by: Leaf |
last post by:
Greetings,
I've been reading with interest the threads here on deadlocking, as I'm
finding my formerly happy app in a production environment suddenly
deadlocking left and right. It started...
|
by: Eddie |
last post by:
I wondering which one of the following I should use to get the best
performance.
1. "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
OR
2. "WITH (NOLOCK)"
I notice that when I use the #1...
|
by: Eric Porter |
last post by:
Dear All,
I have a VB6.COM program using a VB6.COM DLL, which in turn uses ADODB that
performs various bits of SQL.
I have re-written the VB6.COM DLL in C#.NET (with a VB6 shell), which uses...
|
by: Mike P |
last post by:
I am using transactions on my website and the Isolation Level is
ReadCommitted. Since the website has a lot of traffic this may be
causing it to lock up every now and again.
Can somebody tell...
|
by: kanda |
last post by:
Hello.
I am developing the application (VBA&ODBC, to be exact) which
periodically calls the stored procedures in the IBM DB2. A few of the
procedures require executing with isolation level RR (...
|
by: joshsackett |
last post by:
I am redesigning an application that distributes heldesk tickets to our
50 engineers automatically. When the engineer logs into their window a
stored procedure executes that searches through all...
|
by: Christian Stooker |
last post by:
Part one:
======
Hi !
I want to use SQLite database like the FireBird database: with big
isolation level.
What's that meaning ?
I have an application that periodically check some input...
|
by: Bob Stearns |
last post by:
If I'm in a transaction started with 'odbc_autocommit($dbConn, FALSE);'
and I UPDATE a row in tablex, then later SELECT FROM tablex, will I see
the updated row or the original row? If it will help...
|
by: D. |
last post by:
I have a question about the "readCommitted" transaction isolation level.
I have a client that is updating a record on a table.
I suspend the execution after the UPDATE but before the commit...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |