MySQL之锁机制

锁机制

　　锁：计算机中协调多个进程或线程并发访问某一资源的机制　　

　　有锁：有利有弊
　　弊：整个表被锁，其它数据不能进行操作
　　利：防止资源争夺（如果公司DBA要对数据库数据进行备份，容灾等操作此时防止别人窜改此时就需要加锁）

锁的分类　　　

　　a.对数据操作分类
　　　　读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会相互影响
　　　　写锁(排它锁)：当前写操作没有完成前，它会阻断其它锁和读锁

　　b.对数据操作粒度分　　　　

　　　　表锁：对整张表进行加锁(偏读)
　　　　偏向MyISAM存储引擎，开销小，加锁快，无死锁：锁定粒度大，发生锁冲突的概率最高，并发度最低

　　　　读写锁分析-建表准备数据　　

 1 create table mylock(
 2 id int not null primary key auto_increment,
 3 name varchar(20)
 4 )engine myisam;
 5 
 6 insert into mylock(name) values('a');
 7 insert into mylock(name) values('b');
 8 insert into mylock(name) values('c');
 9 insert into mylock(name) values('d');
10 insert into mylock(name) values('e');
11 
12 select * from mylock;

　　　　常用锁命令

1 手动添加表锁
2 lock table 表名字 read(write), 表名字2 read(write)
3 查看是否加锁表
4 show open tables;
5 释放锁
6 unlock tables;

　　分析：读锁会阻塞写，但是不会堵塞读，而写锁会把读和写都阻塞　　

  1 终端1
  2 mysql> show databases;
  3 +--------------------+
  4 | Database           |
  5 +--------------------+
  6 | autohome           |
  7 | db1                |
  8 | db2                |
  9 | db3                |
 10 | db4                |
 11 | information_schema |
 12 | mysql              |
 13 | performance_schema |
 14 | xfz                |
 15 | xfzsql             |
 16 +--------------------+
 17 10 rows in set (0.31 sec)
 18 
 19 mysql> use db1
 20 Database changed
 21 mysql> create table mylock(
 22     -> id int not null primary key auto_increment,
 23     -> name varchar(20)
 24     -> )engine myisam;
 25 Query OK, 0 rows affected (0.44 sec)
 26 
 27 mysql>
 28 mysql> insert into mylock(name) values('a');
 29 Query OK, 1 row affected (0.17 sec)
 30 
 31 mysql> insert into mylock(name) values('b');
 32 Query OK, 1 row affected (0.04 sec)
 33 
 34 mysql> insert into mylock(name) values('c');
 35 Query OK, 1 row affected (0.05 sec)
 36 
 37 mysql> insert into mylock(name) values('d');
 38 Query OK, 1 row affected (0.05 sec)
 39 
 40 mysql> insert into mylock(name) values('e');
 41 Query OK, 1 row affected (0.04 sec)
 42 
 43 mysql>
 44 mysql> select * from mylock;
 45 +----+------+
 46 | id | name |
 47 +----+------+
 48 |  1 | a    |
 49 |  2 | b    |
 50 |  3 | c    |
 51 |  4 | d    |
 52 |  5 | e    |
 53 +----+------+
 54 5 rows in set (0.00 sec)
 55 
 56 mysql> show open tables;
 57 +--------------------+--------------------------+--------+-------------+
 58 | Database           | Table                    | In_use | Name_locked |
 59 +--------------------+--------------------------+--------+-------------+
 60 | mysql              | columns                  |      0 |           0 |
 61 | mysql              | foreign_keys             |      0 |           0 |
 62 | mysql              | column_type_elements     |      0 |           0 |
 63 | mysql              | index_column_usage       |      0 |           0 |
 64 | mysql              | foreign_key_column_usage |      0 |           0 |
 65 | mysql              | view_table_usage         |      0 |           0 |
 66 | mysql              | index_partitions         |      0 |           0 |
 67 | mysql              | indexes                  |      0 |           0 |
 68 | mysql              | schemata                 |      0 |           0 |
 69 | mysql              | collations               |      0 |           0 |
 70 | mysql              | table_partition_values   |      0 |           0 |
 71 | mysql              | table_partitions         |      0 |           0 |
 72 | mysql              | tables                   |      0 |           0 |
 73 | mysql              | triggers                 |      0 |           0 |
 74 | mysql              | column_statistics        |      0 |           0 |
 75 | mysql              | view_routine_usage       |      0 |           0 |
 76 | mysql              | tablespace_files         |      0 |           0 |
 77 | mysql              | tablespaces              |      0 |           0 |
 78 | mysql              | character_sets           |      0 |           0 |
 79 | mysql              | resource_groups          |      0 |           0 |
 80 | mysql              | catalogs                 |      0 |           0 |
 81 | information_schema | SCHEMATA                 |      0 |           0 |
 82 | db1                | mylock                   |      0 |           0 |
 83 | information_schema | TABLES                   |      0 |           0 |
 84 | mysql              | table_stats              |      0 |           0 |
 85 +--------------------+--------------------------+--------+-------------+
 86 25 rows in set (0.08 sec)
 87 
 88 mysql> lock table mylock read, userinfo write;
 89 Query OK, 0 rows affected (0.33 sec)
 90 
 91 mysql> show open tables;
 92 +--------------------+--------------------------+--------+-------------+
 93 | Database           | Table                    | In_use | Name_locked |
 94 +--------------------+--------------------------+--------+-------------+
 95 | mysql              | columns                  |      0 |           0 |
 96 | mysql              | foreign_keys             |      0 |           0 |
 97 | mysql              | column_type_elements     |      0 |           0 |
 98 | mysql              | index_column_usage       |      0 |           0 |
 99 | mysql              | foreign_key_column_usage |      0 |           0 |
