MySQL锁

mysql锁

表级锁：开销小，加锁块；不会出现死锁，锁定颗粒度大、发生锁冲突的概率最高，并发度最低

行级锁：开销大，加锁慢，会出现死锁，锁定颗粒度最小、发生锁冲突的概率最低，并发度最高

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

 

Mysql表级锁的锁模式(MYISAM)

表共享锁和表独占写锁

对myisam的读操作，不会阻塞其他用户对同一表请求，但会阻塞对筒仪表的写请求

对myisam的写操作，则会阻塞其他用户对同一表的读和写操作

myisam表的读操作和写操作之间，以及写操作之间是串行的

当一个线程获得对一个标的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读写操作都会等待，直到锁被释放为止。

 

如何加锁

Myisam在执行查询select前，会自动给涉及的所有表加读锁，在执行更新操作前(update delete insert等)前，会自动给设计的表加血锁，这个过程并不需要用户干预，因此用户一般不需要直接lock table命令给myisam显示式加锁。

lock tables orders read local,order_detail read local;

Select sum(total) from orders;

Select sum(subtaotal) form order_detail;

Unlock tables;

 

上面的例子在lock tables时加了local选项，其作用就是满足myisam表并发插入条件的情况下，允许其他用户在表尾插入记录

在用locak tables给表显示加锁时，必须同时取得所有涉及表的锁，并且mysql支持锁升级。也就是说，在执行lock tables后，只能访问显式加锁的这些表，不能访问未加锁表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此

 

并发锁

在一定条件下，myisam也支持查询和操作的并发进行

Myisam存储引擎有一个系统变量concurrent_insert，专门用以控制七并发插入的行为，其值分别可以为0，1或2

当concurrent_insert设置为0，不允许并发插入

当concurrent_insert设置为1，如果myisam允许在一个读表的同事，另一个进程从表尾插入记录，这也是mysql的默认设置

当concurrent_insert设置为2，无论myisam表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录

 

Myisam存储引擎的读和写锁的互斥，读操作是串行的。那么，一个进程请求某个myisam表的读锁，同事另一个进程也请求同一表的写锁，mysql如何处理呢？答案是写进程先获得锁。不仅如此，即使读进程现请求先到锁等待队列，写请求后到，写锁也会插到读请求之前。这是因为mysql认为写请求比读请求重要。这也正是myisam表不适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很那获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕。但可以通过设置一些参数来调节myisam的调度行为。

 

InnoDB锁问题

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

 

事务(Transaction)及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性

原子性(Actomicity)：事务是一个原子操作单元，其对数据的修改，要么全部执行，要目全都不执行。

一致性(consistent)：在事务完成和开始时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确性的。

