|
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?)
|
|
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
|
|
lock_test_setup.sql