Bug #98296 Multiple-Column Indexes Bug when using SELECT ORDER BY xxx for UPDATE
Submitted: 20 Jan 2020 11:39 Modified: 21 Feb 2020 13:00
Reporter: derry Tes Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[20 Jan 2020 11:39] derry Tes
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
....
###########################################
[20 Jan 2020 14:10] MySQL Verification Team
Hi Mr. Tes,

Thank you for your bug report.

However, we can not verify it in the way that you have reported it.

First, we do not hunt bugs in old versions and releases. Hence, retry your test case with latest 5.7 and with 8.0.19.

Second, I do not see what is wrong with locks as you report them. You obviously did not read the reports carefully and you did not take into the account the supremum record.

Waiting on your feedback.
[21 Feb 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".