[MCOL-4988] Table lock remained after DML failure due to cluster was in readonly mode Created: 2022-02-04  Updated: 2023-09-22  Resolved: 2023-08-16

Status: Closed
Project: MariaDB ColumnStore
Component/s: DMLProc
Affects Version/s: 6.2.3, 23.02.4
Fix Version/s: 23.10.0

Type: Bug Priority: Blocker
Reporter: Daniel Lee (Inactive) Assignee: Gagan Goel (Inactive)
Resolution: Fixed Votes: 0
Labels: rm_stability
Environment:

Build tested: 6.2.3-1 (#3752), CMAPI 1.6.2 (#600)
Test on a 3-node cluster using Centos 8

This issue was identified when testing MCOL-4954

Tested the follow commands (aliases)

mcsModule
mcsShutdown
mcsStart
mcsReadOnly
mcsReadWrite

After setting the cluster in readonly mode, I tried an insert statement. A DBRM read-only error returned as expected, but it left a table lock in place. After setting the cluster back to readwrite mode, subsequent DML statements also failed because of the table lock.

When the cluster is in readonly, a failed DML state should clear the table lock. 

[centos8:root~]# $MCSCLIENT mytest -vvv -e "insert into quicktest values (12, 'readwrite mode')"
 
--------------
 
insert into quicktest values (12, 'readwrite mode')
 
--------------
 
 
 
Query OK, 1 row affected (0.236 sec)
 
 
 
Bye
 
[centos8:root~]# mcsReadOnly
 
{
 
  "timestamp": "2022-02-02 20:26:38.826101",
 
  "cluster-mode": "readonly"
 
}
 
[centos8:root~]# $MCSCLIENT mytest -vvv -e "insert into quicktest values (12, 'readwrite mode')"
 
--------------
 
insert into quicktest values (12, 'readwrite mode')
 
--------------
 
 
 
ERROR 1815 (HY000) at line 1: Internal error: CAL0001: Insert Failed:   a BRM Set hwm error. [BRM error status: DBRM is in READ-ONLY mode]  
Bye
 
[centos8:root~]# viewtablelock
 
 There is 1 table lock
 
 
 
  Table             LockID  Process  PID   Session  Txn  CreationTime              State    DBRoots  
  mytest.quicktest  7       DMLProc  7782  86       15   Wed Feb  2 20:26:48 2022  LOADING  1,2,3    
[centos8:root~]# mcsReadWrite
 
{
 
  "timestamp": "2022-02-02 20:29:41.277906",
 
  "cluster-mode": "readwrite"
 
}
 
[centos8:root~]# $MCSCLIENT mytest -vvv -e "insert into quicktest values (12, 'readwrite mode')"
 
--------------
 
insert into quicktest values (12, 'readwrite mode')
 
--------------
 
 
 
ERROR 1815 (HY000) at line 1: Internal error: CAL0001: Insert Failed:  MCS-2009: Unable to perform the insert operation because DMLProc with PID 7782 is currently holding the table lock for session 86.


Issue Links:
Relates
relates to MCOL-4912 MCS bulk insertion is slow Closed
relates to MCOL-4917 Certain Extent Map operations doesn't... Closed
relates to MCOL-4954 Add support for new DBRM socket bytes... Closed
Sprint: 2023-8
Assigned for Review: Leonid Fedorov Leonid Fedorov
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Comments   
Comment by Roman [ 2022-02-05 ]

This issue surely must be researched b/c I don't see the issue(single-node, no S3, direct mode set with dbrmctl). See the output.

root@drrtuy-u20-2 /data/mdb-server/storage/columnstore/columnstore/versioning/BRM (MCOL-4580-extent-range-keeping-for-dictionaries-develop)$ dbrmctl status
 
OK.
 
root@drrtuy-u20-2 /data/mdb-server/storage/columnstore/columnstore/versioning/BRM (MCOL-4580-extent-range-keeping-for-dictionaries-develop)$ dbrmctl readonly
 
OK.
 
 
 
MariaDB [test]> 
MariaDB [test]> insert into cs1 values (100);
 
ERROR 1815 (HY000): Internal error: CAL0001: Insert Failed:  BRM error executing bulk update of HWM and CP. [BRM error status: DBRM is in READ-ONLY mode]  
ing/BRM (MCOL-4580-extent-range-keeping-for-dictionaries-develop)$ dbrmctl readwrite
 
OK.
 
root@drrtuy-u20-2 /data/mdb-server/storage/columnstore/columnstore/versioning/BRM (MCOL-4580-extent-range-keeping-for-dictionaries-develop)$ dbrmctl status
 
OK.
 
 
 
MariaDB [test]> insert into cs1 values (100);
 
Query OK, 1 row affected (0.080 sec)

Comment by David Hall (Inactive) [ 2022-03-04 ]

This may be fixed when MCOL-4917 is complete. It should be tested – assuming a reproducable case can be found.

Comment by Daniel Lee (Inactive) [ 2022-03-29 ]

Build tested: 6.2.3-1 (#3675)
drrtuy alexey.vorovich David.Hall
Reprodoced the issue in 6.2.3-1
I also tested 6.3.1-1 (#4101), which also has the same issue.

Just in case OS makes a difference, the original test was done on Centos 8 and this new test was done on Rocky 8.

[rocky8:root~]# viewtablelock
 
 No tables are locked in the database.
 
[rocky8:root~]# dbrmctl readonly
 
OK.
 
[rocky8:root~]# mariadb mytest
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 9
 
Server version: 10.6.6-3-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [mytest]> insert into t1 values (1);
 
ERROR 1815 (HY000): Internal error: CAL0001: Insert Failed:  BRM error executing bulk update of HWM and CP. [BRM error status: DBRM is in READ-ONLY mode]  
MariaDB [mytest]> exit
 
Bye
 
[rocky8:root~]# viewtablelock
 
 There is 1 table lock
 
 
 
  Table      LockID  Process  PID   Session  Txn  CreationTime              State    DBRoots  
  mytest.t1  5       DMLProc  6588  9        12   Tue Mar 29 19:27:21 2022  LOADING  1
 
  [rocky8:root~]# mariadb mytest
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 10
 
Server version: 10.6.6-3-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [mytest]> insert into t1 values (1);
 
ERROR 1815 (HY000): Internal error: CAL0001: Insert Failed:  MCS-2009: Unable to perform the insert operation because DMLProc with PID 6588 is currently holding the table lock for session 9.  
MariaDB [mytest]>

Comment by Daniel Lee (Inactive) [ 2023-06-09 ]

Build tested: develop-23.02 branch

engine: 727170dc6629dc3b9fe266039e8764bf20138c3a
server: 805750b3a90ed4aecbf475025e63674aaab7f7f7
buildNo: 7945

The issue still exists

[rocky8:root@rocky8~]# viewtablelock
 
 No tables are locked in the database.
 
[rocky8:root@rocky8~]# dbrmctl status
 
OK.
 
[rocky8:root@rocky8~]# dbrmctl readonly
 
OK.
 
[rocky8:root@rocky8~]# dbrmctl status
 
DBRM is currently Read Only!
 
[rocky8:root@rocky8~]# mariadb mytest
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 17
 
Server version: 10.6.13-8-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [mytest]> insert into cs1 values (100);
 
ERROR 1815 (HY000): Internal error: CAL0001: Insert Failed:  BRM error executing bulk update of HWM and CP. [BRM error status: DBRM is in READ-ONLY mode]  
MariaDB [mytest]> exit
 
Bye
 
[rocky8:root@rocky8~]# dbrmctl status
 
DBRM is currently Read Only!
 
[rocky8:root@rocky8~]# viewtablelock
 
 There is 1 table lock
 
 
 
  Table       LockID  Process  PID    Session  Txn  CreationTime              State    DBRoots  
  mytest.cs1  4       DMLProc  13393  17       13   Fri Jun  9 15:29:31 2023  LOADING  1        
[rocky8:root@rocky8~]# dbrmctl readwrite
 
OK.
 
[rocky8:root@rocky8~]# dbrmctl status
 
OK.
 
[rocky8:root@rocky8~]# viewtablelock
 
 There is 1 table lock
 
 
 
  Table       LockID  Process  PID    Session  Txn  CreationTime              State    DBRoots  
  mytest.cs1  4       DMLProc  13393  17       13   Fri Jun  9 15:29:31 2023  LOADING  1        
[rocky8:root@rocky8~]# mariadb mytest
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 18
 
Server version: 10.6.13-8-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [mytest]> insert into cs1 values (100);
 
ERROR 1815 (HY000): Internal error: CAL0001: Insert Failed:  MCS-2009: Unable to perform the insert operation because DMLProc with PID 13393 is currently holding the table lock for session 17.  
MariaDB [mytest]>

Comment by Daniel Lee (Inactive) [ 2023-08-16 ]

Build verified: develop latest

engine: f55d41c0792c98c760fdb6e69a1ed0aff87aa42b
server: 62d6100a913699fec9ff48284a76bfe6226e70bc
buildNo: 8435

Repeated above test case.

Generated at Thu Feb 08 02:54:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.