mysql--锁 mysql--锁

锁分类根据锁粒度和兼容性划分 |----表锁（MyISAM，InnoDB开销小，加锁快，不死锁，并发差）
|----共享锁（S锁）：加锁后其他用户可读不可写
|----排他锁（X锁）：加锁后其他用户不可读写
|----意向共享锁（IS锁）：innoDB中为了兼容行锁和表锁，并存多个(表级别锁)
|----意向排他锁（IX锁）：innoDB中为了兼容行锁和表锁，只有一个(表级别锁)
|----行锁（InnoDB开销小，加锁快，不死锁，并发差）
|----共享锁（S锁）：加锁后其他用户可读不可写
|----排他锁（X锁）：加锁后其他用户不可写但是可读(这里是快照读)！！！
|----页面锁（BDB，各项性能居中，会出现死锁）
根据加锁机制划分 |----乐观锁：乐观的认为不会出现并发，手动加一个版本号用于判断（不是mvcc的那个版本号）
|----悲观锁：行锁，表锁这些都是悲观的。
行锁的三种算法 |----记录锁：单个行记录上的锁

|----间隙锁：锁定一个范围，但不包括记录本身。
|----nextKey锁：记录锁+间隙锁，锁定一个范围，并且锁定记录本身
1.表锁：
1.1共享读锁（S锁/共享锁）
显式加锁：locktable表名read(local)
隐式加锁：select自动给涉及到所有表加读锁
解锁：unlock tables
加锁之后，其他用户对同一表可读不可写。
LOCAL修饰符表示允许在其他会话中对在当前会话中获取了READ锁的的表执行插入
1.2独占写锁（X锁/排他锁）
显式加锁：locktable表名write
隐式加锁：update，delete，insert自动给所涉及到的表加写锁
解锁：unlock tables
加锁之后，其他用户对同一表不可读写
1.3MyISAM为什么不会出现死锁？
MyISAM在select之前，会自动给涉及到所有表加读锁；在update，delete，insert之前，会自动给所涉及到的表加写锁。这个过程都是自动的，不需要用户使用lock table显式加锁。MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁的原因。
1.4MyISAM的并发插入
MyISAM中的系统变量concurrent_insert，专门处理其并发插入的行为，其值可为0，1(默认值)，2
concurrent_insert = 0，不允许并发插入
concurrent_insert = 1，如果MyISAM表中没有空洞，myisam允许在一个进程读表的同时，另一个进程从表尾插入记录
【mysql--锁】concurrent_insert = 2，无论myisam表中有没有空洞，都允许在表尾并发插入记录
1.5如果两个进程分别请求读锁和写锁，mysql将如何处理？
写进程先获得锁，即使读请求先到锁等待队列，写请求后到，写锁也会插入到读锁队列。mysql认为写请求比读请求更重要。大量的更新操作会造成查询操作很难获得读锁，从而可能永远堵塞。这就是myisam不太适合于有大量更新操作和查询操作的原因
解决方法： setlow_priority_updates= 1将读的优先级提高
1.6意向共享锁（IS锁）意向排他锁（IX锁）仔细想想还是把意向锁放在表锁下面，原因有二：第一意向锁是表级别的锁，第二意向锁的作用是为了兼容表锁和行锁，刚好引出下面的行锁来。
作用：InnoDB为了实现多粒度锁机制。兼容表锁和行锁
举例：假如没有意向锁且行锁和表锁兼容，事务A锁住表中的一行（写锁，其他事务就不可能修改这一行），事务B锁住整个表（写锁，B可就以随便修改表内数据），这样事务A，事务B是相悖的
意向锁如何实现行锁和表锁兼容？：还是上面的例子，事务A申请行锁（写锁），数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞
如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。意向锁是InnoDB自动加的，不需用户干预。对于update、delete和insert语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通select语句，InnoDB不会加任何锁。

文章图片
意向锁是表级别锁。假如此时你要加一个表锁，意向锁如果是行级别锁则需要遍历全表
2.行锁： InnoDB行锁是给索引上的索引项加锁来实现的。只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
2.1共享锁(S锁) 显式加锁：select ... LOCK IN SHARE MODE
隐式加锁：innoDB的select不会加任何锁（这是快照读，详情参考mvcc）
加锁之后，其他用户对同一行可读不可写
2.2排他锁(X锁) 显式加锁：select ... FOR UPDATE
隐式加锁：innoDB的update，delete，insert操作自动给所涉及到的表加排他锁
加锁之后，其他用户对同一行不可写但是可读（因为innoDB的select默认是快照读，不上锁）
2.3记录锁(Record Locks)
记录锁就是为某行记录加锁，它封锁该行的索引记录

SELECT * FROM table WHERE id = 1 FOR UPDATE;

id 列必须为唯一索引列或主键列，查询语句必须为精准匹配（=），不能为 >、<、like等。否则上述语句加的锁就会变成临键锁。
2.4间隙锁(GAP锁)
当我们用范围条件检索数据并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加记录锁；对于键值在条件范围内但并不存在的记录，InnoDB也会对其加锁，这就是间隙锁
举例：下图为表数据和索引B的数据结构，事务A拿到了X锁。事务B要修改数据。

文章图片
事务A：select * from z where b=6 for update
事务B：update z set b=7 where id=7 （阻塞）
update z set id=6 where id=8 （阻塞）
我们可以看到，不管是将b修改为7还是将id修改为8，都在锁住的范围内

文章图片
2.5临键锁(next-key锁)
我们可以将next-key理解为一种特殊的算法。
Next-Key = 记录锁 + 间隙锁
作用：1.和mvcc一起解决幻读的问题2.满足复制，备份需要
假如不使用间隙锁，其它事务插入了empid大于100的记录，那么本事务再次执行上述语句，前后两次结果不一致就会发生幻读。
mysql的恢复机制是按照事务提交的顺序记录sql语句(执行binlog中的sql语句),说白了，还是不允许出现幻读的情况
2.6行锁争用情况
可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：

文章图片
3.死锁：
3.1什么是死锁？多个事务在同一资源上互相占用形成回路。这就是死锁
3.2死锁的例子下面是一个死锁的例子，事务A想修改id=2的数据，但是必须要事务B释放。事务B想要修改id=1的数据，同样也在等待事务A的结束。
t1时刻 A事务执行：select * from test where id=1 for update;
t2时刻 B事务执行:select * from test where id=2 for update;
t3时刻 A事务执行：update test set id=id where id=2;
t4时刻 B事务执行：update test set id=id where id=1;
3.3解决方法：
部分或完全回滚其中一个事务（ InnoDB默认回滚最少行级排他锁的事务）
3.4预防手段：
1.innodb_lock_wait_timeoutInnoDB的锁超时等待参数
2.合理设计索引
3.降低隔离级别（看业务是否允许）
4.大事务拆小，尽量一次性把锁拿全
3.5定位死锁：
1.查看隔离级别select @@tx_isolation;
2.查看当前线程show processlist;
3.show innodb status：返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
4.重要的三张锁的监控表innodb_trx，innodb_locks，innodb_lock_waits