定义

在数据库中，除了传统的计算资源（CPU、GPU、线程、磁盘IO）的竞争使用外，数据也是一共共享的受竞争资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的问题，锁冲突也是影响数据库并发访问性能的一个重要要素。从这个角度来说，锁对数据库显得尤为重要、也更加复杂。

类型

mysql数据库包含表锁、行锁；其中常用的存储引擎INNODB支持行锁和表锁，MyISAM只支持表锁。

MySQL表级锁

a. 表共享读锁（table read lock）
b. 表独占写锁（table write lock）

#加读锁
lock tables t_test read;
#加写锁
lock tabkes t_test write;
#释放锁
unlock tables;

案例分析

1. 当表存储引擎为MyISAM时，读数据时，锁表之后，多个session可同时读，A session锁表之后，A session只能读当前表，不能读写其他没加锁的表；其他session只能读当前表，不能写（写操作会被阻塞），也就是说：在执行读操作之前会自动加共享读锁，在执行写操作之前自动加独占写锁，但可以修改concurrent_insert配置使得可以进行并发插入到操作
   concurrent_insert=0，不允许并发插入
   concurrent_insert=1，若表中没有空洞（没有被删除的行）则允许A线程进行读操作，B进程在表尾进行插入操作
   concurrent_insert=2，表中有或者五空洞都允许在表尾进行插入操作
   写数据时，A session加了写锁之后，A session只能对当前表读写，其他session对当前表的读写操作会被阻塞，A session释放锁之后，其他session才能对当前表进行读写
   总结：MYISAM存储引擎下，读锁会阻塞写，不阻塞读；写锁会阻塞读写

共享读锁、独占写锁对MyISAM和InnoDB存储引擎都是一样的效果

锁表分析

查看锁表情况

#查看所有表是否锁定情况
show open tables;
#查看test_db数据库的表锁定情况
show open tables in   test_db;

表锁定分析方法
通过命令show status like 'table_locks%' ;检查table_locks_waited和table_locks_immediate状态变量，分析系统的表锁定情况。它两记录了mysql内部表级锁定的情况。
table_locks_waited：含义为表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
table_locks_immediate：出现表级锁定争用而发生等待的次数（也就是不能立即获得锁的次数，每等待一次数值加1），此值越高说明存在越严重的表级锁争用情况

---

MySQL行级锁

案例分析1——验证InnoDB存储引擎默认事务隔离级别（可重复读——repeatable read）

操作1
session A更新数据之前session B查询数据；sesson B在session A commit之前和之后查询数据

#session A
#查看SQL自动提交开关状态
show variables like 'autocommit';
#关闭SQL自动提交，关闭之后，每条SQL都需要commit之后才会更新到库
set autocommit =OFF;
update  t_test set user_name='sss' where id =2;
select * from t_test where id =2;
commit;

#session B
select * from t_test where id =2;

结论：当A已更新但未提交时，A读取到的是更新后的数据，B读到的原数据；A更新且已提交之后，B才能读取到A更新后的数据，避免了脏读的出现（读己之所写）
操作2
session A更新数据未提交，B session 更新数据；此时B session更新操作会被阻塞，只有A session释放当前行的锁之后，B session才能正常执行更新操作。

案例分析2——索引失效导致行锁变表锁（比较隐蔽的SQL错误）

session A更新数据时，数据类型转换导致索引失效，导致行锁变表锁。session B更新表的数据时会被阻塞，当session A commit更新之后，session B 才能正常提交；

#session A
#给user_age创建索引，user_age字段类型为int类型
create index idx_user_age  on t_test(user_age);
#更新数据
#关闭SQL自动提交，关闭之后，每条SQL都需要commit之后才会更新到库
set autocommit =OFF;
update t_test set user_name ='哈哈哈哈' where user_age='10';
commit;

#session B
update t_test set user_name ='6666' where id=3;

案例分析3——间隙锁的危害

间隙锁定义：当用范围条件检索数据且请求共享锁活排它锁时，InnoDB存储引擎会给符合条件的已有数据记录索引项加锁；对于存在条件范围内但不存在的记录叫做间隙——GAP，InnoDB也会对这个间隙进行加锁，这种锁机制就是间隙锁（NEXT KEY LOCK）。
危害：因为query执行过程中，通过范围查找会锁定整个范围内所有的索引键值，即使键值不存在也会锁定，从而导致在锁定的时候无法更新范围内的任何数据和无法写入任何数据，这两个操作会被阻塞。在某些场景下严重影响系统正常运行。

案例分析4——给特定行数据加锁

加锁语句模板： select 。。。。。 for update;

#session A
#语句开始
begin;
#给id=10的数据加锁
select * from t_test where id =10 for update;
#做其他业务操作
。。。。。。。。。
#commit 释放锁
commit;

#session B
#更新数据，在session A为提交之前，当前更新操作会被阻塞，直到sessionA释放锁
update t_test set user_name='ceshi' where id =10;

案例结论

innodb存储引擎由于实现了行锁，虽然锁定机制的实现方面代码的性能损耗可能高于表级锁，但是整体的并发处理能力方面要远远优于MyIsam表级锁。当系统并发量高是，InnoDB的整体性能和MyIsam相比就会有比较明显的优势。
但是InnoDB行级锁也有缺陷，当行锁使用不当时，可能会出现行锁变表锁的情况，可能会让整体性能不如MyIsam表级锁

MySQL行级锁分析

通过检查innodb_row_lock状态来分析系统上行锁的争夺情况
show status like '%innodb_row_lock%';
状态说明：

1. Innodb_row_lock_current_waits——当前正在等待锁的线程数量
2. Innodb_row_lock_time——从系统启动到现在锁定总时间长度
3. Innodb_row_lock_time_avg——每次等待锁花的平均时间
4. Innodb_row_lock_time_max——从系统启动到现在，等待锁释放所花的最长时间
5. Innodb_row_lock_waits——系统启动后到现在总共等待的次数
   比较重要的有Innodb_row_lock_time、Innodb_row_lock_time_avg、Innodb_row_lock_waits，尤其当等待次数很高，而且每次等待时间也不小是，我们就需要分析系统中为什么会有如此多的锁等待，然后根据分析结果定制调整计划

行锁分析结论

1. 尽可能让所有数据检索都通过索引来完成，避免无索引导致行锁升级为表锁（varchar不加单引号后果很严重）
2. 合理设计索引，尽量缩小加锁范围
3. 尽可能减少检索条件，避免出现间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度
5. 尽可能使用低级别事务隔离