Description:
Hello,
I tried to run a statment, which is "SELECT id, col1, col2 FROM t WHERE t.col1='xx' ORDER BY t.id FOR UPDATE" while index(col1,col2) is multiple-column indexes and id is the primary key.
Expecting the rows I concerned would be locked, it turned out that some of the other rows which col1 is not equal to 'xx' were locked.
But I found somes solutions to lock my concerned rows, and here are some examples:
1. remove the "ORDER BY" clause (What's more, the "ORDER BY" clause is necessary.)
2. use "ORDER BY col1" or "ORDER BY col2" instead of "ORDER BY t.id"
3. split index(col1,col2) into index(col1) and index(col2)
Is this a bug or not?
Thank you for your feedback.
How to repeat:
CREATE TABLE `order_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`pro_code` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_code` (`user_id`,`pro_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `order_test`(`id`, `user_id`, `pro_code`, `update_time`) VALUES
(1, 'UEGEJ6AV8S', '1000071', '2019-11-19 17:41:01'),
(2, 'UEGEJ6AV8S', '1000478', '2019-11-19 17:41:21'),
(3, 'UEXK9T0PMI', '1000479', '2019-11-19 17:51:56'),
(4, 'UEXK9T0PMI', '1162451', '2019-11-19 17:53:03'),
(5, 'UFEKJ7HMEV', '1100032', '2019-09-11 12:18:29'),
(6, 'UGQOCTQISR', '1100032', '2019-09-11 12:33:28'),
(7, 'UGQOAFDGEI', '1000098', '2019-09-11 12:33:28'),
(8, 'UHZBIF8WPP', '1100032', '2019-09-11 12:47:22'),
(9, 'UJC03NLRCS', '1162431', '2019-09-11 13:02:31');
session 1:
mysql> START TRANSACTION;
mysql> SELECT * FROM order_test WHERE user_id='UEGEJ6AV8S' ORDER BY id DESC FOR UPDATE;
+----+------------+----------+---------------------+
| id | user_id | pro_code | update_time |
+----+------------+----------+---------------------+
| 2 | UEGEJ6AV8S | 1000478 | 2019-11-19 17:41:21 |
| 1 | UEGEJ6AV8S | 1000071 | 2019-11-19 17:41:01 |
+----+------------+----------+---------------------+
2 rows in set (0.00 sec)
mysql>
session 2:
mysql> START TRANSACTION;
mysql> SELECT * FROM order_test WHERE user_id='UEXK9T0PMI' AND pro_code='1000479' FOR UPDATE;
Lock wait timeout exceeded; try restarting transaction
mysql>
The information below is what I collected when I executed session 2:
###########################################
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 22211186
trx_state: LOCK WAIT
trx_started: 2020-01-20 17:31:16
trx_requested_lock_id: 22211186:50196:4:4
trx_wait_started: 2020-01-20 17:31:16
trx_weight: 2
trx_mysql_thread_id: 62
trx_query: SELECT * FROM order_test WHERE user_id='UEXK9T0PMI' AND pro_code='1000479' FOR UPDATE
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
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: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 22211185
trx_state: RUNNING
trx_started: 2020-01-20 17:31:08
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 60
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 3
trx_lock_memory_bytes: 1136
trx_rows_locked: 5
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: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
###########################################
mysql> select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
lock_id: 22211186:50196:4:4
lock_trx_id: 22211186
lock_mode: X
lock_type: RECORD
lock_table: `sys`.`order_test`
lock_index: idx_user_code
lock_space: 50196
lock_page: 4
lock_rec: 4
lock_data: 'UEXK9T0PMI', '1000479', 3
*************************** 2. row ***************************
lock_id: 22211185:50196:4:4
lock_trx_id: 22211185
lock_mode: X
lock_type: RECORD
lock_table: `sys`.`order_test`
lock_index: idx_user_code
lock_space: 50196
lock_page: 4
lock_rec: 4
lock_data: 'UEXK9T0PMI', '1000479', 3
2 rows in set, 1 warning (0.00 sec)
###########################################
mysql> SHOW ENGINE INNODB STATUS;
......
---TRANSACTION 22211186, ACTIVE 36 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 62, OS thread handle 123145525358592, query id 654 localhost 127.0.0.1 root Sending data
SELECT * FROM order_test WHERE user_id='UEXK9T0PMI' AND pro_code='1000479' FOR UPDATE
------- TRX HAS BEEN WAITING 36 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50196 page no 4 n bits 80 index idx_user_code of table `sys`.`order_test` trx id 22211186 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 10; hex 5545584b395430504d49; asc UEXK9T0PMI;;
1: len 7; hex 31303030343739; asc 1000479;;
2: len 4; hex 00000003; asc ;;
------------------
---TRANSACTION 22211185, ACTIVE 44 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 60, OS thread handle 123145525915648, query id 653 localhost 127.0.0.1 root
....
###########################################
Description: Hello, I tried to run a statment, which is "SELECT id, col1, col2 FROM t WHERE t.col1='xx' ORDER BY t.id FOR UPDATE" while index(col1,col2) is multiple-column indexes and id is the primary key. Expecting the rows I concerned would be locked, it turned out that some of the other rows which col1 is not equal to 'xx' were locked. But I found somes solutions to lock my concerned rows, and here are some examples: 1. remove the "ORDER BY" clause (What's more, the "ORDER BY" clause is necessary.) 2. use "ORDER BY col1" or "ORDER BY col2" instead of "ORDER BY t.id" 3. split index(col1,col2) into index(col1) and index(col2) Is this a bug or not? Thank you for your feedback. How to repeat: CREATE TABLE `order_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `pro_code` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_user_code` (`user_id`,`pro_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `order_test`(`id`, `user_id`, `pro_code`, `update_time`) VALUES (1, 'UEGEJ6AV8S', '1000071', '2019-11-19 17:41:01'), (2, 'UEGEJ6AV8S', '1000478', '2019-11-19 17:41:21'), (3, 'UEXK9T0PMI', '1000479', '2019-11-19 17:51:56'), (4, 'UEXK9T0PMI', '1162451', '2019-11-19 17:53:03'), (5, 'UFEKJ7HMEV', '1100032', '2019-09-11 12:18:29'), (6, 'UGQOCTQISR', '1100032', '2019-09-11 12:33:28'), (7, 'UGQOAFDGEI', '1000098', '2019-09-11 12:33:28'), (8, 'UHZBIF8WPP', '1100032', '2019-09-11 12:47:22'), (9, 'UJC03NLRCS', '1162431', '2019-09-11 13:02:31'); session 1: mysql> START TRANSACTION; mysql> SELECT * FROM order_test WHERE user_id='UEGEJ6AV8S' ORDER BY id DESC FOR UPDATE; +----+------------+----------+---------------------+ | id | user_id | pro_code | update_time | +----+------------+----------+---------------------+ | 2 | UEGEJ6AV8S | 1000478 | 2019-11-19 17:41:21 | | 1 | UEGEJ6AV8S | 1000071 | 2019-11-19 17:41:01 | +----+------------+----------+---------------------+ 2 rows in set (0.00 sec) mysql> session 2: mysql> START TRANSACTION; mysql> SELECT * FROM order_test WHERE user_id='UEXK9T0PMI' AND pro_code='1000479' FOR UPDATE; Lock wait timeout exceeded; try restarting transaction mysql> The information below is what I collected when I executed session 2: ########################################### mysql> select * from information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 22211186 trx_state: LOCK WAIT trx_started: 2020-01-20 17:31:16 trx_requested_lock_id: 22211186:50196:4:4 trx_wait_started: 2020-01-20 17:31:16 trx_weight: 2 trx_mysql_thread_id: 62 trx_query: SELECT * FROM order_test WHERE user_id='UEXK9T0PMI' AND pro_code='1000479' FOR UPDATE trx_operation_state: starting index read trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1136 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: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 *************************** 2. row *************************** trx_id: 22211185 trx_state: RUNNING trx_started: 2020-01-20 17:31:08 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 60 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 3 trx_lock_memory_bytes: 1136 trx_rows_locked: 5 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: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 2 rows in set (0.00 sec) ########################################### mysql> select * from information_schema.innodb_locks\G *************************** 1. row *************************** lock_id: 22211186:50196:4:4 lock_trx_id: 22211186 lock_mode: X lock_type: RECORD lock_table: `sys`.`order_test` lock_index: idx_user_code lock_space: 50196 lock_page: 4 lock_rec: 4 lock_data: 'UEXK9T0PMI', '1000479', 3 *************************** 2. row *************************** lock_id: 22211185:50196:4:4 lock_trx_id: 22211185 lock_mode: X lock_type: RECORD lock_table: `sys`.`order_test` lock_index: idx_user_code lock_space: 50196 lock_page: 4 lock_rec: 4 lock_data: 'UEXK9T0PMI', '1000479', 3 2 rows in set, 1 warning (0.00 sec) ########################################### mysql> SHOW ENGINE INNODB STATUS; ...... ---TRANSACTION 22211186, ACTIVE 36 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 62, OS thread handle 123145525358592, query id 654 localhost 127.0.0.1 root Sending data SELECT * FROM order_test WHERE user_id='UEXK9T0PMI' AND pro_code='1000479' FOR UPDATE ------- TRX HAS BEEN WAITING 36 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50196 page no 4 n bits 80 index idx_user_code of table `sys`.`order_test` trx id 22211186 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 10; hex 5545584b395430504d49; asc UEXK9T0PMI;; 1: len 7; hex 31303030343739; asc 1000479;; 2: len 4; hex 00000003; asc ;; ------------------ ---TRANSACTION 22211185, ACTIVE 44 sec 3 lock struct(s), heap size 1136, 5 row lock(s) MySQL thread id 60, OS thread handle 123145525915648, query id 653 localhost 127.0.0.1 root .... ###########################################