Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
11.8, 12.0.2, 12.3.2
-
OS: Ubuntu-20.04 LTS
-
Not for Release Notes
Description
Isolation Level: Read Uncommitted.
UPDATE statement is not blocked by the DELETE statement of another transaction, while SELECT FOR UPDATE statement that has the same WHERE clause as the UPDATE statement is blocked by the same DELETE statement of another transaction and reports deadlock.
Test Case 1:
/* init */ CREATE TABLE t (c1 NUMERIC); |
/* init */ INSERT INTO t VALUES (1); |
|
|
/* t1 */ BEGIN; |
/* t1 */ INSERT INTO t (c1) VALUES (2); |
/* t2 */ BEGIN; |
/* t2 */ DELETE FROM t; -- blocked |
/* t1 */ UPDATE t SET c1 = 3; |
/* t1 */ COMMIT; -- t2 unblocked |
/* t2 */ ROLLBACK; |
/* t2 */ SELECT * FROM t; -- [(1), (3)] |
Test Case 2:
/* init */ CREATE TABLE t (c1 NUMERIC); |
/* init */ INSERT INTO t VALUES (1); |
|
|
/* t1 */ BEGIN; |
/* t1 */ INSERT INTO t (c1) VALUES (2); |
/* t2 */ BEGIN; |
/* t2 */ DELETE FROM t; -- blocked |
/* t1 */ SELECT c1 FROM t FOR UPDATE; -- deadlock |
/* t2 unblocked */ DELETE FROM t; |
/* t2 */ ROLLBACK; |
/* t2 */ SELECT * FROM t; -- [(1)] |
We expect UPDATE statement in test case 1 to report deadlock, and the final database to be [(1)].
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-
- is duplicated by
-
-
- Closed
-