Deadlocks and Timeouts break ACID transactions

Question

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

score 2 · Answer 1 · answered Jul 13 '11 at 08:07

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.

Mmm I didn't think about that. So if I'm using MySQL Workbench and working with rows and I don't commit the transaction on production then I'm holding a lock. Will look into this I guess — creamcheese, Jul 13 '11 at 09:03
You can check it with a `SHOW FULL PROCESSLIST`, see if there's anything suspicious. — Naltharial, Jul 13 '11 at 09:09

score 1 · Accepted Answer · answered Jul 13 '11 at 08:09

1

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.

answered Jul 13 '11 at 08:09

Soren

14,402
4
41
67

Hey, I've added an example of the query being used (which is only used in this place and is only ever wrapped in a transaction). The reason I thought deadlocks may break the transaction is cause when I do get alot of deadlocks is when the data is inconsistent in that units table. I'm confused though because it's only ever used in this one place... perhaps its a problem with the FOR UPDATE which allows 2 people to edit the data when a deadlock releases the locks? – creamcheese Jul 13 '11 at 08:20
What exactly are you seeing which is wrong when you suspect deadlocks are causing inconsistencies? As far as I can tell from the code above, you only update one record, so at the end of the ACID transaction it is either updated or not -- the "or not" being the result of a rollback. Inconsistencies requires two updates to happen in same transaction, where you see one update happening but not the other -- I don't see anything like that in your code. – Soren Jul 13 '11 at 08:25
I have a table of tasks that need performing. If two people query the task table I use the FOR UPDATE to make sure only one person performs the task. One person may set item1 to 1 and then the other person may want to set it to 0. When there are deadlocks then the item1 value can sometimes be 2,3,4,5,6 when it should only ever be 0 or 1 so I get the feeling that when the deadlock occurs the transaction setting it to 0 isn't successful and it becomes 1 + 1 = 2. – creamcheese Jul 13 '11 at 08:37
1

But you have no code which set it to 1 or 0, only code which sets it to value+1 (and possible value-1) somewhere else. So how do you grantee that the value can only be either 0 or 1? If the answer is that you just selected it in the previous statement and then checked it in code, then check if the database format guarantee read consistency (I believe isam and innodb behaves differently, extending the thinking along the lines of @Naltharial) – Soren Jul 13 '11 at 08:47
If possible, try to combine the select and update as one statement? Would that work? – Soren Jul 13 '11 at 08:48
Sorry I didn't post the entire codepiece as I wanted to keep it simple and to the point wondering if Deadlocks can cause ACID to break which to me seemed silly :) I guess I'll have to go back to the drawing board and check what else could be happening as it must be something else. It seems to be caused by deadlocks so must be something that happens when it behaves like that I think. – creamcheese Jul 13 '11 at 09:02

Deadlocks and Timeouts break ACID transactions

2 Answers2