MySQL row-level lock test
The MySQL innodb storage engine uses the same row locking mechanism as Oracle, and you will see how to view row locks that exist in the system in the following lab. Here is the test process:
Session 1: update record
Mysql > set autocommit=off
Query OK, 0 rows affected (0.01 sec)
Mysql > update T1 set email='test@test.com' where id=0
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
Session 2: update the same record and wait
Mysql > set autocommit=off
Query OK, 0 rows affected (0.00 sec)
Mysql > update T1 set email='abc' where id=0
Session 3: view system wait events:
Mysql > show status like'% lock%'
+-+ +
| | Variable_name | Value |
+-+ +
| | Com_lock_tables | 0 | |
| | Com_unlock_tables | 0 | |
| | Innodb_row_lock_current_waits | 1 |-- here |
| | Innodb_row_lock_time | 0 | |
| | Innodb_row_lock_time_avg | 0 | |
| | Innodb_row_lock_time_max | 0 | |
| | Innodb_row_lock_waits | 1 | |
| | Key_blocks_not_flushed | 0 | |
| | Key_blocks_unused | 14497 | |
| | Key_blocks_used | 0 | |
| | Qcache_free_blocks | 1 | |
| | Qcache_total_blocks | 1 | |
| | Table_locks_immediate | 2070991 | |
| | Table_locks_waited | 2 | |
+-+ +
14 rows in set (0.01 sec)
Session 1: submit record
Mysql > commit
Query OK, 0 rows affected (0.01 sec)
Session 2:update will finish it immediately.
Mysql > update T1 set email='abc' where id=0
Query OK, 4 rows affected (2 min 43.44 sec)-such a long time to complete the update operation
Rows matched: 4 Changed: 4 Warnings: 0
Session 3: view the system wait event again
Mysql > show status like'% lock%'
+-+ +
| | Variable_name | Value |
+-+ +
| | Com_lock_tables | 0 | |
| | Com_unlock_tables | 0 | |
| | Innodb_row_lock_current_waits | 0 |-- 0 here |
| | Innodb_row_lock_time | 163436 | |
| | Innodb_row_lock_time_avg | 163436 | |
| | Innodb_row_lock_time_max | 163436 | |
| | Innodb_row_lock_waits | 1 | |
| | Key_blocks_not_flushed | 0 | |
| | Key_blocks_unused | 14497 | |
| | Key_blocks_used | 0 | |
| | Qcache_free_blocks | 1 | |
| | Qcache_total_blocks | 1 | |
| | Table_locks_immediate | 2070991 | |
| | Table_locks_waited | 2 | |
+-+ +
14 rows in set (0.01 sec)
Query the connection ID of session session 1 and session 2
Session 1:
Mysql > status
-
Mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)
Connection id: 15
Session 2:
Mysql > status
-
Mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)
Connection id: 13
When session 1 above has not been committed, you can execute the following command to view some transaction blocking information
Mysql > show innodb status\ G
-
TRANSACTIONS
-
Trx id counter 0 3852351
Purge done for trx's NRO < 0 3852350 undo NRO < 0
History list length 11
Total number of lock structs in row lock hash table 7
LIST OF TRANSACTIONS FOR EACH SESSION:
-TRANSACTION 0 0, not started, OS thread id 15
MySQL thread id 18, query id 2071119 localhost root
Show innodb status
-TRANSACTION 0 3852350, ACTIVE 6 sec, OS thread id 14 starting index read
Mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct (s), heap size 320
MySQL thread id 13, query id 2071118 localhost test Updating-you can see the waiters here
Update T1 set email='abc' where id=0-here you can see the SQL being executed by the waiters
-TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 32782 n bits 1056 index `idx_t1_ id` of table `dc_test/ t1` trx id 0 3852350 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000000; asc;; 1: len 6; hex 00000196fe5d; asc]
-
-TRANSACTION 0 3852348, ACTIVE 391sec, OS thread id 12
7 lock struct (s), heap size 1024, undo log entries 4
MySQL thread id 15, query id 2071117 localhost test
