首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
常见命令语句,InnoDB锁冲突案例演示澳门葡京手机版网址
发布时间:2019-12-29 02:18
浏览次数:
  1. 使用“Ctrl + R”组合键快速打开cmd窗口,并输入“cmd”命令,打开cmd窗口。

    澳门新葡亰35222 1

  2. 使用“mysql -uroot -proot”命令可以连接到本地的mysql服务。

    澳门新葡亰35222 2

  3. 使用“use mysql”命令,选择要使用的数据库,修改远程连接的基本信息,保存在mysql数据库中,因此使用mysql数据库。

    澳门新葡亰35222 3

  4. 使用“GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;”命令可以更改远程连接的设置。

    澳门新葡亰35222 4

  5. 使用“flush privileges;”命令刷新刚才修改的权限,使其生效。

    澳门新葡亰35222 5

  6. 使用“select host,user from user;”查看修改是否成功。

    澳门新葡亰35222 6

     

     

    解决远程连接时防火墙阻止访问:

    xp/2003添加防火墙例外端口
    打开防火墙,选择例外选项卡,添加端口
    名称:mysqlport
    端口号:3306
    选中TCP

#---------------------------
#----cmd命令行连接MySql---------

 

一、数据库基础

win7及以上添加防火墙例外端口:控制面板-防火墙设置-高级设置  
入站规则设置  
第一步 选择 入站规则 然后 新建规则,选择 端口,然后下一步   
第二步 选择TCP 选择特定端口
然后输入端口,如有多个端口需要用逗号隔开了 例如: 3306  
第三步 选择允许连接  
第四步 选择配置文件   
第五步 输入规则名称 mysqlport  


出站规则设置  
第一步 选择出站规则 然后 新建规则,选择 端口,然后下一步   
第二步 选择TCP 选择特定端口
然后输入端口,如有多个端口需要用逗号隔开了 例如: 3306  
第三步 选择允许连接  
第四步 选择配置文件   
第五步 输入规则名称
mysqlport(或者无特殊要求下直接关闭防火墙)

 

转载请注明出处:<http://www.cnblogs.com/fnlingnzb-learner/p/5848405.html>

 

Preface

  1、什么是数据库

    1、数据库(database)是保存有组织的数据的容器( 通常是一个文件或一组文件 )
    2、数据库是一个以某种有组织的方式存储的数据集合

    注意:数据库软件应该称为DBMS(数据库管理系统)。

       数据库是通过DBMS创建和操纵的容器

cd  C:Program FilesMySQLMySQL Server 5.5bin

 

  2、表

    表(table)是某种特定类型数据的结构化清单 

    表的名字是唯一的 (相同的数据库中不能使用相同的表名,但不同的数据库中可以)

 

    I've demontstrated several InnoDB locking cases in my previous blog.I'm gonna do the rest tests about InnoDB locks.

  3、模式

    模式(schema):关于数据库和表的布局及特性的信息 。

# 启动mysql服务器
net  start  mysql

 

  4、列和数据类型

    列(column) 表中的一个字段。所有表都是由一个或多个列组成的。

    数据类型(datetype) 所容许的数据的类型 。每个表列都有相应的数据类型,它限制(或容许)该列中存储的数据。(对优化磁盘起到重要作用)

* *   表由列组成。 列中存储着表中某部分的信息。

    注意:数据类型限制可存储在列中的数据种类,可以帮助正确的排序数据,并在磁盘优化方面起重要作用。

 

Procedure

  5、行

    行(row) 表中的一个记录

# 关闭mysql服务器
net  stop  mysql

 

  6、主键

    1、表中的每一行都应该有可以唯一标识自己的一列(或一组列)。

    2、主键(primary key) 一列(或一组列),其值能够唯一区分表中每个行。

    3、作为主键的条件:

      a.任意两行都不具有相同的主键值。

      b.每个行都必须具有一个主键值(主键列不允许NULL值)。

 

 

Test table information and the relevant variables.

二、关于SQL

