MySQL行级锁测验
发布时间：2022-04-02 21:16:43 所属栏目：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.
|
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%'; 查询会话session 1,session 2的连接ID session 1: mysql> status; -------------- mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386) Connection id: 15 session 2: mysql> status; ---TRANSACTION 0 3852348, ACTIVE 391 sec, OS thread id 12 7 lock struct(s), heap size 1024, undo log entries 4 MySQL thread id 15, query id 2071117 localhost test （编辑：应用网_丽江站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330468号