100 | mysql              | view_table_usage         |      0 |           0 |
101 | mysql              | index_partitions         |      0 |           0 |
102 | mysql              | indexes                  |      0 |           0 |
103 | mysql              | schemata                 |      0 |           0 |
104 | db1                | tb1                      |      1 |           0 |
105 | mysql              | collations               |      0 |           0 |
106 | mysql              | table_partition_values   |      0 |           0 |
107 | mysql              | table_partitions         |      0 |           0 |
108 | mysql              | tables                   |      0 |           0 |
109 | mysql              | triggers                 |      0 |           0 |
110 | mysql              | column_statistics        |      0 |           0 |
111 | mysql              | view_routine_usage       |      0 |           0 |
112 | mysql              | tablespace_files         |      0 |           0 |
113 | mysql              | tablespaces              |      0 |           0 |
114 | mysql              | character_sets           |      0 |           0 |
115 | mysql              | resource_groups          |      0 |           0 |
116 | mysql              | catalogs                 |      0 |           0 |
117 | information_schema | SCHEMATA                 |      0 |           0 |
118 | db1                | mylock                   |      1 |           0 |
119 | information_schema | TABLES                   |      0 |           0 |
120 | mysql              | table_stats              |      0 |           0 |
121 | db1                | userinfo                 |      1 |           0 |
122 +--------------------+--------------------------+--------+-------------+
123 27 rows in set (0.00 sec)
124 
125 mysql> unlock tables;
126 Query OK, 0 rows affected (0.00 sec)
127 
128 mysql> show open tables;
129 +--------------------+--------------------------+--------+-------------+
130 | Database           | Table                    | In_use | Name_locked |
131 +--------------------+--------------------------+--------+-------------+
132 | mysql              | columns                  |      0 |           0 |
133 | mysql              | foreign_keys             |      0 |           0 |
134 | mysql              | column_type_elements     |      0 |           0 |
135 | mysql              | index_column_usage       |      0 |           0 |
136 | mysql              | foreign_key_column_usage |      0 |           0 |
137 | mysql              | view_table_usage         |      0 |           0 |
138 | mysql              | index_partitions         |      0 |           0 |
139 | mysql              | indexes                  |      0 |           0 |
140 | mysql              | schemata                 |      0 |           0 |
141 | db1                | tb1                      |      0 |           0 |
142 | mysql              | collations               |      0 |           0 |
143 | mysql              | table_partition_values   |      0 |           0 |
144 | mysql              | table_partitions         |      0 |           0 |
145 | mysql              | tables                   |      0 |           0 |
146 | mysql              | triggers                 |      0 |           0 |
147 | mysql              | column_statistics        |      0 |           0 |
148 | mysql              | view_routine_usage       |      0 |           0 |
149 | mysql              | tablespace_files         |      0 |           0 |
150 | mysql              | tablespaces              |      0 |           0 |
151 | mysql              | character_sets           |      0 |           0 |
152 | mysql              | resource_groups          |      0 |           0 |
153 | mysql              | catalogs                 |      0 |           0 |
154 | information_schema | SCHEMATA                 |      0 |           0 |
155 | db1                | mylock                   |      0 |           0 |
156 | information_schema | TABLES                   |      0 |           0 |
157 | mysql              | table_stats              |      0 |           0 |
158 | db1                | userinfo                 |      0 |           0 |
159 +--------------------+--------------------------+--------+-------------+
160 27 rows in set (0.00 sec)
161 
162 mysql> lock table mylock read;
163 Query OK, 0 rows affected (0.00 sec)
164 
165 mysql> select * from mylock;
166 +----+------+
167 | id | name |
168 +----+------+
169 |  1 | a    |
170 |  2 | b    |
171 |  3 | c    |
172 |  4 | d    |
173 |  5 | e    |
174 +----+------+
175 5 rows in set (0.00 sec)
176 
177 终端2
178 mysql> use db1
179 Database changed
180 mysql> show tables;
181 +---------------+
182 | Tables_in_db1 |
183 +---------------+
184 | mylock        |
185 | tb1           |
186 | userinfo      |
187 | v1            |
188 +---------------+
189 4 rows in set (0.07 sec)
190 
191 mysql> select * from mylock;
192 +----+------+
193 | id | name |
194 +----+------+
195 |  1 | a    |
196 |  2 | b    |
197 |  3 | c    |
198 |  4 | d    |
199 |  5 | e    |
200 +----+------+
201 5 rows in set (0.00 sec)
202 # 读锁会阻塞写，但是不会堵塞读，而写锁会把读和写都阻塞

