MySQL事务和锁
一、事务的介绍
数据库事务：Database Transaction
是指最为单个逻辑工作单元执行的一系列操作，要么完全执行，要么完全不执行。
一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性、持久性）属性。
1.1 原子性（Atomicity）
事务开始后所有操作，要么全部完成，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就想原子，是物质构成的基本单位。
1.2 一致性 （Consistency）
事务开始后结束后，数据库的完整性约束没有破坏。比如B向C转账，不可能B扣钱了，C却没有收到。
1.3 隔离性
同一时间，只允许一个事务请求统一数据，不同事务之间彼此没有任何干扰。比如一张银行卡，A在取钱，B就不能取钱。
1.4 持久性
事务完成后，事务对数据库的所有更新将被保存到数据库，不能归滚。
小结：原子性是事务隔离的基础，隔离性和持久性是手段，最终为了保证数据的一致性。
1.5 事务在并发时候会出现的问题
更新丢失；
脏读； 事务A读取了事务B更新的数据，然后B回滚，那么A读取的是脏数据；
不可重复读； 在事务A中，先读取了数据，然后数据经过修改，再次读取数据，两次读的数据不一致，解决办法，只有在修改事务完全提交后才可以读取数据。
幻读 不可重复读侧重于修改，幻读侧重于新增或者删除，解决不可重复读只需要锁住满足条件的行，解决幻读需要锁表。
幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。
二、事务隔离的四个级别
2.1 Read Uncommitted 读未提交
最低的隔离级别，什么都不需要做，一个事务可以读到另外一个事务未提交的结果，所有的并未问题都会发生，解决了更新丢失。
2.2 Read Committed 读已经提交
只有在事务提交后，其更新 结果才会被其他事务看见。，解决了更新丢失，脏读。
大多数数据库的默认隔离级别为 ：Read Commited 读已经提交，如Oracle,DB2,Sql Server.
2.3 Repeated Read 重复读
在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。
解决了更新丢失，脏读，不可重复读。
2.4 Serialization 串行化
事务串行化执行，隔离级别搞，牺牲了系统的并发性，可以解决并发的所有问题。
2.5 查询事务的隔离级别
mysql> show variables like '%tx_isolation%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.03 sec)
或者：
mysql> select @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set, 1 warning (0.00 sec)
三、MVCC 多版本并发控制
是指一种提高并发的技术。
最早的数据库版本，只有读读之间可以并发；但读写，写读，谢谢都要组三。
引入多版本之后，只有写写之间相互阻塞，其他三种操作（读读、读写，写读）都可以并行，这样大幅度提高了InnoDB的并发度。
在内部实现中，InnoDB是通过在undo log 中实现的，通过undo log 可以找回数据的历史版本，找回的数据历史版本可以提供给用户读，也可以在回滚的时候覆盖数据页上的数据。
3.1 MVCC多版本控制实现原理
每个事务启动时，InnoDB会为每个启动的事务提供一个当前时刻的镜像；
为了实现此功能，InnoDB会为每个表提供2个隐藏字段：
一个用户保存行的创建时间，
一个用于保存行的失效时间（里面存储的是系统版本号：system version number）
在某一个事务中，使用比当前事务相等或者更旧的版本号数据，从而保证其所读取的数据都是过去的数据。
3.2 MVCC 的优点
在读取数据的时候，innodb几乎不用任何锁，每个查询都通过版本检查，只获得自己需要的数据版本，从而大大提高了系统的并发度。
3.3 MVCC的缺点
为了实现多版本，innodb必须多每行增加响应的字段来存储版本信息，同事需要维护每一行的版本信息，而且在检索行的时候，需要镜像版本的比较，因而降低了查询的效率；
innodb还需要定期清理不需要的行版本，及时回收空间，这也增加了一些开销。
3.4 MVCC注意事项
只在2个隔离级别下有效： read commited 和repeatable read。
其他两个隔离级别和MVCC不兼容：
READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。
SERIALIZABLE 对所有读取的行都加锁。
3.5 MySQL手动执行事务操作命令
start transaction;
rollback;
savepoint a;
rollback to a;
commit;
如果没有显示启动事务，每个语句都会当作一个独立的事务，执行完成会被自动提交：
mysql> select @@global.autocommit; +---------------------+ | @@global.autocommit | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec)
四、四种隔离级别的案例
4.1、READ-UNCOMMITTED 读未提交
打开两个窗口，都将事务隔离级别设置称为READ-UNCOMMITTED
mysql> set session tx_isolation='READ-UNCOMMITTED'; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set, 1 warning (0.00 sec)
窗口一，
mysql> create table yq(id int,name varchar(30)); Query OK, 0 rows affected (0.04 sec) mysql> select * from yq; Empty set (0.04 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into yq values(1,'chg'); Query OK, 1 row affected (0.01 sec) mysql> select * from yq; +------+------+ | id | name | +------+------+ | 1 | chg | +------+------+ 1 row in set (0.00 sec)
窗口二
mysql> select * from yq; +------+------+ | id | name | +------+------+ | 1 | chg | +------+------+ 1 row in set (0.00 sec)
窗口一没有提交，窗口二就可以查询到数据；
窗口一回滚：
mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> select * from yq; Empty set (0.01 sec)
窗口二：
mysql> select * from yq; Empty set (0.00 sec)
4.2 READ COMMITTED 读已经提交
两个窗口都设置为READ-COMMITTED
mysql> set session tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set, 1 warning (0.01 sec)
窗口一：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into yq values(4,'anning'); Query OK, 1 row affected (0.02 sec) mysql> select * from yq; +------+--------+ | id | name | +------+--------+ | 2 | chg | | 3 | qilihe | | 4 | anning | +------+--------+ 3 rows in set (0.00 sec)
窗口二查询：并没有刚才窗口一插入的数据
mysql> select * from yq; +------+--------+ | id | name | +------+--------+ | 2 | chg | | 3 | qilihe | +------+--------+ 2 rows in set (0.00 sec)
窗口一提交：
mysql> commit; Query OK, 0 rows affected (0.01 sec)
窗口二查询：
mysql> select * from yq; +------+--------+ | id | name | +------+--------+ | 2 | chg | | 3 | qilihe | | 4 | anning | +------+--------+ 3 rows in set (0.00 sec)
4.3 Repeatable Read 可重复读
将两个窗口的隔离级别都设置为REPEATABLE-READ
不可重复读：事务A事先读取了数据，事务B紧接了更新数据并提交了事务，而事务A再次读取该数据扣款时，数据已经发生了改变。
可重复读：A事务只要不提交，每次读到的内容一致：
A事务读取数据（假如有10条），此时B事务插入或删除一条符合条件的数据但未提交事务，A事务再次读取，结果一样，因为B并没提交事务！说明可重复读的级别也解决了脏读。
A事务读取数据（假如有10条），此时B事务插入或删除一条符合条件的数据并提交事务，然后B事务也查了查，数据已经改变了，A事务再次读取，结果还是一样！
A事务表示很疑惑，以为是B事务没提交造成的，B事务一脸懵，想着明明已经提交事务了呀，为了配合A事务，B事务又添加或删除一条符合条件的数据并提交事务，并且B事务也查了查数据并让A看了看，没错，查到的数据已经改变（数据多了或少了一条），A事务这回确认B事务已经修改并提交了，就再次读取，发现无论读取多少次结果都一样！A表示出现了幻觉!
这就叫可重复读！因为每次读的都一样，实际上硬盘数据已经改变，A每次读取的都是备份数据！
此时A事务提交后再去读取，终于读取结果是B事务修改后的数据了！
mysql> set session tx_isolation='REPEATABLE-READ'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)
窗口一：开始一个事务
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into yq values(1,'qilihe'); Query OK, 1 row affected (0.00 sec)
mysql> select * from yq;
+------+--------+
| id | name |
+------+--------+
| 1 | qilihe |
+------+--------+
1 row in set (0.00 sec)
窗口二开启一个事务;
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from yq; Empty set (0.00 sec)
窗口一提交事务
mysql> commit; Query OK, 0 rows affected (0.03 sec) mysql> select * from yq; +------+--------+ | id | name | +------+--------+ | 1 | qilihe | +------+--------+ 1 row in set (0.00 sec)
窗口二查询没有这条数据
mysql> select * from yq; Empty set (0.00 sec)
窗口二提交这个事务，然后查询，有这条数据。
mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from yq; +------+--------+ | id | name | +------+--------+ | 1 | qilihe | +------+--------+ 1 row in set (0.00 sec)
4.4 serializable 串行化
两个窗口都设置串行
mysql> set session tx_isolation='serializable'; Query OK, 0 rows affected, 1 warning (0.00 sec)
窗口一：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into yq values(4,'wlxy'); Query OK, 1 row affected (0.01 sec)
窗口二; 查询卡住了
mysql> select * from yq;
窗口一提交
mysql> commit; Query OK, 0 rows affected (0.00 sec)
窗口二正常查询
mysql> select * from yq; +------+--------+ | id | name | +------+--------+ | 1 | qilihe | | 4 | wlxy | +------+--------+ 2 rows in set (0.00 sec)
第二种情况，查询阻塞插入
第一个窗口执行插入操作：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from yq; +------+--------+ | id | name | +------+--------+ | 1 | qilihe | | 4 | wlxy | +------+--------+ 2 rows in set (0.00 sec)
第二个窗口：执行插入卡死
mysql> insert into yq values(6,'chguan');
第一个窗口执行commit，第二窗口插入成功。
五、锁
S锁：Shared Lock，中文为共享锁，有时候也称为读锁，即Read Lock。S锁之间是共享的，或者不阻塞的，当事务读取一条记录时候，需要先获取该记录的S锁。事务 T1 对记录 R1 加上了 S 锁，那么事务 T1 可以读取 R1 这一行记录，但是不能修改 R1，其他事务 T2 可以继续对 R1 添加 S 锁，但是不能添加 X 锁，只有当 R1 上面的 S 锁释放了，才能加上 X 锁。
X锁，英文为Exclusive Lock ，中午为排他锁，有时候我们也称为写锁，即Write Lock。X锁是具有排他性的，即一个写锁会阻塞其他的X锁和S锁。
5.1 打开锁的日志输出
mysql> set global innodb_status_output_locks=1;
5.2查看锁的状态
mysql> show engine innodb status\G TRANSACTIONS ------------ Trx id counter 17691 Purge done for trx's n:o < 17687 undo n:o < 0 state: running but idle History list length 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421138731587408, not started 0 lock struct(s), heap size 1136, 0 row lock(s) --------
5.3 查看锁的参数
mysql> show variables like '%innodb%lock%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_api_disable_rowlock | OFF | | innodb_autoinc_lock_mode | 1 | | innodb_deadlock_detect | ON | -----死锁检测 | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 1000 | | innodb_print_all_deadlocks | OFF | ----打开的话死锁的信息会输入到错误日志中去。 | innodb_status_output_locks | ON | ----输出日志 | innodb_table_locks | ON | +--------------------------------+-------+ 10 rows in set (0.00 sec)
5.4 查看出现锁的表
mysql> show open tables where in_use>0; +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | jl | yq | 1 | 0 | +----------+-------+--------+-------------+ 1 row in set (0.02 sec)
方法二：
mysql> mysql> show processlist; +----+------+-----------+------+---------+------+----------+--------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+--------------------------------+ | 3 | root | localhost | jl | Sleep | 197 | | NULL | | 4 | root | localhost | jl | Query | 14 | update | insert into yq values(1,'pjp') | | 5 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------+------+---------+------+----------+--------------------------------+ 3 rows in set (0.00 sec)
方法三：查看正在锁的事务
select * from information_schema.innodb_trx;
方法四：查看正在锁的事务
select * from information_schema.innodb_locks;
查看等待锁的事务：源头
select * from information_schema.innodb_lock_waits;
查看数据库中被锁的表
mysql> show open tables from jl; +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | jl | zg | 0 | 0 | | jl | qh | 0 | 0 | | jl | zg2 | 0 | 0 | | jl | zg1 | 0 | 0 | | jl | yq | 0 | 0 | +----------+-------+--------+-------------+ 5 rows in set (0.00 sec)
5.5 如何避免死锁
应用程序：大事务拆小，大表拆小表；
修改表模式：删除不必要的外键，合理使用索引。