隔离性(Isolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发影响的“独立环境”执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

持久性(Durable)：事务完成之后，他对于数据的修改是永久性的。

 

获取InnoDB行锁争用情况

可以通过检查InnoDB_row_lock状态变量来分析系统上的航所的争夺情况

show status like ‘innodb_row_lock%’

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

 

InnoDB的行锁模式及加锁方法

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

共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

排它锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同的数据集共享读锁和排他写锁。另外，为了允许行锁和表锁共存，实现多颗粒度锁机制，InnoDB还有两种内部使用意向锁，这两种意向锁都是表锁。

意向共享锁(IS)：事务打算给数据行共享锁，事务在给一个数据行加共享锁之前必须先取得该表的IS锁。

意向排它锁(IX)：事务打算给数据行加排它锁，事务在给一个数据行加排它锁钱必须先取得该标的IX锁。

 

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事物；反之，如果两者不兼容，该事物就要等待锁释放

 

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

 

意向锁是InnoDB自动加的，不需要用户干预。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排它锁(X)；对于普通的select语句，InnoDB不会加任何锁；事务可以通过以下语句显式的给记录加共享锁或排他锁。

共享锁(S)：select * from table_name where … lock in share mode

排它锁(X)：select * from table_name where … for update

用select…lock in share mode获得共享锁，主要用在需要数据依存关系时确认某一行记录是否存在，并确保没有人对这个记录进行update或者delete操作。但是如果当前十五也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用select … for update方式获取排它锁。

 

InnoDB行锁实现方式

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

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能造成大量的锁冲突，从而影响并发性能。

 

 

间隙锁(next-key锁)

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

假如temp表中只有101条记录，其中tempid的值分别是1,2,3….100,101

select * from temp where tempid>100 for update;

上述SQL是一个范围条件检索，InnoDB不仅会对符合条件的tempid值为101的记录加锁，也会对tempid大于101(这些记录并不存在)的‘间隙’加锁。

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

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

 

关于死锁

Myisam表锁是deadlock free的，因为myisam总是一次性获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁，但是InnoDB中，出单个SQL组成的事务外，所示逐步获取的，这就决定了InnoDB发生思索是可能的。

发生死锁后，InnoDB一般都能自动检测到，病是一个事务释放锁并退回，了另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需的锁二挂起，会占用大量计算机资源，会造成严重性能问题，甚至托垮数据库。我们通过设置核实的锁等待超时阈值，可以避免这种情况的发生。

通常来说，死锁都是应用设计的问题，通过调整业务流程，数据库对象设计，事务大小，以及访问数据库的SQL语句，绝大部分都可以避免。

1，在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序访问表，这样可以大大降低产生死锁的机会。

2，在程序一批量方式处理数据的时候，如果实现对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低死锁的可能

3，在事务中，如果要更新记录，应该直接申请足够级别的锁，即排它锁。

4，在repeatable-read隔离级别下，如果两个线程同时对相同条件记录用select … for update加排它锁，在没有符合记录下，两个线程加锁都会成功。程序发现记录尚不存在，就是图插入一条新纪录，如果两个线程都这么做，就会出现死锁，这种情况下，将隔离级别改成read committed，就可以避免问题。

5，当隔离级别为read committed时，如果两个线程都先执行select … for update，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程插入成功，另一个线程会出现锁等待情况，当第一个线程提交后，第二个线程会因为逐主键出错，虽然这个线程出错了，却会获得一个排它锁，这时如果有第3个线程又来申请排它锁，也会出现死锁，对于这种情况，可以直接做插入操作，然后再补货主键异常，或准则在遇到逐渐错误时，总是执行rollback释放其他锁。

 

尽管通过上面的设计和优化等措施，可以大大减少死锁，大门死锁很难完全避免。因此，程序设计中总是不会比过处理死锁异常是一个很好的变成习惯。

如果出现死锁，可以用show innodb status命令来确定最后一个死锁产生的原因和改进措施。

 

总结

 

对于Myisam的表锁，主要有以下几点

1，共享读锁之间是兼容的，但共享读锁和排他写锁之间，以及排它锁之间是互斥的，也就是说读和写是串行的

2，在一定条件下，myisam允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表的锁争用问题

3，MyiSAM默认的锁调度机制是写有限，这并不一定适合所有的应用，用户可以通过设置LOW_PRIPORITY_UPDATES参数，或者在insert、update、delete语句中指定low_pripority选项来调节锁的争用。

4，由于表锁的锁定颗粒度大，读写之间又是串行，因此，如果所有更新操作较多，myIsam表可能胡出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突

 

对于InnoDB表，主要有以下几点

1，InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁

2，InnoDB间隙锁机制，以及InnoDB使用间隙所的原因

3，在不用的隔离级别下，InnoDB的锁机制和一致性读策略不同

4，mysql的恢复和复制对InnoDB的锁机制和一致性度策略也有较大影响

5，锁冲突甚至死锁很难避免

 

在了解InnoDB的锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁

尽量使用较低的隔离级别

精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会

选择合理的事务大小，小事务发生锁冲突的几率也更小

给记录及显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好申请排它锁，而不是先申请共享锁，修改时在请求排它锁，这样容易产生死锁。

不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行，这样可以大大减少死锁的机会。

尽量用想用条件访问数据，这样可以避免间隙所对并发插入的影响

不要申请超过实际需要的锁级别，除非必要，查询时不要显式加锁

对于一些特定的事务，可以使用表锁来提高处理速度或者减少思索的可能。