MySQL row-Level Lock Test

The MySQL innodb Storage engine uses the same row lock mechanism as Oracle. The following experiment shows how to view the row locks in the system. The following is the test process:
Session 1: update records

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 also updates the same record and waits

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: Submission record

Mysql> commit;

Query OK, 0 rows affected (0.01 sec)

Session 2: update completed immediately

Mysql> update t1 set email = 'abc' where id = 0;

Query OK, 4 rows affected (2 min 43.44 sec) -- complete the update operation so long

Rows matched: 4 Changed: 4 Warnings: 0

Session 3: view system wait events again

Mysql> show status like '% lock % ';

+ ------------------------------- + --------- +

| Variable_name | Value |

+ ------------------------------- + --------- +

| Com_lock_tables | 0 |

| Com_unlock_tables | 0 |

| Innodb_row_lock_current_waits | 0 | -- here 0

| 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 |

+ ------------------------------- + --------- +

1 2 Next Page