RohitPatel9999 <rohitpatel9999-/[EMAIL PROTECTED]> wrote:
Will BEGIN IMMEDIATE surely avoid deadlock ? or BEGIN EXCLUSIVE is better ? i.e. in such cases, what advantage BEGIN IMMEDIATE gives over BEGIN EXCLUSIVE ?
BEGIN IMMEDIATE will avoid deadlocks when used consistently for all transactions that begin with SELECT then proceed to write. BEGIN IMMEDIATE is better because it allows the writer to work in parallel with readers up to the point where it actually needs to physically write to disk (upon commit, or when in-memory cache fills up and has to spill).
One problem with BEGIN IMMEDIATE is that you can get a busy timeout at pretty much any point, including COMMIT. So you need to write complex error handling logic to deal with this situation. Imagine a transaction where you need to read some data, perform long a complex calculations and finally update the data. What are you going to do if update times out? Wait for arbitrarily long time, restart from scratch, fail the whole operation altogether?
BEGIN EXCLUSIVE sort of solves this problem by moving the point of failure up to the beginning of the transaction. BEGIN EXCLUSIVE statement itself may time out, but once it succeeded, you are the only one touching the database and no timeouts may occur. The downside is, of course, that nobody else may read from the database while you perform your calculations and prepare for writing.
Igor Tandetnik
