Hi All,
I noticed a lack of concurrency when using connect by from the customer's case.
The customer is using Db2 11.5 with Oracle compatibility mode enabled.
Below is the scenario I ran on my Db2 test server.
I did a SELECT statement using CTE and CONNECT BY in one session, and executed an UPDATE in the other session.
<Test case 1>
1. Running recursive query (CTE) with auto-commit off
2. The update operation works fine.
<Test case 2>
1. Running recursive query (CONNECT BY) with auto-commit off
2. The update operation waits for a lock. (lock caused by select statement using connect by clause)
This seems to be the original behavior rather than a defect.
Is there a only way to use CTE instead of CONNECT BY clause to improve the concurrency of other SQL statements?
-- my_emp.sql
DROP TABLE my_emp;
CREATE TABLE my_emp(empid INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(10),
salary DECIMAL(9, 2),
mgrid INTEGER);
INSERT INTO my_emp
VALUES ( 1, 'Jones', 30000, 10),
( 2, 'Hall', 35000, 10),
( 3, 'Kim', 40000, 10),
( 4, 'Lindsay', 38000, 10),
( 5, 'McKeough', 42000, 11),
( 6, 'Barnes', 41000, 11),
( 7, 'O''Neil', 36000, 12),
( 8, 'Smith', 34000, 12),
( 9, 'Shoeman', 33000, 12),
(10, 'Monroe', 50000, 15),
(11, 'Zander', 52000, 16),
(12, 'Henry', 51000, 16),
(13, 'Aaron', 54000, 15),
(14, 'Scott', 53000, 16),
(15, 'Mills', 70000, 17),
(16, 'Goyal', 80000, 17),
(17, 'Urbassek', 95000, NULL);
-- with.sql
connect to sample;
update command options using c off;
WITH n(empid, name) AS
(SELECT empid, name
FROM my_emp
WHERE name = 'Goyal'
UNION ALL
SELECT nplus1.empid, nplus1.name
FROM my_emp as nplus1, n
WHERE n.empid = nplus1.mgrid)
SELECT name FROM n
;
-- connby.sql
connect to sample;
update command options using c off;
SELECT name
FROM my_emp
START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid
;
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Db2