How to avoid deadlock inside transaction? [closed]

Question

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:

START TRANSACTION
    IF (SELECT something FROM table_1) THEN
        UPDATE table_2
        UPDATE table_1
    ELSE
        INSERT INTO table_1
        UPDATE table_2
    END IF
COMMIT

(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.

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)