　　表锁分析　　

 1 # 1.表锁分析 查看状态
 2 mysql> show status like 'table%';
 3 +----------------------------+-------+
 4 | Variable_name              | Value |
 5 +----------------------------+-------+
 6 | Table_locks_immediate      | 116   |
 7 | Table_locks_waited         | 0     |
 8 | Table_open_cache_hits      | 0     |
 9 | Table_open_cache_misses    | 0     |
10 | Table_open_cache_overflows | 0     |
11 +----------------------------+-------+
12 5 rows in set (0.01 sec)
13 # Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁就增加1
14 # Table_locks_waited：出现表级锁争用而等待的次数，没等待一次就增加1，值越大说明表级锁争用越严重
15 # 所以Myisam的读写锁调度使写优先，不适合做写为主表的引擎

　　行锁分析　

  1 # 行锁：对表中行进行加锁
  2 # 偏向InnoDB存储引擎，开销慢，加锁快，会出现死锁：锁定粒度小，发生锁冲突的概率最低，并发度也最高        
  3 # InnoDB与Myisam最大的不同点：1.支持事务2.采用行级锁
  4 # 行锁分析建表
  5 create table test(a int(11), b varchar(16))engine=innodb;
  6 insert into test(a,b)values(1,'b2'),
  7 (3,'3000'),(4,'4000'),(5,'5000'),(6,'6000'),(7,'7000'),(8,'8000'),(9,'9000'),(1,'b1');
  8 create index idx_test_innodb_a on test(a);
  9 create index idx_test_innodb_b on test(b);
 10 select * from test;
 11 # 自动提交关了，必须手动写commit才能提交
 12 set autocommit = 0;
 13 终端1
 14 mysql> set autocommit = 0;
 15 Query OK, 0 rows affected (0.00 sec)
 16 
 17 mysql> update test set b='4001' where a=4;
 18 Query OK, 1 row affected (0.04 sec)
 19 Rows matched: 1  Changed: 1  Warnings: 0
 20 
 21 mysql> select * from test;
 22 +------+------+
 23 | a    | b    |
 24 +------+------+
 25 |    1 | b2   |
 26 |    3 | 3000 |
 27 |    4 | 4001 |
 28 |    5 | 5000 |
 29 |    6 | 6000 |
 30 |    7 | 7000 |
 31 |    8 | 8000 |
 32 |    9 | 9000 |
 33 |    1 | b1   |
 34 +------+------+
 35 9 rows in set (0.00 sec)
 36 
 37 mysql> commit;
 38 Query OK, 0 rows affected (0.00 sec)
 39 
 40 mysql> update test set b='4002' where a=4;
 41 Query OK, 1 row affected (0.00 sec)
 42 Rows matched: 1  Changed: 1  Warnings: 0
 43 
 44 mysql> select * from test;
 45 +------+------+
 46 | a    | b    |
 47 +------+------+
 48 |    1 | b2   |
 49 |    3 | 3000 |
 50 |    4 | 4002 |
 51 |    5 | 5000 |
 52 |    6 | 6000 |
 53 |    7 | 7000 |
 54 |    8 | 8000 |
 55 |    9 | 9000 |
 56 |    1 | b1   |
 57 +------+------+
 58 9 rows in set (0.00 sec)
 59 
 60 mysql> commit;
 61 Query OK, 0 rows affected (0.07 sec)
 62 
 63 mysql> select * from test where a=4;
 64 +------+------+
 65 | a    | b    |
 66 +------+------+
 67 |    4 | 4003 |
 68 +------+------+
 69 1 row in set (0.01 sec)
 70 
 71 mysql> update test set b='4005' where a=4;
 72 Query OK, 1 row affected (0.00 sec)
 73 Rows matched: 1  Changed: 1  Warnings: 0
 74 
 75 mysql> commit;
 76 Query OK, 0 rows affected (0.00 sec)
 77 
 78 
 79 终端2
 80 mysql> set autocommit=0;
 81 Query OK, 0 rows affected (0.00 sec)
 82 
 83 mysql> select * from test;
 84 +------+------+
 85 | a    | b    |
 86 +------+------+
 87 |    1 | b2   |
 88 |    3 | 3000 |
 89 |    4 | 4000 |
 90 |    5 | 5000 |
 91 |    6 | 6000 |
 92 |    7 | 7000 |
 93 |    8 | 8000 |
 94 |    9 | 9000 |
 95 |    1 | b1   |
 96 +------+------+
 97 9 rows in set (0.00 sec)
 98 mysql> commit;
 99 Query OK, 0 rows affected (0.01 sec)
100 # 如果会话2不提交，要发生重复读，这样查出来还是4000 取消了自动提交
101 
102 mysql> select * from test;
103 +------+------+
104 | a    | b    |
105 +------+------+
106 |    1 | b2   |
107 |    3 | 3000 |
108 |    4 | 4001 |
109 |    5 | 5000 |
110 |    6 | 6000 |
111 |    7 | 7000 |
112 |    8 | 8000 |
113 |    9 | 9000 |
114 |    1 | b1   |
115 +------+------+
116 9 rows in set (0.00 sec)
117 
118 mysql> update test set b='4003' where a=4;
119 Query OK, 1 row affected (15.39 sec)
120 Rows matched: 1  Changed: 1  Warnings: 0
121 # 这里就会阻塞，同时修改同一行数据，只有等会话1提交之后，才能被会话2修改
122 
123 mysql> commit;
124 Query OK, 0 rows affected (0.00 sec)
125 
126 mysql> select * from test where a=4;
127 +------+------+
128 | a    | b    |
129 +------+------+
130 |    4 | 4003 |
131 +------+------+
132 1 row in set (0.01 sec)
133 
134 mysql> update test set b='9001' where a=9;
135 Query OK, 1 row affected (0.00 sec)
136 Rows matched: 1  Changed: 1  Warnings: 0
137 
138 mysql> commit;
139 Query OK, 0 rows affected (0.01 sec)
140 
141 mysql> select * from test;
142 +------+------+
143 | a    | b    |
144 +------+------+
145 |    1 | b2   |
146 |    3 | 3000 |
147 |    4 | 4005 |
148 |    5 | 5000 |
149 |    6 | 6000 |
150 |    7 | 7000 |
151 |    8 | 8000 |
152 |    9 | 9001 |
153 |    1 | b1   |
154 +------+------+
155 9 rows in set (0.00 sec)
156 # 行锁：若操作同一行数据就要阻塞，若操作不一同行数据没影响
157 
158 # 索引失效行锁变表锁严重警告
159 mysql> show index from test;
160 +-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
161 | Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
162 +-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
163 | test  |          1 | idx_test_innodb_a |            1 | a           | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
164 | test  |          1 | idx_test_innodb_b |            1 | b           | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
165 +-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
166 2 rows in set (0.00 sec)
167 
168 # 终端1
169 mysql> update test set a=40 where b=4005;
170 Query OK, 1 row affected (0.00 sec)
171 Rows matched: 1  Changed: 1  Warnings: 0
172 
173 mysql> select * from test;
174 +------+------+
175 | a    | b    |
176 +------+------+
177 |    1 | b2   |
178 |    3 | 3000 |
179 |   40 | 4005 |
180 |    5 | 5000 |
181 |    6 | 6000 |
182 |    7 | 7000 |
183 |    8 | 8000 |
184 |    9 | 9001 |
185 |    1 | b1   |
186 +------+------+
187 9 rows in set (0.00 sec)
188 
189 mysql> commit;
190 Query OK, 0 rows affected (0.03 sec)
191 
192 # 终端2
193 mysql> update test set b='9002' where a=9;
194 Query OK, 1 row affected (35.44 sec) # 索引失效行锁变表锁 阻塞35多秒
195 Rows matched: 1  Changed: 1  Warnings: 0

