aspose file tools*
The moose likes JDBC and the fly likes ORA-08177: can't serialize access for this transaction Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ORA-08177: can Watch "ORA-08177: can New topic
Author

ORA-08177: can't serialize access for this transaction

jyoti vermakat
Greenhorn

Joined: Oct 04, 2013
Posts: 8
ORA-08177: can't serialize access for this transaction

I am facing this issue...from long back.

As on doing serch on google, got the information that

1) I should increase the value of initrans, so i have did the same(tried from 3-255 so many values) , but the problem have not been resolved.

2) As i have not set the isolation level in my application, so by default it is READ_COMMITED.

Not able to understand why this issue is coming, and how i should reslove from database side.

Please help me as this issue is pending from long back.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Welcome to the Ranch!

According to the docs, ORA-08177 can be caused only by serializable transactions. It means that a row which the serializable transaction is trying to modify was modified by another transaction after the serializable transaction has begun. Similar situation can happen with the read committed isolation level, but in this case the database automatically and silently restarts the entire SQL statement, and no error occurs.

So my guess is that your transaction is serializable after all. Default isolation level can be set, I believe, in the connection, and perhaps also in connection properties. Are you sure you've checked every possible place? You should be able to easily verify this by calling getTransactionIsolation() on the connection.
jyoti vermakat
Greenhorn

Joined: Oct 04, 2013
Posts: 8
Thanks Martin,

I tried with getTransactionIsolation() method on the connection, its SERIALIZABLE.

I changed it to READ_COMMITED, issue got resolved, but we need to keep it SERIALIZABLE only, as this is a banking application. so what to do now ?

As i have already checked with INITRANS value, As the initrans new value will be applicable only on new blocks, i have dropped the table and recreated it, but it not worked. so what should i do by which this error will resolve with SERIALIZABLE isolation level.



Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Increasing INITRANS will help to achieve higher concurrency, but it must be done on the table and all its indexes, and the ORA-08177 error can still occur, in cases where a row you're trying to update has been already modified by a different transaction. Edit: creating the table in ROWDEPENDENCIES mode might also help, I believe.

The only cure for the ORA-08177 error is to rollback changes and re-run the entire business transaction. If there are no update collision on the next run, the transaction will finish.

The important thing is that the entire business transaction (its complete logic) has to be re-run. The typical scenario can be easily described on a banking account. Let's say that we have an account with a balance of $100, and there are two processes (business transactions) trying to subtract $75 each from that account. This is how the timing might work like:

  • Transaction 1: starts serializable transaction (all data read from the database will correspond to the state of the database at this moment)
  • Transaction 2: starts serializable transaction (all data read from the database will correspond to the state of the database at this moment)
  • ------
  • T1: reads the ballance of the account (sees $100)
  • T1: compares the account to the amount to subtract ($75) - there is enough money, so the transaction proceeds
  • T1: updates the account in the database: subtracts $75. The current balance is $25
  • T1: commits
  • ------
  • T2: reads the ballance of the account (sees $100, even though the T1 has already committed, because the transaction is serializable)
  • T2: compares the account to the amount to subtract ($75) - there is enough money, so the transaction proceeds
  • T2: tries to update the account in the database. Since it was modified by T1, ORA-08177 is thrown.
  • ------
  • T2 restarts the entire process
  • T2: rollback (important!)
  • T2: starts serializable transaction (all data read from the database will correspond to the state of the database at this moment)
  • T2: reads the ballance of the account (sees $25)
  • T2: compares the account to the amount to subtract ($75) - there is not enough money, so the transaction doesn't proceed, but informs the client that there are not enough money on the account.
  • T2: rollback (important!)


  • Hopefully this scenario illustrates how serializable isolation level help to protect the integrity of the data. The exact order of individual statements can be different. The important thing is that the first transaction that updates the row gets through, while the second transaction fails with ORA-08177.

    I'd say that you'd really benefit from reading the Oracle's Concepts Guide (this is for version 11.2, google for the guide of your version if you're using another). Developing a banking application requires very good understanding of the processing in the database, in my opinion, and Concepts Guide is an excellent introduction into it.
    jyoti vermakat
    Greenhorn

    Joined: Oct 04, 2013
    Posts: 8
    Thanks a lot Martin,

    I tried with the table in ROWDEPENDENCIES mode. My application is working fine now.

    Thanks a lot again.
     
    Consider Paul's rocket mass heater.
     
    subject: ORA-08177: can't serialize access for this transaction