# 进入mysql命令行 

 1 zlm@192.168.56.100:3306 [zlm]>show create table t1G
 2 *************************** 1. row ***************************
 3        Table: t1
 4 Create Table: CREATE TABLE `t1` (
 5   `c1` int(10) unsigned NOT NULL DEFAULT '0',
 6   `c2` int(10) unsigned NOT NULL DEFAULT '0',
 7   `c3` int(10) unsigned NOT NULL DEFAULT '0',
 8   `c4` int(10) unsigned NOT NULL DEFAULT '0',
 9   PRIMARY KEY (`c1`),
10   KEY `c2` (`c2`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
12 1 row in set (0.00 sec)
13 
14 zlm@192.168.56.100:3306 [zlm]>select * from t1;
15 +----+----+----+----+
16 | c1 | c2 | c3 | c4 |
17 +----+----+----+----+
18 |  0 |  0 |  0 |  0 |
19 |  1 |  1 |  1 |  0 |
20 |  3 |  3 |  3 |  0 |
21 |  4 |  2 |  2 |  0 |
22 |  6 |  2 |  5 |  0 |
23 |  8 |  6 |  6 |  0 |
24 | 10 |  4 |  4 |  0 |
25 +----+----+----+----+
26 8 rows in set (0.01 sec)
27 
28 zlm@192.168.56.100:3306 [zlm]>select @@transaction_isolation;
29 +-------------------------+
30 | @@transaction_isolation |
31 +-------------------------+
32 | REPEATABLE-READ         |
33 +-------------------------+
34 1 row in set (0.00 sec)
35 
36 zlm@192.168.56.100:3306 [(none)]>show variables like 'innodb_status_output_locks';
37 +----------------------------+-------+
38 | Variable_name              | Value |
39 +----------------------------+-------+
40 | innodb_status_output_locks | ON    |
41 +----------------------------+-------+
42 1 row in set (0.00 sec)

  1、什么是SQL

    SQL是结构化查询语言的缩写。一种用来专门用来与数据库通信的语言。

mysql  -h  localhost  -u  root  -p

 

  2、SQL的优点

    1、几乎所有重要的DBMS都支持SQL

    2、简单易学。语句全都是由描述性很强的英语单词组成。

    3、可以进行非常复杂和高级的数据库操作。

 #---------------------------

Test 1: session1 update while session2 insert.

#----MySql用户管理---------

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c2>=4;select * from t1 where c2>=4;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 2 rows affected (0.00 sec)
 6 Rows matched: 2  Changed: 2  Warnings: 0
 7 
 8 +----+----+----+----+
 9 | c1 | c2 | c3 | c4 |
10 +----+----+----+----+
11 | 10 |  4 |  4 | 20 |
12 |  8 |  6 |  6 | 20 |
13 +----+----+----+----+
14 2 rows in set (0.00 sec)
15 
16 //Lock information of session1.
17 TABLE LOCK table `zlm`.`t1` trx id 2997544 lock mode IX
18 RECORD LOCKS space id 178 page no 4 n bits 80 index c2 of table `zlm`.`t1` trx id 2997544 lock_mode X
19 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
20  0: len 8; hex 73757072656d756d; asc supremum;;
21 
22 Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
23  0: len 4; hex 00000006; asc     ;;
24  1: len 4; hex 00000008; asc     ;;
25 
26 Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
27  0: len 4; hex 00000004; asc     ;;
28  1: len 4; hex 0000000a; asc     ;;
29 
30 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997544 lock_mode X locks rec but not gap
31 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
32  0: len 4; hex 00000008; asc     ;;
33  1: len 6; hex 0000002dbd28; asc    - (;;
34  2: len 7; hex 3a0000012727bb; asc :   '' ;;
35  3: len 4; hex 00000006; asc     ;;
36  4: len 4; hex 00000006; asc     ;;
37  5: len 4; hex 00000014; asc     ;;
38 
39 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
40  0: len 4; hex 0000000a; asc     ;;
41  1: len 6; hex 0000002dbd28; asc    - (;;
42  2: len 7; hex 3a000001272799; asc :   '' ;;
43  3: len 4; hex 00000004; asc     ;;
44  4: len 4; hex 00000004; asc     ;;
45  5: len 4; hex 00000014; asc     ;;
46 
47 //Session2:
48 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,5,10,10;
49 Query OK, 0 rows affected (0.00 sec)
50 
51 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
52 
53 //Session2 was block because of the gap lock(c2>=4 hold a supremum lock) which was holed by session 1.The value c2=5 which session2 want to insert is conficted with the range lock.

#修改密码:首先在DOS 下进入mysql安装路径的bin目录下,然后键入以下命令:

 

mysqladmin -uroot -p123 password 456;

Test 2: session1 update while session2 insert.

*#增加用户*

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c2>=4;select * from t1 where c2>=4;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 2 rows affected (0.00 sec)
 6 Rows matched: 2  Changed: 2  Warnings: 0
 7 
 8 +----+----+----+----+
 9 | c1 | c2 | c3 | c4 |
10 +----+----+----+----+
11 | 10 |  4 |  4 | 20 |
12 |  8 |  6 |  6 | 20 |
13 +----+----+----+----+
14 2 rows in set (0.00 sec)
15 
16 //Session2:
17 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,2,10,10;
18 Query OK, 0 rows affected (0.00 sec)
19 
20 Query OK, 1 row affected (0.00 sec)
21 Records: 1  Duplicates: 0  Warnings: 0
22 
23 //This time the transaction in session2 was committed immediately.The value c2=2 didn't conflict with the range lock in session1.

**#格式:grant 权限 on 数据库.*澳门葡京网上赌场, to 用户名@登录主机 identified by '密码'**

 

*澳门新葡亰官网网址,**/****

Test 3: session1  update while session2 insert.

**如,增加一个用户user1密码为password1,让其可以在本机上登录, 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql,然后键入以下命令:**

  1 //Session1:
  2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c1>=6;select * from t1 where c1>=6;
  3 Query OK, 0 rows affected (0.00 sec)
  4 
  5 Query OK, 3 rows affected (0.00 sec)
  6 Rows matched: 3  Changed: 3  Warnings: 0
  7 
  8 +----+----+----+----+
  9 | c1 | c2 | c3 | c4 |
 10 +----+----+----+----+
 11 |  6 |  2 |  5 | 20 |
 12 |  8 |  6 |  6 | 20 |
 13 | 10 |  4 |  4 | 20 |
 14 +----+----+----+----+
 15 3 rows in set (0.00 sec)
 16 
 17 //Lock information of session1.
 18 ---TRANSACTION 2997551, ACTIVE 3 sec
 19 3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3
 20 MySQL thread id 103, OS thread handle 140181164484352, query id 4187 zlm1 192.168.56.100 zlm
 21 Trx read view will not see trx with id >= 2997552, sees < 2997552
 22 TABLE LOCK table `zlm`.`t1` trx id 2997551 lock mode IX
 23 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997551 lock_mode X locks rec but not gap
 24 Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 25  0: len 4; hex 00000006; asc     ;;
 26  1: len 6; hex 0000002dbd2f; asc    - /;;
 27  2: len 7; hex 3e0000012d180e; asc >   -  ;;
 28  3: len 4; hex 00000002; asc     ;;
 29  4: len 4; hex 00000005; asc     ;;
 30  5: len 4; hex 00000014; asc     ;;
 31 
 32 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997551 lock_mode X
 33 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 34  0: len 8; hex 73757072656d756d; asc supremum;;
 35 
 36 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 37  0: len 4; hex 00000008; asc     ;;
 38  1: len 6; hex 0000002dbd2f; asc    - /;;
 39  2: len 7; hex 3e0000012d1830; asc >   - 0;;
 40  3: len 4; hex 00000006; asc     ;;
 41  4: len 4; hex 00000006; asc     ;;
 42  5: len 4; hex 00000014; asc     ;;
 43 
 44 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 45  0: len 4; hex 0000000a; asc     ;;
 46  1: len 6; hex 0000002dbd2f; asc    - /;;
 47  2: len 7; hex 3e0000012d1852; asc >   - R;;
 48  3: len 4; hex 00000004; asc     ;;
 49  4: len 4; hex 00000004; asc     ;;
 50  5: len 4; hex 00000014; asc     ;;
 51 
 52 //Session2:
 53 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 9,9,9,9;
 54 Query OK, 0 rows affected (0.00 sec)
 55 
 56 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 57 
 58 //Locks information of session2.
 59 ---TRANSACTION 2997604, ACTIVE 5 sec inserting
 60 mysql tables in use 1, locked 1
 61 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
 62 MySQL thread id 255, OS thread handle 140181022336768, query id 10108 zlm1 192.168.56.100 zlm executing
 63 insert into t1 select 9,9,9,9
 64 ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
 65 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997604 lock_mode X locks gap before rec insert intention waiting
 66 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 67  0: len 4; hex 0000000a; asc     ;;
 68  1: len 6; hex 0000002dbd63; asc    - c;;
 69  2: len 7; hex 400000012f17f3; asc @   /  ;;
 70  3: len 4; hex 00000004; asc     ;;
 71  4: len 4; hex 00000004; asc     ;;
 72  5: len 4; hex 00000014; asc     ;;
 73 
 74 ------------------
 75 TABLE LOCK table `zlm`.`t1` trx id 2997604 lock mode IX
 76 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997604 lock_mode X locks gap before rec insert intention waiting
 77 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 78  0: len 4; hex 0000000a; asc     ;;
 79  1: len 6; hex 0000002dbd63; asc    - c;;
 80  2: len 7; hex 400000012f17f3; asc @   /  ;;
 81  3: len 4; hex 00000004; asc     ;;
 82  4: len 4; hex 00000004; asc     ;;
 83  5: len 4; hex 00000014; asc     ;;
 84 
 85 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 11,9,9,9;
 86 Query OK, 0 rows affected (0.00 sec)
 87 
 88 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 89 
 90 //Locks information of session2.
 91 Trx id counter 2997606
 92 Purge done for trx's n:o < 2997603 undo n:o < 0 state: running but idle
 93 History list length 35
 94 LIST OF TRANSACTIONS FOR EACH SESSION:
 95 ---TRANSACTION 421656271875696, not started
 96 0 lock struct(s), heap size 1136, 0 row lock(s)
 97 ---TRANSACTION 2997605, ACTIVE 3 sec inserting
 98 mysql tables in use 1, locked 1
 99 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
100 MySQL thread id 255, OS thread handle 140181022336768, query id 10237 zlm1 192.168.56.100 zlm executing
101 insert into t1 select 11,9,9,9
102 ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
103 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997605 lock_mode X insert intention waiting
104 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
105  0: len 8; hex 73757072656d756d; asc supremum;;
106 
107 ------------------
108 TABLE LOCK table `zlm`.`t1` trx id 2997605 lock mode IX
109 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997605 lock_mode X insert intention waiting
110 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
111  0: len 8; hex 73757072656d756d; asc supremum;;
112  
113 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 10,9,9,9;
114 Query OK, 0 rows affected (0.00 sec)
115 
116 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
117 
118 //locks information of session2.
119 ---TRANSACTION 2997609, ACTIVE 5 sec inserting
120 mysql tables in use 1, locked 1
121 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
122 MySQL thread id 271, OS thread handle 140181022738176, query id 10784 zlm1 192.168.56.100 zlm executing
123 insert into t1 select 10,9,9,9
124 ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
125 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997609 lock mode S waiting
126 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
127  0: len 4; hex 0000000a; asc     ;;
128  1: len 6; hex 0000002dbd68; asc    - h;;
129  2: len 7; hex 4400000da22824; asc D    ($;;
130  3: len 4; hex 00000004; asc     ;;
131  4: len 4; hex 00000004; asc     ;;
132  5: len 4; hex 00000014; asc     ;;
133 
134 ------------------
135 TABLE LOCK table `zlm`.`t1` trx id 2997609 lock mode IX
136 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997609 lock mode S waiting
137 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
138  0: len 4; hex 0000000a; asc     ;;
139  1: len 6; hex 0000002dbd68; asc    - h;;
140  2: len 7; hex 4400000da22824; asc D    ($;;
141  3: len 4; hex 00000004; asc     ;;
142  4: len 4; hex 00000004; asc     ;;
143  5: len 4; hex 00000014; asc     ;;
144 
145 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 8,9,9,9;
146 Query OK, 0 rows affected (0.00 sec)
147 
148 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
149 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,9,9,9;
150 Query OK, 0 rows affected (0.00 sec)
151 
152 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
153 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 6,9,9,9;
154 Query OK, 0 rows affected (0.00 sec)
155 
156 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
157 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 5,9,9,9;
158 Query OK, 0 rows affected (0.00 sec)
159 
160 Query OK, 1 row affected (0.00 sec)
161 Records: 1  Duplicates: 0  Warnings: 0
162 
163 //Only c1=5 in session2 can be executed immediately.Because c1=5 didn't conflict with the range lock.

*grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";*

 

*澳门新葡亰35222,*如果希望该用户能够在任何机器上登陆mysql,则将localhost改为"%"。**

Test 4: session1 select for update while session2 delete.

***如果你不想user1有密码,可以再打一个命令将密码去掉。*澳门葡京手机版网址,**

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1<=4 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 +----+----+----+----+
 6 | c1 | c2 | c3 | c4 |
 7 +----+----+----+----+
 8 |  0 |  0 |  0 |  0 |
 9 |  1 |  1 |  1 |  0 |
10 |  3 |  3 |  3 |  0 |
11 |  4 |  2 |  2 |  0 |
12 +----+----+----+----+
13 4 rows in set (0.00 sec)
14 
15 //Locks information of session1.
16 ---TRANSACTION 2997623, ACTIVE 4 sec
17 2 lock struct(s), heap size 1136, 5 row lock(s)
18 MySQL thread id 295, OS thread handle 140181022537472, query id 11851 zlm1 192.168.56.100 zlm
19 TABLE LOCK table `zlm`.`t1` trx id 2997623 lock mode IX
20 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997623 lock_mode X
21 Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
22  0: len 4; hex 00000000; asc     ;;
23  1: len 6; hex 0000002dbb07; asc    -  ;;
24  2: len 7; hex a7000002690110; asc     i  ;;
25  3: len 4; hex 00000000; asc     ;;
26  4: len 4; hex 00000000; asc     ;;
27  5: len 4; hex 00000000; asc     ;;
28 
29 Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
30  0: len 4; hex 00000001; asc     ;;
31  1: len 6; hex 0000002dbb07; asc    -  ;;
32  2: len 7; hex a700000269011d; asc     i  ;;
33  3: len 4; hex 00000001; asc     ;;
34  4: len 4; hex 00000001; asc     ;;
35  5: len 4; hex 00000000; asc     ;;
36 
37 Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
38  0: len 4; hex 00000003; asc     ;;
39  1: len 6; hex 0000002dbb07; asc    -  ;;
40  2: len 7; hex a700000269012a; asc     i *;;
41  3: len 4; hex 00000003; asc     ;;
42  4: len 4; hex 00000003; asc     ;;
43  5: len 4; hex 00000000; asc     ;;
44 
45 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
46  0: len 4; hex 00000004; asc     ;;
47  1: len 6; hex 0000002dbb07; asc    -  ;;
48  2: len 7; hex a7000002690137; asc     i 7;;
49  3: len 4; hex 00000002; asc     ;;
50  4: len 4; hex 00000002; asc     ;;
51  5: len 4; hex 00000000; asc     ;;
52 
53 Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
54  0: len 4; hex 00000006; asc     ;;
55  1: len 6; hex 0000002dbd6c; asc    - l;;
56  2: len 7; hex 470000025802f4; asc G   X  ;;
57  3: len 4; hex 00000002; asc     ;;
58  4: len 4; hex 00000005; asc     ;;
59  5: len 4; hex 00000014; asc     ;;
60 
61 //Session2:
62 zlm@192.168.56.100:3306 [zlm]>begin;delete from t1 where c1=6;
63 Query OK, 0 rows affected (0.00 sec)
64 
65 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
66 
67 //The lock session2 requested for where c1=6 was locked either.So it was blocked.Even though primary key does not contains the gap lock,but when the condition contains a range like c1<=4,it will block the next-key of c1=6 here.

**grant select,insert,update,delete on mydb.* to user1@localhost identified by "";**

 

**/*

Test 5: session1 select for update while session2 delete.

**grant all privileges on wpj1105.* to sunxiao@localhost identified by '123'; #all privileges 所有权限**

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1>=4 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 +----+----+----+----+
 6 | c1 | c2 | c3 | c4 |
 7 +----+----+----+----+
 8 |  4 |  2 |  2 |  0 |
 9 |  6 |  2 |  5 | 20 |
10 |  8 |  6 |  6 | 20 |
11 | 10 |  4 |  4 | 20 |
12 +----+----+----+----+
13 4 rows in set (0.00 sec)
14 
15 //Lock information of session1.
16 ---TRANSACTION 2997627, ACTIVE 4 sec
17 3 lock struct(s), heap size 1136, 5 row lock(s)
18 MySQL thread id 338, OS thread handle 140181164685056, query id 14154 zlm1 192.168.56.100 zlm
19 TABLE LOCK table `zlm`.`t1` trx id 2997627 lock mode IX
20 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997627 lock_mode X locks rec but not gap
21 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
22  0: len 4; hex 00000004; asc     ;;
23  1: len 6; hex 0000002dbb07; asc    -  ;;
24  2: len 7; hex a7000002690137; asc     i 7;;
25  3: len 4; hex 00000002; asc     ;;
26  4: len 4; hex 00000002; asc     ;;
27  5: len 4; hex 00000000; asc     ;;
28 
29 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997627 lock_mode X
30 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
31  0: len 8; hex 73757072656d756d; asc supremum;;
32 
33 Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
34  0: len 4; hex 00000006; asc     ;;
35  1: len 6; hex 0000002dbd6c; asc    - l;;
36  2: len 7; hex 470000025802f4; asc G   X  ;;
37  3: len 4; hex 00000002; asc     ;;
38  4: len 4; hex 00000005; asc     ;;
39  5: len 4; hex 00000014; asc     ;;
40 
41 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
42  0: len 4; hex 00000008; asc     ;;
43  1: len 6; hex 0000002dbd6c; asc    - l;;
44  2: len 7; hex 47000002580316; asc G   X  ;;
45  3: len 4; hex 00000006; asc     ;;
46  4: len 4; hex 00000006; asc     ;;
47  5: len 4; hex 00000014; asc     ;;
48 
49 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
50  0: len 4; hex 0000000a; asc     ;;
51  1: len 6; hex 0000002dbd6c; asc    - l;;
52  2: len 7; hex 47000002580338; asc G   X 8;;
53  3: len 4; hex 00000004; asc     ;;
54  4: len 4; hex 00000004; asc     ;;
55  5: len 4; hex 00000014; asc     ;;
56  
57  //Session2:
58  zlm@192.168.56.100:3306 [zlm]>delete from t1 where c1=3;
59 Query OK, 1 row affected (0.00 sec)
60 
61 //Session2 was not blocked this time.Because c1=3 was not in the gap lock here.

 

 

#----------------------------

Test 5: session1 update while session2 select for update.

#-----MySql数据库操作基础-----

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c1=40 where c1=4;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 1 row affected (0.00 sec)
 6 Rows matched: 1  Changed: 1  Warnings: 0
 7 
 8 //lock information of session1.
 9 ---TRANSACTION 2997652, ACTIVE 49 sec
10 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
11 MySQL thread id 366, OS thread handle 140181022738176, query id 15652 zlm1 192.168.56.100 zlm
12 TABLE LOCK table `zlm`.`t1` trx id 2997652 lock mode IX
13 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997652 lock_mode X locks rec but not gap
14 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
15  0: len 4; hex 00000004; asc     ;;
16  1: len 6; hex 0000002dbd94; asc    -  ;;
17  2: len 7; hex 6000000dab26f6; asc `    & ;;
18  3: len 4; hex 00000002; asc     ;;
19  4: len 4; hex 00000002; asc     ;;
20  5: len 4; hex 00000000; asc     ;;
21  
22 //Session1 holded only 1 record lock with no gap of record c1=4.
23 
24 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c2=2 for update;
25 Query OK, 0 rows affected (0.00 sec)
26 
27 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
28 
29 //Lock information of session2.
30 ---TRANSACTION 2997657, ACTIVE 39 sec fetching rows
31 mysql tables in use 1, locked 1
32 LOCK WAIT 3 lock struct(s), heap size 1136, 4 row lock(s)
33 MySQL thread id 365, OS thread handle 140181022537472, query id 15743 zlm1 192.168.56.100 zlm Sending data
34 select * from t1 where c2=2 for update
35 ------- TRX HAS BEEN WAITING 39 SEC FOR THIS LOCK TO BE GRANTED:
36 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997657 lock_mode X waiting
37 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
38  0: len 4; hex 00000004; asc     ;;
39  1: len 6; hex 0000002dbd94; asc    -  ;;
40  2: len 7; hex 6000000dab26f6; asc `    & ;;
41  3: len 4; hex 00000002; asc     ;;
42  4: len 4; hex 00000002; asc     ;;
43  5: len 4; hex 00000000; asc     ;;
44 
45 ------------------
46 TABLE LOCK table `zlm`.`t1` trx id 2997657 lock mode IX
47 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997657 lock_mode X
48 Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
49  0: len 4; hex 00000000; asc     ;;
50  1: len 6; hex 0000002dbb07; asc    -  ;;
51  2: len 7; hex a7000002690110; asc     i  ;;
52  3: len 4; hex 00000000; asc     ;;
53  4: len 4; hex 00000000; asc     ;;
54  5: len 4; hex 00000000; asc     ;;
55 
56 Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
57  0: len 4; hex 00000001; asc     ;;
58  1: len 6; hex 0000002dbb07; asc    -  ;;
59  2: len 7; hex a700000269011d; asc     i  ;;
60  3: len 4; hex 00000001; asc     ;;
61  4: len 4; hex 00000001; asc     ;;
62  5: len 4; hex 00000000; asc     ;;
63 
64 Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
65  0: len 4; hex 00000003; asc     ;;
66  1: len 6; hex 0000002dbd8b; asc    -  ;;
67  2: len 7; hex db0000019f0110; asc        ;;
68  3: len 4; hex 00000003; asc     ;;
69  4: len 4; hex 00000003; asc     ;;
70  5: len 4; hex 00000000; asc     ;;
71 
72 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997657 lock_mode X waiting
73 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
74  0: len 4; hex 00000004; asc     ;;
75  1: len 6; hex 0000002dbd94; asc    -  ;;
76  2: len 7; hex 6000000dab26f6; asc `    & ;;
77  3: len 4; hex 00000002; asc     ;;
78  4: len 4; hex 00000002; asc     ;;
79  5: len 4; hex 00000000; asc     ;;
80  
81  //Session2 holded 4 record locks on c1 but only c1=4 was conflicted with session1.It was still blocked.
82  //I'm a little baffled why the session2 holded so many record locks on the primary key column(c1=0,c1=1,c1=3).

 

 

# 创建数据库

Test 6: session1 delete then insert while session2 insert.

create  database   namage  default  character  set  utf8  collate  utf8_general_ci;

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;delete from t1 where c1=8;insert into t1 select 9,9,9,9;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 1 row affected (0.00 sec)
 6 
 7 Query OK, 1 row affected (0.00 sec)
 8 Records: 1  Duplicates: 0  Warnings: 0
 9 
10 //Lock information of session1.
11 ---TRANSACTION 2997659, ACTIVE 12 sec
12 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
13 MySQL thread id 414, OS thread handle 140181164484352, query id 17399 zlm1 192.168.56.100 zlm
14 TABLE LOCK table `zlm`.`t1` trx id 2997659 lock mode IX
15 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997659 lock_mode X locks rec but not gap
16 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
17  0: len 4; hex 00000008; asc     ;;
18  1: len 6; hex 0000002dbd9b; asc    -  ;;
19  2: len 7; hex 64000001b9298c; asc d    ) ;;
20  3: len 4; hex 00000006; asc     ;;
21  4: len 4; hex 00000006; asc     ;;
22  5: len 4; hex 00000014; asc     ;;
23 
24 //Session2:
25 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,7,7,7;
26 Query OK, 0 rows affected (0.00 sec)
27 
28 Query OK, 1 row affected (0.00 sec)
29 Records: 1  Duplicates: 0  Warnings: 0
30 
31 //Session2 did not conflict with the "X" record locks holded by session1.So it was executed immediately.Because there was no gap lock on c1 column(primary key).

# 如果数据库存在删除

 

drop  database  if  exists  manage;

Test 7: session1 insert while session2 insert.

 

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 9,9,9,9;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 1 row affected (0.00 sec)
 6 Records: 1  Duplicates: 0  Warnings: 0
 7 
 8 //Lock information of session1.
 9 ---TRANSACTION 2997666, ACTIVE 3 sec
10 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
11 MySQL thread id 440, OS thread handle 140181022738176, query id 18609 zlm1 192.168.56.100 zlm
12 TABLE LOCK table `zlm`.`t1` trx id 2997666 lock mode IX
13 
14 //Notice that the c1=9 is not exist in primary key.It only generate a "IX" lock(intention lock).
15 
16 //Session2:
17 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 9,9,9,9;
18 Query OK, 0 rows affected (0.01 sec)
19 
20 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
21 
22 //Lock information of session2.
23 ---TRANSACTION 2997671, ACTIVE 9 sec inserting
24 mysql tables in use 1, locked 1
25 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
26 MySQL thread id 443, OS thread handle 140181022537472, query id 18663 zlm1 192.168.56.100 zlm executing
27 insert into t1 select 9,9,9,9
28 ------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
29 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997671 lock mode S waiting
30 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
31  0: len 4; hex 00000009; asc     ;;
32  1: len 6; hex 0000002dbda2; asc    -  ;;
33  2: len 7; hex e8000001b00110; asc        ;;
34  3: len 4; hex 00000009; asc     ;;
35  4: len 4; hex 00000009; asc     ;;
36  5: len 4; hex 00000009; asc     ;;
37 
38 ------------------
39 TABLE LOCK table `zlm`.`t1` trx id 2997671 lock mode IX
40 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997671 lock mode S waiting
41 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
42  0: len 4; hex 00000009; asc     ;;
43  1: len 6; hex 0000002dbda2; asc    -  ;;
44  2: len 7; hex e8000001b00110; asc        ;;
45  3: len 4; hex 00000009; asc     ;;
46  4: len 4; hex 00000009; asc     ;;
47  5: len 4; hex 00000009; asc     ;;
48 
49 ---TRANSACTION 2997666, ACTIVE 57 sec
50 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
51 MySQL thread id 440, OS thread handle 140181022738176, query id 18609 zlm1 192.168.56.100 zlm
52 TABLE LOCK table `zlm`.`t1` trx id 2997666 lock mode IX
53 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997666 lock_mode X locks rec but not gap
54 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
55  0: len 4; hex 00000009; asc     ;;
56  1: len 6; hex 0000002dbda2; asc    -  ;;
57  2: len 7; hex e8000001b00110; asc        ;;
58  3: len 4; hex 00000009; asc     ;;
59  4: len 4; hex 00000009; asc     ;;
60  5: len 4; hex 00000009; asc     ;;
61  
62  //Session2 was waiting for the "S" record lock and it also request for "X" record lock on record c1=9 in primary key.Although the record c1=9 is not exist in primary key,but they were inserting into the same row.As the session2 cannot get the lock for inserting.It was blocked.

# 进入数据库

 

use  manage;

Test 8: session1 select for update while session2 insert.**(modify the same row)**

# 删除数据库

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=7 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Empty set (0.00 sec)
 6 
 7 //Lock information of session1.
 8 ---TRANSACTION 2997672, ACTIVE 7 sec
 9 2 lock struct(s), heap size 1136, 1 row lock(s)
10 MySQL thread id 464, OS thread handle 140181164484352, query id 19707 zlm1 192.168.56.100 zlm
11 TABLE LOCK table `zlm`.`t1` trx id 2997672 lock mode IX
12 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997672 lock_mode X locks gap before rec
13 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
14  0: len 4; hex 00000008; asc     ;;
15  1: len 6; hex 0000002dbd6c; asc    - l;;
16  2: len 7; hex 47000002580316; asc G   X  ;;
17  3: len 4; hex 00000006; asc     ;;
18  4: len 4; hex 00000006; asc     ;;
19  5: len 4; hex 00000014; asc     ;;
20 
21 //Session1 holded a gap lock(or we can call it next-key lock either) of c1=8.Even though the record of c1=7 was not exist.
22 
23 //Session2:
24 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,7,7,7;
25 Query OK, 0 rows affected (0.00 sec)
26 
27 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
28 
29 //Lock information of session2.
30 ---TRANSACTION 2997673, ACTIVE 3 sec inserting
31 mysql tables in use 1, locked 1
32 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
33 MySQL thread id 465, OS thread handle 140181022537472, query id 19772 zlm1 192.168.56.100 zlm executing
34 insert into t1 select 7,7,7,7
35 ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
36 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997673 lock_mode X locks gap before rec insert intention waiting
37 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
38  0: len 4; hex 00000008; asc     ;;
39  1: len 6; hex 0000002dbd6c; asc    - l;;
40  2: len 7; hex 47000002580316; asc G   X  ;;
41  3: len 4; hex 00000006; asc     ;;
42  4: len 4; hex 00000006; asc     ;;
43  5: len 4; hex 00000014; asc     ;;
44 
45 ------------------
46 TABLE LOCK table `zlm`.`t1` trx id 2997673 lock mode IX
47 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997673 lock_mode X locks gap before rec insert intention waiting
48 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
49  0: len 4; hex 00000008; asc     ;;
50  1: len 6; hex 0000002dbd6c; asc    - l;;
51  2: len 7; hex 47000002580316; asc G   X  ;;
52  3: len 4; hex 00000006; asc     ;;
53  4: len 4; hex 00000006; asc     ;;
54  5: len 4; hex 00000014; asc     ;;
55  
56 //Session2 generated a insert intention lock and request for the next-key lock holded by session1.So it blocked.

drop  manage;

 

# 查看表的结构

**Test 9: session1 insert while session2 select for update.*(modify the same row)***

desc  class;

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,7,7,7;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 1 row affected (0.00 sec)
 6 Records: 1  Duplicates: 0  Warnings: 0
 7 
 8 //Lock information of session1.
 9 ---TRANSACTION 2997674, ACTIVE 4 sec
10 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
11 MySQL thread id 493, OS thread handle 140181022738176, query id 20721 zlm1 192.168.56.100 zlm
12 TABLE LOCK table `zlm`.`t1` trx id 2997674 lock mode IX
13 
14 //Session1 generated only a "IX" lock.
15 
16 //Session2:
17 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=7 for update;
18 Query OK, 0 rows affected (0.00 sec)
19 
20 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
21 
22 //Lock information of session2.
23 ---TRANSACTION 2997675, ACTIVE 2 sec starting index read
24 mysql tables in use 1, locked 1
25 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
26 MySQL thread id 494, OS thread handle 140181164685056, query id 20752 zlm1 192.168.56.100 zlm statistics
27 select * from t1 where c1=7 for update
28 ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
29 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997675 lock_mode X locks rec but not gap waiting
30 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
31  0: len 4; hex 00000007; asc     ;;
32  1: len 6; hex 0000002dbdaa; asc    -  ;;
33  2: len 7; hex ee000001bd0110; asc        ;;
34  3: len 4; hex 00000007; asc     ;;
35  4: len 4; hex 00000007; asc     ;;
36  5: len 4; hex 00000007; asc     ;;
37 
38 ------------------
39 TABLE LOCK table `zlm`.`t1` trx id 2997675 lock mode IX
40 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997675 lock_mode X locks rec but not gap waiting
41 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
42  0: len 4; hex 00000007; asc     ;;
43  1: len 6; hex 0000002dbdaa; asc    -  ;;
44  2: len 7; hex ee000001bd0110; asc        ;;
45  3: len 4; hex 00000007; asc     ;;
46  4: len 4; hex 00000007; asc     ;;
47  5: len 4; hex 00000007; asc     ;;
48 
49 //Lock information of session1.
50 ---TRANSACTION 2997674, ACTIVE 31 sec
51 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
52 MySQL thread id 493, OS thread handle 140181022738176, query id 20721 zlm1 192.168.56.100 zlm
53 TABLE LOCK table `zlm`.`t1` trx id 2997674 lock mode IX
54 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997674 lock_mode X locks rec but not gap
55 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
56  0: len 4; hex 00000007; asc     ;;
57  1: len 6; hex 0000002dbdaa; asc    -  ;;
58  2: len 7; hex ee000001bd0110; asc        ;;
59  3: len 4; hex 00000007; asc     ;;
60  4: len 4; hex 00000007; asc     ;;
61  5: len 4; hex 00000007; asc     ;;
62  
63  //when session2 intended to update the same row,session1 generated a "X" record lock.Then session2 had to wait for the release of the lock by session1.It was blocked.That means InnoDB adds locks only if it detects multiple concurrent transactions are modifying the same rows no mater whether the record is exist or not.

# 查看表内数据

 

select  *常见命令语句,InnoDB锁冲突案例演示澳门葡京手机版网址。   from  class;

Test 10: session1 select for update while session2 select for update.(modify the same row)

 

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=7 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Empty set (0.00 sec)
 6 
 7 //Lock information of session1.
 8 ---TRANSACTION 2997677, ACTIVE 3 sec
 9 2 lock struct(s), heap size 1136, 1 row lock(s)
10 MySQL thread id 520, OS thread handle 140181164885760, query id 21845 zlm1 192.168.56.100 zlm
11 TABLE LOCK table `zlm`.`t1` trx id 2997677 lock mode IX
12 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997677 lock_mode X locks gap before rec
13 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
14  0: len 4; hex 00000008; asc     ;;
15  1: len 6; hex 0000002dbd6c; asc    - l;;
16  2: len 7; hex 47000002580316; asc G   X  ;;
17  3: len 4; hex 00000006; asc     ;;
18  4: len 4; hex 00000006; asc     ;;
19  5: len 4; hex 00000014; asc     ;;
20  
21 //Session2:
22 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=7 for update;
23 Query OK, 0 rows affected (0.00 sec)
24 
25 Empty set (0.00 sec)
26 
27 //Lock information of session2.
28 ---TRANSACTION 2997678, ACTIVE 14 sec
29 2 lock struct(s), heap size 1136, 1 row lock(s)
30 MySQL thread id 519, OS thread handle 140181022738176, query id 21890 zlm1 192.168.56.100 zlm
31 TABLE LOCK table `zlm`.`t1` trx id 2997678 lock mode IX
32 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997678 lock_mode X locks gap before rec
33 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
34  0: len 4; hex 00000008; asc     ;;
35  1: len 6; hex 0000002dbd6c; asc    - l;;
36  2: len 7; hex 47000002580316; asc G   X  ;;
37  3: len 4; hex 00000006; asc     ;;
38  4: len 4; hex 00000006; asc     ;;
39  5: len 4; hex 00000014; asc     ;;
40  
41 //Session1 together with session2 both hold the gap lock(next-key lock) of c1=8.Because gap lock does not block each other.They are actually coexistent util there's a actual inserting operation.Therefore,session2 was not blocked in the case.

# 创建班级表并添加字段:
create  table  class(
id  int(10)  not  null  auto_increment,
name  varchar(30)  not  null  default  " noname",
add_time  datetime  no t null,
primary  key(id)
)
ENGINE = INNODB  charset=utf8;

 

 

Summary

# 1、向表内添加2条数据:如果 add_time 字段为datetime
insert into class(name,add_time) values ("一年级","2018-08-31 15:33");
insert into class(name,add_time) values ("二年级","2018-08-31 15:33");

  • In RR transaction isolation level,the InnoDB locking seems more complicated than that in RC transaction isolation level.
  • InnoDB gap locks in differtent transactions are compatible only if the insert intention appears.
  • Generally,primary index and unique index does not generate gap locks like secondary index does.The exception is that when the condition contains a range of scanned indexes.The gap lock will appear according to the range of condition of query.
  • As for the "select ... for update" statement,if the records are in table,it adds "LOCK_REC_NOT_GAP"(secondary index is "LOCK_ORDINARY") otherwise it adds "LOCK_GAP".

 

 

# 2、向表内添加2条数据:如果 add_time 字段为timestamp

insert into class(name) values ("一年级");

insert into class(name) values ("二年级");

友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有