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 |

+——————————-+———+
14 rows in set (0.01 sec)

查询会话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;

————–

mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)

Connection id: 13

在上面的session 1尚没有提交的时候,可以执行下列命令，查看一些事务阻塞信息

mysql> show innodb statusG;

————

TRANSACTIONS

————

Trx id counter 0 3852351

Purge done for trx’s n:o

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 –这里可以看到等待者

update t1 set email=’abc’ where id=0 –这里可以看到等待者正在执行的SQL

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

相关推荐: 深入理解MySQL主从原理专栏发布

相信作为一名DBA来讲MySQL主从一直都是一个绕不开的话题，我们在很多高可用构架中都能看到它的身影。在我们心中一定都或多或少的产生过一些疑问，比如：这个系列就是想通过描述主从原理，抽丝剥茧解开大家对这些问题的疑惑。我认为如果要深入学习主从原理需要按照一定的…