mysql 行锁_MySQL InnoDB引擎的表锁及行锁

今天跟大家分享下MySQL InnoDB引擎的表锁及行锁的知识。

0 前言

InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

在现代数据库里几乎有事务机制，acid的机制应该能解决并发调度的问题了，为什么还要主动加锁呢？原因是防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决。

1 行锁和表锁

表级锁：每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

行级锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

1、MyISAM的锁

稍微提一下MyISAM，只说和InnoDB不同的。

a. MyISAM只有表锁，锁又分为读锁和写锁。　

b. 没有事务，不用考虑并发问题，世界和平~

c. 由于锁的粒度太大，所以当该表写并发量较高时，要等待的查询就会很多了。

2、InnoDB的行锁和表锁

没有特定的语法。mysql的行锁是通过索引体现的。

如果where条件中只用到索引项，则加的是行锁；否则加的是表锁。比如说主键索引，唯一索引和聚簇索引等。如果sql的where是全表扫描的，想加行锁也爱莫能助。

行锁和表锁对我们编程有什么影响，要在where中尽量只用索引项，否则就会触发表锁。另一个可能是，我们发疯了地想优化查询，但where子句中就是有非索引项，于是我们自己写连接？行锁和表锁各适合怎么样的应用，待求证？。

3、读锁和写锁

InnoDB用意向锁？实现隔离性级别，原理未名，贴张图：

回想锁协议，对什么操作加什么锁是一个问题，加锁加到什么时候有是一个问题。锁协议里常常会看到“加锁直到事务结束”的烦心字样。而在InnoDB中，select,insert,update,delete等语句执行时都会自动加解锁。select的锁一般执行完就释放了，修改操作的X锁会持有到事务结束，效率高很多。至于详细的加锁原理，见这里，搜“InnoDB存储引擎中不同SQL在不同隔离级别下锁比较”。

mysql也给用户提供了加锁的机会，只要在sql后加LOCK IN SHARE MODE 或FOR UPDATE。

共享锁(S)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE排他锁(X)：SELECT * FROM table_name WHERE ... FOR UPDATE

值得注意的是，自己加的锁没有释放锁的语句，所以锁会持有到事务结束。

mysql 还提供了LOCK TABLES，UNLOCK TABLES，用于加表锁。

4、考察加锁的情况

加了读锁还是写锁，加了行锁还是表锁，说什么时候释放，可以从原理上分析。但刚开始时我不太懂原理，于是又写了个程序。

1. public class ForUpdate1  implements Runnable{private CountDownLatch countDown;public ForUpdate1(CountDownLatch countDown){this.countDown = countDown;}@Overridepublic void run() {Connection conn=null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8","root", "123");} catch (Exception e) {e.printStackTrace();return;}try {conn.setAutoCommit(false);/*PreparedStatement ps =conn.prepareStatement("select * from LostUpdate where id =1 for update");ps.executeQuery();*/PreparedStatement ps =conn.prepareStatement("update LostUpdate set count =1 where id =1");ps.executeUpdate();Thread.sleep(10000);conn.commit();System.out.println("test 1 finish");countDown.countDown();} catch (Exception e) {try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}e.printStackTrace();}}}

1. public class ForUpdate2  implements Runnable{private CountDownLatch countDown;public ForUpdate2(CountDownLatch countDown){this.countDown = countDown;}@Overridepublic void run() {Connection conn=null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8","root", "123");} catch (Exception e) {e.printStackTrace();return;}try {Thread.sleep(2000);conn.setAutoCommit(false);PreparedStatement ps =conn.prepareStatement("select * from LostUpdate where id =1 for update");ps.executeQuery();/*PreparedStatement ps =conn.prepareStatement("update LostUpdate set count =1 where id =1");ps.executeUpdate();*/conn.commit();System.out.println("test 2 finish");countDown.countDown();} catch (Exception e) {try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}e.printStackTrace();}}}

1. public class TestForUpdate {public static void main(String[] args) throws InterruptedException {final int THREAD_COUNT=10;ExecutorService threadPool=Executors.newFixedThreadPool(THREAD_COUNT);CountDownLatch count=new CountDownLatch(2);threadPool.execute(new ForUpdate1(count));threadPool.execute(new ForUpdate2(count));threadPool.shutdown();count.await();System.out.println("finish");}}

只有两个线程，ForUpdate1先执行sql语句之后等10s，ForUpdate2先等待2s再执行sql语句。所以如果ForUpdate1持有锁，而且ForUpdate2等待，输出就应该是test 1 finish->test 2 finish->finish；否则就是test 2 finish->test 1 finish->finish。

这个程序改一下能测试上面说的理论：

repeatable read能解决脏读和不可重复读比如行锁真的只锁住一行s，x，is和ix的关系

判断加锁情况，可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

总结一下这一章，mysql提供了行锁和表锁，我们写语句时应该尽量启动行锁，以提高效率；另一方面，也说了一下读锁和写锁的原理。好了武器(原理)我们都懂了，那就看怎么优化了。

今天就分享这么多，如果觉得文章对你有帮助，请点右下角【在看】，让更多人看到该文章。

近期热门推荐?

1.迟到的年终总结，我的 2019，很拼~

2.程序员被质疑跳槽频繁不稳定，随后的一番话令HR哑口无言！

3.哎！又要过年了，程序员最怕问到什么？

4.牛X，试用了下 GitHub 上 2 万 Star 的第一抢票神器，3 秒钟抢到！

5.2018年所有精华文章汇总，错过了血亏！

关注公众号

每天进步一点点

点赞是最大的支持