Search code examples
phpmysqltransactionsaciddatabase-concurrency

Deadlocks and Timeouts break ACID transactions

I've got transactional application that works like so:

try {
     $db->begin();
     increaseNumber();
     $db->commit();
} catch(Exception $e) {
     $db->rollback();
}

And then inside the increaseNumber() I'll have a query like so, which is the only function that works with this table:

// I use FOR UPDATE so that nobody else can read this table until its been updated
$result = $db->select("SELECT item1
FROM units
WHERE id = '{$id}'
FOR UPDATE");

$result = $db->update("UPDATE units SET item1 = item1 + 1
WHERE id = '{$id}'");

Everything is wrapped in a transaction but lately I've been dealing with some pretty slow queries and there's a lot of concurrency going on in my application so I can't really make sure that queries are to be run in a specific order.

Can deadlocks cause ACID transactions to break? I have one function that adds something and then another that removes it but when I have deadlocks then I find the data is completely out of sync like the transactions were ignored.

Is this bound to happen or is something else wrong?

Thanks, Dominic

Solution

  • A in ACID stands for Atomic, so no deadlocks cannot make an ACID transaction break -- Rather it will make it not happen like in all-or-nothing.

    More likely, if you inconsistent data, you application is doing multiple "transactions" in what is a logical single transaction, like: user creates and account (transaction-begin..-commit), user sets a password (transaction-begin...-deadlock..-rollback) your application ignored the error and continues, and now your database is left with a user created and no password.

    Look in your application as what else the application is doing besides the rollback, and logically whether there is multiple parts to the build up of the consistent data.