• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
    Posts: 8
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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.
    reply
      Bookmark Topic Watch Topic
    • New Topic