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 wit开发者_JS百科h 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

Well, if a transaction runs into a lock (from another transaction) that doesn't release, it'll fail after timeout. I believe the default is 30 seconds. You should make note if anyone is using any 3rd party applications on the database. I know for a fact that, for example, SQL Manager 2007 does not release locks on InnoDB unless you disconnect from database (sometimes it only takes a Commit Transaction on ... well, everything), which causes a lot of queries to fail after timeout. Of course, if your transactions are ACID-compliant, it should execute in all-or-nothing. It will break only if you break data between transactions.

You can try extending the timeout, but a 30 second lock might imply some deeper problems. It depends, of course, on what storage engine you're using (by MySQL tag and transactions I assumed InnoDB).

You can also try and turn on query profiling to see if any queries run for a ridiculous amount of time. Just note that it does, of course, decrease performance, so it may not be a production 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.