-- Had to comment --source include/have_innodb.inc [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.2/mysql-test: cat ../docs/INFO_SRC commit: ce1656762c6483614b85263ee749af0cf155c8f6 date: 2017-03-20 16:43:43 +0100 build-date: 2017-03-20 17:00:32 +0100 short: ce16567 branch: mysql-trunk MySQL source 8.0.2 [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.2/mysql-test: ./mtr bug86812 Logging: ./mtr bug86812 MySQL Version 8.0.2 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.2/mysql-test/var'... Installing system database... Using parallel: 1 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 set transaction isolation level read committed; create table locker (pk int not null auto_increment, some_key varchar(10) not null, some_val varchar(25) default 'hi', primary key (pk), unique key uk_locker (some_key)); insert into locker values(1, 'key-one', 'some-value'); start transaction; select * from locker where some_key='key-one' for update; pk some_key some_val 1 key-one some-value set transaction isolation level read committed; set innodb_lock_wait_timeout=50; start transaction; select * from locker where some_key='key-one' for update; select * from information_schema.innodb_trx; trx_id 2062 trx_state LOCK WAIT trx_started 2017-06-23 15:04:16 trx_requested_lock_id 2062:66:6:2 trx_wait_started 2017-06-23 15:04:16 trx_weight 2 trx_mysql_thread_id 6 trx_query select * from locker where some_key='key-one' 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 trx_id 2061 trx_state RUNNING trx_started 2017-06-23 15:04:16 trx_requested_lock_id NULL trx_wait_started NULL trx_weight 3 trx_mysql_thread_id 5 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 2 trx_rows_modified 0 trx_concurrency_tickets 0 trx_isolation_level REPEATABLE READ 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 select * from performance_schema.data_locks; ENGINE INNODB ENGINE_LOCK_ID 2062:79 ENGINE_TRANSACTION_ID 2062 THREAD_ID 35 EVENT_ID 134 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME NULL OBJECT_INSTANCE_BEGIN 139720520671320 LOCK_TYPE TABLE LOCK_MODE IX LOCK_STATUS GRANTED LOCK_DATA NULL ENGINE INNODB ENGINE_LOCK_ID 2062:66:6:2 ENGINE_TRANSACTION_ID 2062 THREAD_ID 35 EVENT_ID 140 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME uk_locker OBJECT_INSTANCE_BEGIN 139720520668280 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS WAITING LOCK_DATA 'key-one' ENGINE INNODB ENGINE_LOCK_ID 2061:79 ENGINE_TRANSACTION_ID 2061 THREAD_ID 34 EVENT_ID 2154 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME NULL OBJECT_INSTANCE_BEGIN 139720520665368 LOCK_TYPE TABLE LOCK_MODE IX LOCK_STATUS GRANTED LOCK_DATA NULL ENGINE INNODB ENGINE_LOCK_ID 2061:66:6:2 ENGINE_TRANSACTION_ID 2061 THREAD_ID 34 EVENT_ID 2159 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME uk_locker OBJECT_INSTANCE_BEGIN 139720520662328 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS GRANTED LOCK_DATA 'key-one' ENGINE INNODB ENGINE_LOCK_ID 2061:66:5:2 ENGINE_TRANSACTION_ID 2061 THREAD_ID 34 EVENT_ID 2164 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME PRIMARY OBJECT_INSTANCE_BEGIN 139720520662672 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS GRANTED LOCK_DATA 1 select * from performance_schema.data_lock_waits; ENGINE INNODB REQUESTING_ENGINE_LOCK_ID 2062:66:6:2 REQUESTING_ENGINE_TRANSACTION_ID 2062 REQUESTING_THREAD_ID 35 REQUESTING_EVENT_ID 140 REQUESTING_OBJECT_INSTANCE_BEGIN 139720520668280 BLOCKING_ENGINE_LOCK_ID 2061:66:6:2 BLOCKING_ENGINE_TRANSACTION_ID 2061 BLOCKING_THREAD_ID 34 BLOCKING_EVENT_ID 2159 BLOCKING_OBJECT_INSTANCE_BEGIN 139720520662328 delete from locker where some_key='key-one'; select * from information_schema.innodb_trx; trx_id 2062 trx_state LOCK WAIT trx_started 2017-06-23 15:04:16 trx_requested_lock_id 2062:66:6:2 trx_wait_started 2017-06-23 15:04:16 trx_weight 2 trx_mysql_thread_id 6 trx_query select * from locker where some_key='key-one' 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 trx_id 2061 trx_state RUNNING trx_started 2017-06-23 15:04:16 trx_requested_lock_id NULL trx_wait_started NULL trx_weight 3 trx_mysql_thread_id 5 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 2 trx_rows_modified 0 trx_concurrency_tickets 0 trx_isolation_level REPEATABLE READ 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 select * from performance_schema.data_locks; ENGINE INNODB ENGINE_LOCK_ID 2062:79 ENGINE_TRANSACTION_ID 2062 THREAD_ID 35 EVENT_ID 134 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME NULL OBJECT_INSTANCE_BEGIN 139720520671320 LOCK_TYPE TABLE LOCK_MODE IX LOCK_STATUS GRANTED LOCK_DATA NULL ENGINE INNODB ENGINE_LOCK_ID 2062:66:6:2 ENGINE_TRANSACTION_ID 2062 THREAD_ID 35 EVENT_ID 140 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME uk_locker OBJECT_INSTANCE_BEGIN 139720520668280 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS WAITING LOCK_DATA 'key-one' ENGINE INNODB ENGINE_LOCK_ID 2061:79 ENGINE_TRANSACTION_ID 2061 THREAD_ID 34 EVENT_ID 2154 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME NULL OBJECT_INSTANCE_BEGIN 139720520665368 LOCK_TYPE TABLE LOCK_MODE IX LOCK_STATUS GRANTED LOCK_DATA NULL ENGINE INNODB ENGINE_LOCK_ID 2061:66:6:2 ENGINE_TRANSACTION_ID 2061 THREAD_ID 34 EVENT_ID 2159 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME uk_locker OBJECT_INSTANCE_BEGIN 139720520662328 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS GRANTED LOCK_DATA 'key-one' ENGINE INNODB ENGINE_LOCK_ID 2061:66:5:2 ENGINE_TRANSACTION_ID 2061 THREAD_ID 34 EVENT_ID 2164 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME PRIMARY OBJECT_INSTANCE_BEGIN 139720520662672 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS GRANTED LOCK_DATA 1 select * from performance_schema.data_lock_waits; ENGINE INNODB REQUESTING_ENGINE_LOCK_ID 2062:66:6:2 REQUESTING_ENGINE_TRANSACTION_ID 2062 REQUESTING_THREAD_ID 35 REQUESTING_EVENT_ID 140 REQUESTING_OBJECT_INSTANCE_BEGIN 139720520668280 BLOCKING_ENGINE_LOCK_ID 2061:66:6:2 BLOCKING_ENGINE_TRANSACTION_ID 2061 BLOCKING_THREAD_ID 34 BLOCKING_EVENT_ID 2159 BLOCKING_OBJECT_INSTANCE_BEGIN 139720520662328 insert into locker values(1, 'key-one', 'some-value'); ERROR 40001: Deadlock found when trying to get lock; try restarting transaction rollback; alter table locker drop key uk_locker; start transaction; select * from locker where some_key='key-one' for update; pk some_key some_val 1 key-one some-value set innodb_lock_wait_timeout=50; start transaction; select * from locker where some_key='key-one' for update; select * from information_schema.innodb_trx; trx_id 2062 trx_state LOCK WAIT trx_started 2017-06-23 15:04:16 trx_requested_lock_id 2062:66:6:2 trx_wait_started 2017-06-23 15:04:16 trx_weight 2 trx_mysql_thread_id 6 trx_query select * from locker where some_key='key-one' 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 trx_id 2061 trx_state RUNNING trx_started 2017-06-23 15:04:16 trx_requested_lock_id NULL trx_wait_started NULL trx_weight 3 trx_mysql_thread_id 5 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 2 trx_rows_modified 0 trx_concurrency_tickets 0 trx_isolation_level REPEATABLE READ 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 select * from performance_schema.data_locks; ENGINE INNODB ENGINE_LOCK_ID 2079:79 ENGINE_TRANSACTION_ID 2079 THREAD_ID 35 EVENT_ID 254 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME NULL OBJECT_INSTANCE_BEGIN 139720520671320 LOCK_TYPE TABLE LOCK_MODE IX LOCK_STATUS GRANTED LOCK_DATA NULL ENGINE INNODB ENGINE_LOCK_ID 2079:66:5:2 ENGINE_TRANSACTION_ID 2079 THREAD_ID 35 EVENT_ID 260 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME PRIMARY OBJECT_INSTANCE_BEGIN 139720520668280 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS WAITING LOCK_DATA 1 ENGINE INNODB ENGINE_LOCK_ID 2078:79 ENGINE_TRANSACTION_ID 2078 THREAD_ID 34 EVENT_ID 4216 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME NULL OBJECT_INSTANCE_BEGIN 139720520665368 LOCK_TYPE TABLE LOCK_MODE IX LOCK_STATUS GRANTED LOCK_DATA NULL ENGINE INNODB ENGINE_LOCK_ID 2078:66:5:1 ENGINE_TRANSACTION_ID 2078 THREAD_ID 34 EVENT_ID 4221 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME PRIMARY OBJECT_INSTANCE_BEGIN 139720520662328 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS GRANTED LOCK_DATA supremum pseudo-record ENGINE INNODB ENGINE_LOCK_ID 2078:66:5:2 ENGINE_TRANSACTION_ID 2078 THREAD_ID 34 EVENT_ID 4221 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME PRIMARY OBJECT_INSTANCE_BEGIN 139720520662328 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS GRANTED LOCK_DATA 1 select * from performance_schema.data_lock_waits; ENGINE INNODB REQUESTING_ENGINE_LOCK_ID 2079:66:5:2 REQUESTING_ENGINE_TRANSACTION_ID 2079 REQUESTING_THREAD_ID 35 REQUESTING_EVENT_ID 260 REQUESTING_OBJECT_INSTANCE_BEGIN 139720520668280 BLOCKING_ENGINE_LOCK_ID 2078:66:5:2 BLOCKING_ENGINE_TRANSACTION_ID 2078 BLOCKING_THREAD_ID 34 BLOCKING_EVENT_ID 4221 BLOCKING_OBJECT_INSTANCE_BEGIN 139720520662328 delete from locker where some_key='key-one'; select * from information_schema.innodb_trx; trx_id 2062 trx_state LOCK WAIT trx_started 2017-06-23 15:04:16 trx_requested_lock_id 2062:66:6:2 trx_wait_started 2017-06-23 15:04:16 trx_weight 2 trx_mysql_thread_id 6 trx_query select * from locker where some_key='key-one' 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 trx_id 2061 trx_state RUNNING trx_started 2017-06-23 15:04:16 trx_requested_lock_id NULL trx_wait_started NULL trx_weight 3 trx_mysql_thread_id 5 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 2 trx_rows_modified 0 trx_concurrency_tickets 0 trx_isolation_level REPEATABLE READ 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 select * from performance_schema.data_locks; ENGINE INNODB ENGINE_LOCK_ID 2079:79 ENGINE_TRANSACTION_ID 2079 THREAD_ID 35 EVENT_ID 254 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME NULL OBJECT_INSTANCE_BEGIN 139720520671320 LOCK_TYPE TABLE LOCK_MODE IX LOCK_STATUS GRANTED LOCK_DATA NULL ENGINE INNODB ENGINE_LOCK_ID 2079:66:5:2 ENGINE_TRANSACTION_ID 2079 THREAD_ID 35 EVENT_ID 260 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME PRIMARY OBJECT_INSTANCE_BEGIN 139720520668280 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS WAITING LOCK_DATA 1 ENGINE INNODB ENGINE_LOCK_ID 2078:79 ENGINE_TRANSACTION_ID 2078 THREAD_ID 34 EVENT_ID 4216 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME NULL OBJECT_INSTANCE_BEGIN 139720520665368 LOCK_TYPE TABLE LOCK_MODE IX LOCK_STATUS GRANTED LOCK_DATA NULL ENGINE INNODB ENGINE_LOCK_ID 2078:66:5:1 ENGINE_TRANSACTION_ID 2078 THREAD_ID 34 EVENT_ID 4221 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME PRIMARY OBJECT_INSTANCE_BEGIN 139720520662328 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS GRANTED LOCK_DATA supremum pseudo-record ENGINE INNODB ENGINE_LOCK_ID 2078:66:5:2 ENGINE_TRANSACTION_ID 2078 THREAD_ID 34 EVENT_ID 4221 OBJECT_SCHEMA test OBJECT_NAME locker PARTITION_NAME NULL SUBPARTITION_NAME NULL INDEX_NAME PRIMARY OBJECT_INSTANCE_BEGIN 139720520662328 LOCK_TYPE RECORD LOCK_MODE X LOCK_STATUS GRANTED LOCK_DATA 1 select * from performance_schema.data_lock_waits; ENGINE INNODB REQUESTING_ENGINE_LOCK_ID 2079:66:5:2 REQUESTING_ENGINE_TRANSACTION_ID 2079 REQUESTING_THREAD_ID 35 REQUESTING_EVENT_ID 260 REQUESTING_OBJECT_INSTANCE_BEGIN 139720520668280 BLOCKING_ENGINE_LOCK_ID 2078:66:5:2 BLOCKING_ENGINE_TRANSACTION_ID 2078 BLOCKING_THREAD_ID 34 BLOCKING_EVENT_ID 4221 BLOCKING_OBJECT_INSTANCE_BEGIN 139720520662328 insert into locker values(1, 'key-one', 'some-value'); ERROR HY000: Lock wait timeout exceeded; try restarting transaction rollback; rollback; drop table locker; main.bug86812 [ pass ] 51048 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 51.048 of 58 seconds executing testcases Completed: All 1 tests were successful. [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.2/mysql-test: ###### 5.7.18 rm -rf 86812 bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/86812 -v bin/mysqld --basedir=$PWD --datadir=$PWD/86812 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/86812/log.err 2>&1 & [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.18: bin/mysql -uroot -S /tmp/mysql.sock --prompt='Session1>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.18-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Session1>create database test; Query OK, 1 row affected (0.00 sec) Session1>use test Database changed Session1>create table locker (pk int not null auto_increment, some_key varchar(10) not null, some_val varchar(25) default 'hi', primary key (pk), unique key uk_locker (some_key)); Query OK, 0 rows affected (0.01 sec) Session1>insert into locker values(1, 'key-one', 'some-value'); Query OK, 1 row affected (0.01 sec) Session1>start transaction; Query OK, 0 rows affected (0.00 sec) Session1>select * from locker where some_key='key-one' for update; +----+----------+------------+ | pk | some_key | some_val | +----+----------+------------+ | 1 | key-one | some-value | +----+----------+------------+ 1 row in set (0.00 sec) Session1> [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.18: bin/mysql -uroot -S /tmp/mysql.sock --prompt='Session2>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.18-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Session2>use test Database changed Session2>start transaction; Query OK, 0 rows affected (0.00 sec) Session2>select * from locker where some_key='key-one' for update; ^^ hung Session1>delete from locker where some_key='key-one'; Query OK, 1 row affected (0.00 sec) Session1>insert into locker values(1, 'key-one', 'some-value'); Query OK, 1 row affected (0.00 sec) Session 2 will get deadlock. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Session2> -- rollback Session1>rollback; Query OK, 0 rows affected (0.00 sec) Session2>rollback; Query OK, 0 rows affected (0.00 sec) Session1>alter table locker drop key uk_locker; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- repeat previous steps Session1>show create table locker\G *************************** 1. row *************************** Table: locker Create Table: CREATE TABLE `locker` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `some_key` varchar(10) NOT NULL, `some_val` varchar(25) DEFAULT 'hi', PRIMARY KEY (`pk`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Session1>start transaction; Query OK, 0 rows affected (0.00 sec) Session1>select * from locker where some_key='key-one' for update; +----+----------+------------+ | pk | some_key | some_val | +----+----------+------------+ | 1 | key-one | some-value | +----+----------+------------+ 1 row in set (0.00 sec) Session1>delete from locker where some_key='key-one'; Query OK, 1 row affected (0.00 sec) Session1>insert into locker values(1, 'key-one', 'some-value'); Query OK, 1 row affected (0.00 sec) Session1> Session2>start transaction; Query OK, 0 rows affected (0.00 sec) Session2>select * from locker where some_key='key-one' for update; ^^ no deadlock seen but timeout as expected ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction