[SOLVED] firebird - deadlock update conflicts with concurrent update

firebird - deadlock update conflicts with concurrent update

I'm maintaining an old software (Firebird 2.5 and C#.net). Recently we get a lot of "deadlock update conflicts with concurrent update" errors. I checked the transaction settings. It doesn't set Wait time out option:

public override IDbTransaction BeginTransaction(IDbConnection conn)
{
    FbTransaction trans = null;

    if (conn.State != ConnectionState.Open)
        conn.Open();

    FbTransactionOptions op = new FbTransactionOptions();
    op.TransactionBehavior = FbTransactionBehavior.ReadCommitted | FbTransactionBehavior.RecVersion;

    trans = ((FbConnection)conn).BeginTransaction(op);

    return trans;
}

So, why do we get timeout? Shouldn't it wait for one transaction to be committed to commit the next one?

Solution

A "deadlock update conflicts with concurrent update" happens when multiple transaction want to modify the same row. Only one updater can really change the row and commit. As long as the first transaction hasn't committed, the update in the second transaction will wait (indefinitely or until the configured timeout). As soon as the first transaction commits, the update in the second transaction will end with this error (if instead the first transaction had rolled back, the second would have continued).

If this started happening recently, you need to identify what changed. Did another tool also start writing to the database, did the number of users increase, did you upgrade something (eg Firebird, or the Firebird ado.net provider version, etc), did you make a change that resulted in long running transactions performing updates?

Your application code will need to be changed to automatically retry on this error. Also make sure that your transactions aren't too 'long' time-wise (the longer transactions are, the more chance of these types of errors occurring). In addition, you could try and change the transaction behavior from FbTransactionBehavior.RecVersion to FbTransactionBehavior.NoRecVersion, but that can introduce waiting when reading records that are currently updated by concurrent transactions, and it may actually increase occurrence of update conflicts if the record was updated (and committed) by a transaction with a newer transaction id.