　　间隙锁GAP危害

 1 # 间隙锁GAP危害
 2 # 范围内进行更新即使不存在的值要想被插入必须等提交之后才能插入操作
 3 终端1
 4 mysql> update test set b='6666' where a>1 and a<6; # 不存在a=2行数据
 5 Query OK, 3 rows affected (0.00 sec)
 6 Rows matched: 3  Changed: 3  Warnings: 0
 7 mysql> commit;
 8 Query OK, 0 rows affected (0.02 sec)
 9 
10 终端2
11 mysql> insert into test values(2, '2000'); # 提交之后才能被插入
12 Query OK, 1 row affected (35.31 sec) # 这里保证数据一致性

　　如何锁定一行数据

 1 终端1
 2 mysql> begin;
 3 Query OK, 0 rows affected (0.01 sec)
 4 mysql> select * from test where a=8 for update;
 5 +------+------+
 6 | a    | b    |
 7 +------+------+
 8 |    8 | 8000 |
 9 +------+------+
10 1 row in set (0.00 sec)
11 
12 mysql> commit;
13 Query OK, 0 rows affected (0.00 sec)
14 终端2
15 mysql> update test b='xxx' where a=8;
16 Query OK, 1 row affected (31.48 sec) # 锁定被阻塞等提交才能被操作
17 Rows matched: 1  Changed: 1  Warnings: 0
18 mysql> select * from test;
19 +------+------+
20 | a    | b    |
21 +------+------+
22 |    1 | b2   |
23 |    3 | 6666 |
24 |    4 | 6666 |
25 |    5 | 6666 |
26 |    6 | 6000 |
27 |    7 | 7000 |
28 |    8 | xxx  |
29 |    9 | 9002 |
30 |    1 | b1   |
31 |    2 | 2000 |
32 +------+------+
33 10 rows in set (0.00 sec)

　　页锁

　　　　开销和加锁时间界于表锁与行锁之间的锁，会出现死锁，，锁定粒度界于两锁之间，并发度一般