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.