mysql中的锁

朱雀 2022-04-17 03:46 111阅读 0赞

# MyISAM表锁 #

MySQL表级锁有两种模式：**表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）**

*  对MyISAM的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
 *  对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；

MyISAM表的读操作和写操作之间，以及写操作之间是**串行的**。

## 如何加表锁 ##

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会**自动给涉及的表加写锁**。

例子：（ps实际并不需要显示加锁）

LOCK tables orders read local,order_detail read local;
    SELECT SUM(total) FROM orders;
    SELECT SUM(subtotal) FROM order_detail;
    Unlock tables;

*  上面的例子在LOCK TABLES时加了‘local’选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾插入记录
 *  在用LOCKTABLES给表显式加表锁是时，必须**同时取得所有涉及表的锁**，并且MySQL支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MySQL问题一次获得SQL语句所需要的全部锁。这也正是MyISAM表**不会出现死锁**（Deadlock Free）的原因

## 并发锁 ##

MyISAM存储引擎有一个系统变量concurrent\_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

*  当concurrent\_insert设置为0时，不允许并发插入。
 *  当concurrent\_insert设置为1时，如果MyISAM允许在一个读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
 *  当concurrent\_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录。

## MyISAM的锁调度 ##

MyISAM存储引擎的读和写锁是互斥，读操作是串行的。  
那么，一个进程请求某个MyISAM表的读锁，**同时另一个进程也请求同一表的写锁**，MySQL如何处理呢？  
答案：**写进程先获得锁**  
这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因

可以通过一些设置来调节MyISAM的调度行为

*  通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
 *  通过执行命令SET LOW\_PRIORITY\_UPDATES=1，使该连接发出的更新请求优先级降低。
 *  通过指定INSERT、UPDATE、DELETE语句的LOW\_PRIORITY属性，降低该语句的优先级。
 *  系统参数max\_write\_lock\_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL变暂时将写请求的优先级降低，给读进程一定获得锁的机会。

**建议：**  
一些需要**长时间运行的查询操作**，也会使写进程“饿死”！应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题。因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。

# InnoDB 行锁&表锁 #

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

## 查询InnoDB行锁争用情况 ##

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

## InnoDB的行锁模式及加锁方法 ##

InnoDB实现了以下两种类型的行锁。

*  共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
 *  排他锁（Ｘ）：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。

另外，为了**允许行锁和表锁共存，实现多粒度锁机制**，InnoDB还有两种内部使用的**意向锁（Intention Locks）**，这两种意向锁**都是表锁**。

*  意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
 *  意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及及数据集加排他锁（Ｘ）  
对于普通SELECT语句，InnoDB不会任何锁  
事务可以通过以下语句显示给记录集加共享锁或排锁

--共享锁（Ｓ）
    SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
    --排他锁（X）
    SELECT * FROM table_name WHERE ... FOR UPDATE

## InnoDB行锁实现方式 ##

InnoDB行锁是通过**使用索引上的索引项**来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。  
InnoDB这种行锁实现特点意味者：**只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！**

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是**使用相同的索引键，是会出现锁冲突的**。应用设计的时候要注意这一点。

## 间隙锁（Next-Key锁） ##

当我们用**范围条件**而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；**对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”**，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）

InnoDB使用间隙锁的目的，

*  一方面是为了**防止幻读**，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；
 *  另一方面，是为了**满足其恢复和复制的需要**。有关其恢复和复制对机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况。

建议：  
在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。  
因此，在实际开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，**尽量使用相等条件来访问更新数据**，避免使用范围条件。

## 什么时候使用表锁 ##

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为**事务和行锁**往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中，也可以考虑使用表级锁。

*  第一种情况是：**事务需要更新大部分或全部数据，表又比较大**，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
 *  第二种情况是：**事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚**。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
    
    当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ表。

在InnoDB下 ，使用表锁要注意以下两点。

*  （１）使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，**仅当autocommit=0、innodb\_table\_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁**，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
 *  （２）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为**UNLOCK TABLES会隐含地提交事务**；COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。

SET AUTOCOMMIT=0;
    LOCAK TABLES t1 WRITE, t2 READ, ...;
    [do something with tables t1 and here];
    COMMIT;
    UNLOCK TABLES;

## 死锁 ##

发生死锁后，InnoDB一般都能**自动检测到**，并使一个事务释放锁并退回，另一个事务获得锁，继续完成事务。

设置锁等待超时参数**innodb\_lock\_wait\_timeout**来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

--来确定最后一个死锁产生的原因和改进措施
    SHOW INNODB STATUS

几种死锁的常用方法。

*  （１）在应用中，如果不同的程序会并发存取多个表，**应尽量约定以相同的顺序为访问**表，这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可能避免。
 *  （２）在程序以批量方式处理数据的时候，如果**事先对数据排序**，保证每个线程按固定的顺序来处理记录，也可以大大降低死锁的可能。
 *  （３）在事务中，如果要更新记录，应该**直接申请足够级别的锁，即排他锁**，而不应该先申请共享锁，更新时再申请排他锁，甚至死锁。
 *  （４）在REPEATEABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…ROR UPDATE加排他锁，在没有符合该记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ-COMMITTED，就可以避免问题。
 *  （５）当隔离级别为READ-COMMITED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第１个线程提交后，第２个线程会因主键重出错，但**虽然这个线程出错了，却会获得一个排他锁**！这时如果有第３个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

参考：[https://www.cnblogs.com/chenqionghe/p/4845693.html][https_www.cnblogs.com_chenqionghe_p_4845693.html]  
[https://www.jianshu.com/p/655c2fc92077][https_www.jianshu.com_p_655c2fc92077]  
[https://lanjingling.github.io/2015/10/10/mysql-hangsuo/][https_lanjingling.github.io_2015_10_10_mysql-hangsuo]

[https_www.cnblogs.com_chenqionghe_p_4845693.html]: https://www.cnblogs.com/chenqionghe/p/4845693.html
[https_www.jianshu.com_p_655c2fc92077]: https://www.jianshu.com/p/655c2fc92077
[https_lanjingling.github.io_2015_10_10_mysql-hangsuo]: https://lanjingling.github.io/2015/10/10/mysql-hangsuo/