mysql行级锁使用方法,MySQL行级锁测试

MySQL innodb存储引擎使用与Oracle相同的行锁机制，对如何查看系统中存在的行锁情况在下面的实验中，将可以看到。下面是测试过程: session 1:更新记录

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也更新相同的记录,出现等待

mysql> set autocommit=off;

Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set email='abc' where

id=0;

session 3:查看系统等待事件:

mysql> show status like '%lock%';

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

|

Variable_name | Value |

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

|

Com_lock_tables |

0 |

|

Com_unlock_tables |

0 |

| Innodb_row_lock_current_waits |

1 | --这里

|

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:提交记录

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

session 2:update立刻完成

mysql> update t1 set email='abc' where

id=0;

Query OK, 4 rows affected (2 min 43.44 sec)--这么长时间完成更新操作

Rows matched: 4 Changed: 4 Warnings: 0

session 3:再次查看系统等待事件

mysql> show status like '%lock%';

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

|

Variable_name | Value |

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

|

Com_lock_tables |

0 |

|

Com_unlock_tables |

0 |

| Innodb_row_lock_current_waits |

0 | --这里为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 |

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