mysql -- 锁

1、为什么使用锁？

张三和李四两个人同时来洗浴中心洗脚，可是现在就只有苍老师提供服务，这个时候应该怎么解决谁先洗脚呢？

2、锁的概念

• 锁是计算机协调多个进程或线程并发访问某一资源的机制。
• 在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、
  有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个
  重要因素

3、Mysql中的锁

• 表级锁：开销小，加锁快；不会出现死锁；锁的粒度大，发生锁冲突的概率最高，并发度最低；
• 行级锁：开销大，加锁慢；会出现死锁；锁的粒度最小，发生锁冲突的概率最低，并发度最高；
• 页面锁（间隙锁）：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度
  界于表锁和行锁之间，并发度一般。
  

4、表锁和行锁使用场景

表锁更适合以查询为主，只有少量按索引条件更新数据的应用
行锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用

5、表锁

mysql的表锁有两种模式：表共享读锁和表独占写锁；MyISAM数据库引擎中只支持表锁，不支持行锁。

5.1 读锁

前提条件：现有事务A、B，在事务A中对表user进行加读锁

# 语法如下
lock table user read;

• 事务A：对user表可读但不能更新，对其他表不能进行更新和访问
• 事务B：对user表可读，更新user表会出现等待直到事务A释放锁，可以对其他表进行更新和访问

5.2 写锁

前提条件：现有事务A、B，在事务A中对表user进行加写锁

# 语法如下
lock table user write;

• 事务A：对user表可更新，读操作会阻塞
• 事务B：对user表读和写操作都会阻塞

6、行锁

• 共享锁：又称读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但
  不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
• 排它锁：又称写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允
  许其他事务给这几行上任何锁。包括写锁。

6.1 语法

• 共享读锁：lock in share mode

select * from 表 where 条件 lock in share mode；

• 排它锁：for update

select * from 表 where 条件 for update；

特别注意：
1、两个事务不能锁同一个索引
2、insert ，delete ， update 在事务中都会自动默认加上排它锁。
3、行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

7、锁的等待

本文开头提到张三和李四去洗脚的问题，如果张三抢到了沙发，那张三就能够洗脚，而李四没有抢到沙发他就只能等待张三完事儿后让出沙发，这样李四才能洗脚。在这个例子中，张三李四就是两个进程或者线程，沙发就是锁，苍老师就是进程之间争夺的资源，这就是锁的等待。
在mysql中也存在锁的等待，前段时间在工作中就遇到了事务死锁的问题。下面给大家模拟一下。
数据库中有两张表，system_notification （系统通知表）和 system_notification_user（系统通知用户关联表），表结构如下

有一个需求如下，需要将用户id为173的购物券到期提醒和购物券到账的系统通知标记为已读，然后同事的SQL是这样写的

UPDATE system_notification sn, system_notification_user snu SET snu.public_read = 1 
WHERE sn.id = snu.system_notification_id AND snu.user_id = 173 AND 
sn.notice_type IN ('coupon_delayed','coupon_arrived')

不仔细看的话看不出问题。发布版本后走读线上错误日志就发现这个经常报错数据库事务死锁的错误。
在上面的SQL中 由于更新system_notification_user 的时候 没有用到索引 ，这样会导致在更新的过程中没有使用行锁而是将整个表都锁起来了，这样的话并发稍微高一点就会出现数据库事务死锁问题。。
如何解决？
1、寻找死锁的原因

#查看当前数据库中的锁
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread
FROM
information_schema.innodb_lock_waits w
INNER JOIN
information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN
information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

找到锁的进程之后可以 直接 kill 掉（不怕影响业务的话） 比如

kill 714583;

2、通过以上的查询结果可以知道具体的SQL 然后再项目中进行优化。具体不再阐述