3、mysql事务与锁

一、事务特性

（一）、哪些存储引擎支持事务？Innodb、NDB

（二）、事务的四大特性？
1、原子性（atomicity）：用undo log回滚来实现
2、一致性（consistent）：
3、隔离性（isolation）：
4、持久性（durable）：数据库的崩溃回复通过redo log来实现。doubule write buffer（双写缓冲）为数据页创建副本，保证页的完整性。

（三）、什么时候会出现事务？
1、前提条件
select version();
show variables like '%engine%';
show global variables like 'tx_isolation';--->REPEATABLE-READ
show global variables like 'autocommit';--->自动提交功能，默认是打开的
关闭自动提交：set session autocommit = off;
2、事务执行过程
a、开启事务：begin; 或 start transaction;
b、执行sql：......
c、结束事务：commit; 或 rollback;------->事务持有的锁会释放掉，客户端与mysql的连接断开的时候锁也会被释放

（四）、事务并发的三大问题（读一致性的问题）、与隔离级别、MVCC。
PostGreSQL 事务操作_sunxj1222的博客-CSDN博客
注：事务的id是自增的
1、postgresql
txid_current()：当前事务的id
xmin：inset时当前事务的id
xmax：delete时当前事务的id
2、mysql
db_trx_id：inset时当前事务的id
db_roll_ptr:delete时当前事务的id

二、锁的分类，行锁原理及算法

（一）myisam表锁，innodb不支持
a、lock tables 表名 read;
b、lock tables 表名 write;
c、unlock tables;

（二）行锁
1、shared locks：读锁、s锁、可重入锁
a、共享锁：又称为读锁，简称s锁，顾名思义，共享锁就是多个事务对于同一数据可以共享的一把锁，都能访问到数据，但只能读不能修改。总之，有锁的才可以修改数据，但可能有多个事务同时有锁。
b、加锁：select * from student where id = 1 lock in share mode;
c、解锁：commit、rollback;

2、exclusive locks：写锁、x锁、不可重入锁
a、排他锁：又称写锁，简称x锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排查锁），只有该获取了排他锁的事务可以对数据进行修改，其他事务也可以进行读取。
b、自动加锁：delete、update、insert默认加上x锁。
c、手动加锁：select * from student where id = 1 for update；
d、解锁：commit、rollback;

（三）、事务的隔离级别（数据的可见性）：
select：通过mvcc实现
delete、update、insert、select...for update：通过锁来实现

（三）表锁-意向所
a、数据库自己维护，不能手工加
b、只要表中某一行加了读锁或写锁，那么这个表就会表就会被加上了意向锁，再进行lock tables 表名;的时候就会失败。

1、意向共享锁（IS：intention shared lock）
a、表示事务准备给数据行加入共享锁，也就是一个数据行加共享锁前必须先取得该表的IS锁。

2、意向排他锁（IX：intention exclusive lock）
a、表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

（四）锁的作用？
1、解决资源（数据行）并发访问的问题。

（五）写锁（select...for update;）到底锁的是什么？
1、没有索引的表：isolaton=repeatable read时，会将整个表的主键索引（rowid）都锁住；isolaton=read commited时，锁住那一行。
2、有主键索引的表：会将主键索引的那一行锁住
3、有主键也有唯一索引的表：会将主键索引的那一行锁住

---->where条件不走索引的表：isolaton=repeatable read时，会将整个表都锁住；isolaton=read commited时，锁住那一行。
 

（六）行锁的算法--->锁的范围
1、区间划分，如图：

注：字符用ASICC来排序

2、record lock：记录锁
a、条件：唯一性索引（唯一、主键）等值查询，精准匹配
b、锁记录

3、gap lock：间隙锁
a、条件：范围查询，记录不存在
b、锁区间

4、next-key-lock：临键锁
a、条件：范围查询，包含记录和区间
b、锁临键

(七)、死锁
a、创建写锁的超时时间：show variables like 'innodb_lock_wait_timeout'; 默认50s。
1、死锁的发生；
事务A：锁id=1，       锁id=2，
事务B：       锁id=2，       锁id=1，
a、mysql会自动检测死锁，事务B成功，事务A失败。
b、死锁会造成线程阻塞，io能力下降

2、问题排查参数
a、查看innodb当前锁的情况：show status like 'innodb_row_lock_%';
innodb_row_lock_current_waits：当前正持有锁的事务
innodb_row_lock_waits：mysql启动后总共等待的事务
b、查询当前事务语句：select * from infomation_schema.INNODB_TRX;
trx_stated：当前事务的状态，如：running、lock wait
trx_mysql_thread_id：当前线程的id。可以通过kill id，杀死这个线程。
c、查询当前用锁语句：select * from infomation_schema.INNODB_LOCKS;
lock_mode：哪种锁
d、查询当前等待锁语句：select * from infomation_schema.INNODB_LOCK_WAITS;

e、查询服务端线程的状态：show processlist; 或 select * from information_schema.processlist;  host：哪个客户端
f、mysql启动后的各种状态：show status;
g、查看存储引擎的运行信息：show engine innodb status;