Edit Question - KnowledgeHub

Title Body

Let's say we execute... SELECT * FROM MY_TABLE FOR UPDATE ...and there is more than one row in MY_TABLE. Theoretically, if two concurrent transactions execute this statement, but it happens to traverse (and therefore lock) the rows in different order, a deadlock may occur. For example: Transaction 1: Locks row A. Transaction 2: Locks row B. Transaction 1: Attempts to lock row B and blocks. Transaction 2: Attempts to lock row A and deadlocks. The way to resolve this is to use ORDER BY to ensure rows are always locked in the same order. So, my question is: will this theoretical deadlock ever occur in practice? I know there are ways to artificially induce it , but could it ever occur in the normal operation? Should we just always use ORDER BY, or it's actually safe to omit it? I'm primarily interested in behavior of Oracle and MySQL/InnoDB, but comments on other DBMSes would be helpful as well. --- EDIT --- Here is how to reproduce a deadlock under Oracle when locking order is not the same: Create the test table and fill it with some test data... CREATE TABLE DEADLOCK_TEST ( ID INT PRIMARY KEY, A INT ); INSERT INTO DEADLOCK_TEST SELECT LEVEL, 1 FROM DUAL CONNECT BY LEVEL <= 10000; COMMIT; ...from one client session (I used SQL Developer), run the following block: DECLARE CURSOR CUR IS SELECT * FROM DEADLOCK_TEST WHERE ID BETWEEN 1000 AND 2000 ORDER BY ID FOR UPDATE; BEGIN WHILE TRUE LOOP FOR LOCKED_ROW IN CUR LOOP UPDATE DEADLOCK_TEST SET A = -99999999999999999999 WHERE CURRENT OF CUR; END LOOP; ROLLBACK; END LOOP; END; / From a different client session (I simply sta

Tags (comma-separated) Cancel