Db2

 View Only

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