Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
12.3.2
-
OS: Ubuntu-20.04 LTS
-
Not for Release Notes
Description
Isolation Level: Read Uncommitted.
UPDATE statement with a WHERE clause is not blocked by the INSERT statement of another transaction, while UPDATE statement without a WHERE clause is blocked by the same INSERT statement of another transaction.
Test Case 1:
/* init */ CREATE TABLE t (c1 CHAR); |
/* init */ INSERT INTO t (c1) VALUES ('4'); |
/* init */ INSERT INTO t (c1) VALUES ('a'); |
|
|
/* t1 */ BEGIN; |
/* t1 */ INSERT INTO t (c1) VALUES ('b'); |
/* t2 */ BEGIN; |
/* t2 */ UPDATE t SET c1 = 'c' WHERE (c1 SOUNDS LIKE 1); |
/* t1 */ COMMIT; |
/* t2 */ COMMIT; |
Test Case 2:
/* init */ CREATE TABLE t (c1 CHAR); |
/* init */ INSERT INTO t (c1) VALUES ('4'); |
/* init */ INSERT INTO t (c1) VALUES ('a'); |
|
|
/* t1 */ BEGIN; |
/* t1 */ INSERT INTO t (c1) VALUES ('b'); |
/* t2 */ BEGIN; |
/* t2 */ UPDATE t SET c1 = 'c'; -- blocked |
/* t1 */ COMMIT; |
/* t2 */ COMMIT; |
We expect UPDATE statement in test case 1 to be blocked.
In addition, DELETE statement with the same WHERE clause is blocked by the same INSERT statement of another transaction.
Test Case 3:
/* init */ CREATE TABLE t (c1 CHAR); |
/* init */ INSERT INTO t (c1) VALUES ('4'); |
/* init */ INSERT INTO t (c1) VALUES ('a'); |
|
|
/* t1 */ BEGIN; |
/* t1 */ INSERT INTO t (c1) VALUES ('b'); |
/* t2 */ BEGIN; |
/* t2 */ DELETE FROM t WHERE (c1 SOUNDS LIKE 1); -- blocked |
/* t1 */ COMMIT; |
/* t2 */ COMMIT; |