锁
部分总结参考博客
http://b.codejs.cc/articles/2017/10/23/1508749325215.html
http://blog.csdn.net/cug_jiang126com/article/details/50544728
MyISAM 只支持表锁
show create table crm_member; ##查看引擎
alter table crm_member engine = MyISAM; ##更改表引擎
读锁演示
Session 1 读锁 其它session可以读不可写
lock table crm_member read;
Session 1 锁等待时间设置
select @@global.lock_wait_timeout; ##查看锁等待时间设置
set @@global.lock_wait_timeout = 20; ##设置锁等待时间为20秒
Session 2 查询数据 不影响
MariaDB [member]> select name from crm_member where id = 4;
+------+
| name |
+------+
| 222 |
+------+
1 row in set (0.00 sec)
Session 2 更新数据
MariaDB [member]>
MariaDB [member]> update crm_member set name = '222' where id = 4;
## 进入锁等待
Session 1 查看进程
show processlist;
| 97 | root | localhost | member | Query | 120 | Waiting for table level lock | update crm_member set name = '222' where id = 4 | 0.000 |
## 等待一个表级别的锁
Session 1 释放锁
MariaDB [member]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Session 2 获得锁 立即更新数据
MariaDB [member]> update crm_member set name = '222' where id = 4;
Query OK, 0 rows affected (3 min 9.38 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Session 2 锁等待超时
MariaDB [member]> update crm_member set name = '222' where id = 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
写锁演示
Session 1 添加写锁
MariaDB [member]> lock table crm_member write;
Query OK, 0 rows affected (0.00 sec)
Session 1 添加写锁
MariaDB [member]> lock table crm_member write;
Query OK, 0 rows affected (0.00 sec)
Session 2 读数据
MariaDB [member]> select name from crm_member where id = 4;
Session 1 查看进程
MariaDB [member]> show processlist;
| 109 | root | localhost | member | Query | 2 | Waiting for table metadata lock | select name from crm_member where id = 4 | 0.000 |
## 等待一个元数据的锁
Session 2 读数据 锁等待超时
MariaDB [member]> select name from crm_member where id = 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
InnoDB 锁 既支持表锁（与MyISAM相同） 也支持 行锁。
排它锁演示
方式1 Session 1 直接update 其它session不可以读不可写 除非快照读
MariaDB [member]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [member]> update crm_member set name = 'aaaaaa' where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
方式2 Session 1 for-update 其它session不可以读不可写 除非快照读
MariaDB [member]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [member]> select name from crm_member where id = 4 for update;
Session 2 方式1 事务方式 开始更新操作相同的记录
MariaDB [member]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [member]> update crm_member set name = 'bbb' where id = 4;
## 进入锁等待
Session 2 方式2 普通更新操作
MariaDB [member]> update crm_member set name = 'bbb' where id = 4;
Session 1 查看进程
MariaDB [member]> show processlist;
+-----+------+---------------------+--------+---------+-------+----------+-------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-----+------+---------------------+--------+---------+-------+----------+-------------------------------------------------+----------+
| 109 | root | localhost | member | Query | 2 | updating | update crm_member set name = 'ccc' where id = 4 | 0.000 |
+-----+------+---------------------+--------+---------+-------+----------+-------------------------------------------------+----------+
## 有一个更新等待
Session 2 锁等待超时
MariaDB [member]> update crm_member set name = 'bbb' where id = 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Session 1 查看数据 已经更改
MariaDB [member]> select name from crm_member where id = 4;
+------+
| name |
+------+
| ccc |
+------+
Session 2 读取到的数据没有更改 只能读取未提交版本的数据
MariaDB [member]> select name from crm_member where id = 4;
+------+
| name |
+------+
| bbb |
+------+