Getting error can't serialize access for this transaction
-
Hi All, I am using Oracle Transaction for getting records from Oracle 11g from my .net Application while retreiving the records I am getting the error, ORA-08177 can't serialize access for this transaction error. How can this problem be solved from transactions while using in .net Application. Thanks.
-
Hi All, I am using Oracle Transaction for getting records from Oracle 11g from my .net Application while retreiving the records I am getting the error, ORA-08177 can't serialize access for this transaction error. How can this problem be solved from transactions while using in .net Application. Thanks.
Hi, There are several possible reasons for this. Beasically the problem is that when the transaction is run in
SERIALIZABLEmode some data change has been encountered so the transaction fails. IfREAD COMMMITTEDwould have been used the data would have been gotten based on SCN's and undo information. Few things you could check: - are triggers involved - do you have autonomus transactions - is the SERIALIZABLE mode hte isolation level you really want to useThe need to optimize rises from a bad design.My articles[^]
-
Hi, There are several possible reasons for this. Beasically the problem is that when the transaction is run in
SERIALIZABLEmode some data change has been encountered so the transaction fails. IfREAD COMMMITTEDwould have been used the data would have been gotten based on SCN's and undo information. Few things you could check: - are triggers involved - do you have autonomus transactions - is the SERIALIZABLE mode hte isolation level you really want to useThe need to optimize rises from a bad design.My articles[^]
Hi, Thanks for reply.I am using the Serializable mode but there no autonomus transactions and triggers.But, there are only multiple queries like first there is an insertion in a table, then deletion from the same table and then selection from other tables and so on. Also, while doing a new transaction the connection is reopened every time with the Oracle. Thanks.
-
Hi, Thanks for reply.I am using the Serializable mode but there no autonomus transactions and triggers.But, there are only multiple queries like first there is an insertion in a table, then deletion from the same table and then selection from other tables and so on. Also, while doing a new transaction the connection is reopened every time with the Oracle. Thanks.
As this is a concurrency problem it's kinda hard pointing out the problem without seeing the whole scenario. However to demonstrate the problem, do the following: 1. Create a test table and insert data into it
CREATE TABLE Test (
a NUMBER PRIMARY KEY NOT NULL
);
INSERT INTO Test VALUES (1);
COMMIT;2. Now in this session start a transaction and delete the rows. Leave the transaction open
-- SESSION 1
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
DELETE FROM Test;3. Start another session and select data from the test table in serializable mode
-- SESSION 2
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
SELECT a FROM Test FOR UPDATE OF a;4. The session 2 hangs and waits for the result from session 1. Now go back to session 1 and commit the transaction:
-- SESSION 1
COMMIT;The result in session 2 is:
SELECT a FROM Test FOR UPDATE OF a
*
ERROR at line 1:
ORA-08177: can't serialize access for this transactionHopefully this clarifies the problem. Best regards, mika
The need to optimize rises from a bad design.My articles[^]