Elena Stepanova added a comment - 2018-12-28 20:21 What kind of table lock do you observe, and where do you see it?

Dmitry Pustovalov added a comment - 2019-01-01 22:52 - edited I observe shared lock on agent table. I attach minimal reproducible example for my issue, sql-script creating database with one table, few rows and stored procedure. After applying script it's easy to reproduce problem: SESSION 1. MariaDB [lock_test_db]> set autocommit 0 ; Query OK, 0 rows affected ( 0.00 sec)   MariaDB [lock_test_db]> call lock_test( 1 , 1 ); +---+ | 1 | +---+ | 1 | +---+ 1 row in set ( 0.00 sec)   Query OK, 0 rows affected ( 0.00 sec) SESSION 2. MariaDB [lock_test_db]> set autocommit 0; Query OK, 0 rows affected (0.00 sec)   MariaDB [lock_test_db]> select * from agent where id = 1 lock in share mode; + ----+------------+ | id | agent_guid | + ----+------------+ | 1 | guid 1 | + ----+------------+ 1 row in set (0.02 sec)   MariaDB [lock_test_db]> update agent set agent_guid = 'new guid' where id = 1 ; ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted MariaDB [lock_test_db]> Ctrl-C -- exit! As you can see in session 2 I can acquire S-lock on row with id = 1, but I can't acquire X lock. Some innodb diagnostics: 1. innodb status show engine innodb status   ---TRANSACTION 3142821 , ACTIVE 350 sec 2 lock struct(s), heap size 360 , 1 row lock(s) MySQL thread id 41 , OS thread handle 0x7fe94be31b00 , query id 2883 localhost root init 2. INNODB_TRX MariaDB [lock_test_db]> select * from information_schema.INNODB_TRX where trx_id = 3142821 \G *************************** 1 . row *************************** trx_id: 3142821 trx_state: RUNNING trx_started: 2019 - 01 - 02 01 : 43 : 25 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 2 trx_mysql_thread_id: 41 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 2 trx_lock_memory_bytes: 360 trx_rows_locked: 1 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set ( 0.00 sec) 3. Table INNODB_LOCKS is empty (why?)

Elena Stepanova added a comment - 2019-01-02 13:51 Thanks for the clarification and test case. Indeed, there is a difference in behavior for which I have no logical (or illogical, but documented) explanation. Below is an MTR-friendly demonstration of the difference in locking, based on the provided test case. The first procedure pr1() doesn't leave any locks behind, as shown by ENGINE INNODB STATUS and can be further verified by running an UPDATE on the row with id=1. The second procedure pr2() , which is almost identical except that it runs SELECT from inside an IF condition, leaves a lock behind. Transactions look different in different versions, but the effect can be observed on all MariaDB and MySQL versions. Both REPEATABLE READ and READ COMMITTED exhibit the same behavior, the output is for READ COMMITTED. --source include/have_innodb.inc   CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, f varchar (64) DEFAULT NULL , PRIMARY KEY (id) ) ENGINE=InnoDB;   INSERT INTO t1 VALUES (1, 'guid 1' ); INSERT INTO t1 VALUES (2, 'guid 1' ); INSERT INTO t1 VALUES (3, 'guid 2' );   --delimiter $$   CREATE PROCEDURE pr1() BEGIN SELECT 1 IN ( SELECT id FROM t1 WHERE f = 'guid 1' ); SELECT 2; END $$   CREATE PROCEDURE pr2() BEGIN IF 1 IN ( SELECT id FROM t1 WHERE f = 'guid 1' ) THEN SELECT 2; END IF ; END $$   --delimiter ;   --connect (con1,localhost,root,,) SET autocommit= 0; CALL pr1();   --connection default show engine innodb status;   --connection con1 COMMIT ; CALL pr2();   --connection default show engine innodb status; 10.3 after pr1 ------------ TRANSACTIONS ------------ Trx id counter 42 Purge done for trx's n:o < 42 undo n:o < 0 state: running History list length 19 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421115617190128, not started 0 lock struct(s), heap size 1160, 0 row lock(s) after pr2 ------------ TRANSACTIONS ------------ Trx id counter 42 Purge done for trx's n:o < 42 undo n:o < 0 state: running but idle History list length 19 Total number of lock structs in row lock hash table 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421115617190128, not started 0 lock struct(s), heap size 1160, 0 row lock(s) 10.1 (with XtraDB) after pr1 ------------ TRANSACTIONS ------------ Trx id counter 1287 Purge done for trx's n:o < 789 undo n:o < 0 state: running but idle History list length 6 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1285, not started MySQL thread id 3, OS thread handle 0x7f3d9eee2b00, query id 29 localhost root init show engine innodb status ---TRANSACTION 1286, ACTIVE 0 sec MySQL thread id 4, OS thread handle 0x7f3d9ee98b00, query id 26 localhost root Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC after pr2 ------------ TRANSACTIONS ------------ Trx id counter 1288 Purge done for trx's n:o < 789 undo n:o < 0 state: running but idle History list length 6 Total number of lock structs in row lock hash table 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1285, not started MySQL thread id 3, OS thread handle 0x7f3d9eee2b00, query id 34 localhost root init show engine innodb status ---TRANSACTION 1287, ACTIVE 0 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 4, OS thread handle 0x7f3d9ee98b00, query id 31 localhost root Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC

Marko Mäkelä added a comment - 2019-04-17 13:14 I believe that this needs some fix in ha_innobase::store_lock() or ha_innobase::external_lock() or the callers of these functions.