Mysql中的锁

一、Mysql中的锁

（1）从对数据操作的粒度分：

表锁(偏读)：偏向MyISAM引擎(不支持事务)，开销小，加锁快；无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低

行锁(偏写)：偏向InnoDB引擎(支持事务)，开销大，枷锁慢，存在死锁，锁定粒度小，发生锁冲突的概率最低，并发度最高

（2）从对数据操作的类型分：
读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁(排它锁)：当前写操作没有完成前，它会阻断其他写锁和读锁
查看表上加过的锁：show open tables；1就是有锁，0就是无锁。
手动加表锁：lock table 表名 read(write),表名2 read(write)

释放锁：unlock tables;

二、给一张表加上读锁

我们在session_1客户端执行：lock table mylock read;

这时如果有两个客户端session_1,session_2

session_1：a.可以查询mylock 表数据、b.不能查询其他没有锁定的表、c.插入或更新锁定的表会报错、d.释放锁

session_2：a.可以查询mylock 表数据、b.可查询或更新未锁定的表、c.插入或更新锁定的表会一直等待获得锁、d.获得锁，插入操作完成

三、给一张表加上写锁

我们在session_1客户端执行：lock table mylock write;

这时如果有两个客户端session_1,session_2

session_1：对锁定表查询、更新、插入操作都可以执行

session_2：对锁定表查询被阻塞，需要等待锁被释放

总结：读锁会阻塞写，但是不会堵塞读、而写锁会把读和写都堵塞

执行SQL：show status like 'table%'; 得到两个参数：

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，没立即获取锁值加1

Table_locks_waited：出现表级锁定争用而发生的等待次数(不能立即获取锁的次数，每等待一次，锁值加1)，此值高说明存在着较严重的表级锁争用情况。

这也是mysiam不适合做以写为主的表引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。这就是让表锁(偏读)：MyISAM引擎，不偏写的原因。

四、行锁(就是事务的隔离级别)

session_1：a.set autocommit=0;(手动提交事务)、b.update innodb set b=4 where a=2;(不commit)、c.commit

session_2：a.set autocommit=0;(手动提交事务)、b.update innodb set b=5 where a=2;被阻塞、c.接触阻塞，更新正常进行

4.1，索引失效会导致行锁变表锁：

假设a是int类型，b是vachar类型

session_1：a.set autocommit=0;(手动提交事务)、b.update innodb set b=‘6’ where a=2;(不commit)、

session_2：a.set autocommit=0;(手动提交事务)、b.update innodb set a=5 where b=‘3’;ok

session_1：a.set autocommit=0;(手动提交事务)、b.update innodb set b=‘6’ where a=2;(不commit)、c.commit

session_2：a.set autocommit=0;(手动提交事务)、b.update innodb set a=5 where b=3;被阻塞、ok

本来是行锁：session_1和session_2操作不同行互不影响的，但由于where b=3导致索引失效，使行锁变成了表锁

4.2，如何人为锁定一行数据

场景：假如因为程序bug导致数据库订单表有行数据是错的，要去人为修改，这时为了避免其他用户下单时改动改行数据，就需要人为为这行数据加锁(行锁)

这时需要session_1 commit提交事务后(自动提交事务)，session_2那边才能更改该条人为加锁的数据

4.3 查看行锁重要参数

当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统为什么会有如此多得等待(通过 Show Profile 分析)

总结：Inndb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁会更高一些，但是整体并发处理能力方面要远远优于MyISAM的表级锁定。当系统并发较高时，Innldb比MyISAM相比就会有比较明显的优势。
但是，如果我们使用不当，可能会使Innodb行级锁整体性能比MyISAM表级锁还差(索引失效行锁变表锁)。