Hello,
One of our customer's database is facing multiple locks on a daily basis. "deadlocks" were ocurring at first, but development team addressed some of the issues and are trying to figure out others. Most of these locks occur during inserts, and database hangs, and we have to mark the blocking sessions for kill.
Before proceding, i'd like to mention Justin Crave's answer to another post, that might give some ground to the question:
[...] "Of course, generating keys by taking the MAX(col_key)+n is going to be much slower than using a sequence and is not going to work in a multiuser environment."
- Dev team uses insert select max + 1 (and yes, it's a multiuser environment) because of the following:
Every insert, must validate against Brazil's Receita Federal (IRS in US) systems, and only after validation, this data can be commited. They say if they use sequence, the transaction will get a sequence number, let's say 2, and if the transaction fails, the next sequence would be 3, leaving gap sequences on failed transactions. Select max + 1 it's a workaround for that, but stills the sessions have to wait for first transaction to conlude in order to get next max + 1.
So, what are the alternatives to fix the problem? I tried re-reading the docs on sequence concepts and external articles to see if there are options for that, but couldn't find anything specific to above scenario.
Any reference papers, test cases would be appreciated.
Thanks.