Db2

Db2

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

Why do row locks be held by CONNECT BY, unlike queries with CTE?

  • 1.  Why do row locks be held by CONNECT BY, unlike queries with CTE?

    Posted Fri December 20, 2019 02:37 AM
    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