I have a transaction that wraps two code paths that update/insert related tables in the opposite order. Outside of a transaction I understand why that would cause a deadlock, but why does it happen inside a transaction? I must be misunderstanding the relationship between locks and transactions (are they completely unrelated? What I had assumed was that the transaction would prevent parallel execution entirely). Pseudo-code:

    IF (SELECT something FROM table_1) THEN
        UPDATE table_2
        UPDATE table_1
        INSERT INTO table_1
        UPDATE table_2
    END IF

(I've done my best to reverse the order of the UPDATEs to avoid deadlock potential but I have a couple code paths where that is not a good option)

FYI: Error message is: Deadlock found when trying to get lock; try restarting transaction and as far as I can tell is caused by more than one parallel process invoking the same DB call at the same time.

  • @mustaccio Of course, and notice the code paths in the pseudo code are mutually exclusive. The deadlock is due to multiple parallel calls into the same code at nearly the same time.
    – chell
    Mar 2 at 22:32
  • Does that SELECT have the FOR UPDATE clause on the end?
    – Rick James
    Mar 2 at 22:44
  • @RickJames No it does not. Is doing so advisable as a generic lock that I thought "START TRANSACTION" was doing? Does it lock the transaction even if the UPDATES are not against that table (or are against more than that table)?
    – chell
    Mar 2 at 23:32

1 Answer 1


When a SELECT is "inside" a transaction (that is, between the BEGIN and COMMIT),

Case 1: The Select has nothing to do with the subsequent updates/inserts/deleted (inside the transaction). It may as well be done before the BEGIN, thereby making the transaction run a little faster, hence a little less likely to collide with other connections.

Case 2: SELECT ... FOR UPDATE -- This implies that you are checking something in the table in order to decide whether the row should be updated/etc. The FOR UPDATE announces to other connections "Please do not change these row(s); I will get confused if you do." This locking mechanism expires at COMMIT.

So, add FOR UPDATE and see if the problem is solved.

Are both connections performing the pseudo-code you listed? If so... The pairs of writing to table1 and table2 could, perhaps, be done in the same order. This may turn a deadlock into a "lock wait" (cf, innodb_lock_wait_timeout)

  • To be clear, it sounds like my assumption that START TRANSACTION provides protection against parallel execution was wrong? Transactions only guarantee the block is atomic?
    – chell
    Mar 3 at 10:54
  • Is using FOR UPDATE on table_1 sufficient as a lock even though table_2 is also being written?
    – chell
    Mar 3 at 10:55
  • What if there is no SELECT? Then how would one attain a lock and prevent deadlock?
    – chell
    Mar 3 at 10:56
  • @chell - Your example has a SELECT. If you have a different example, please provide it.
    – Rick James
    Mar 3 at 18:43
  • Maybe replace the if (SELECT) with with if (var = x)
    – chell
    Mar 4 at 19:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.