MySQL锁

MySQL锁机制

概念

  • 锁时计算机协调多个进程或线程并发访问某一资源的机制。
  • 在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
  • 查看哪些表被锁：show open tables;
  • 分析表锁定：show status like 'table%';

锁定粒度

  • 就是通常我们所说的锁级别。
  • MySQL数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。
  • 锁定在较小的粒度（如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需持有更多的锁。
  • 锁定在较大的粒度（如表）会降低了并发度，因为锁定整个表限制了其他事务对表中任意部分的访问。但其开销较低，因为需要维护的锁较少。

锁的分类

  • 按对数据库操作的类型分：读锁和写锁。
  • 读锁（共享锁）： 针对同一份数据，多个读操作可以同时进行而不会互相影响；
  • 写锁（排他锁）： 当前写操作没有完成前，它会阻断其他写锁和读锁。
  • 按对数据操作的粒度分：表锁和行锁。

三锁

  • 相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
  • MySQL大致可归纳为以下3种锁：
  • 表级锁： 开销小，加锁快；**不会出现死锁；**锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 表共享读锁(Table Read Lock)；
  • 表独占写锁(Table Write Lock)；
  • 行级锁： 开销大，加锁慢；**会出现死锁；**锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 页面锁： 开销和加锁时间界于表锁和行锁之间；**会出现死锁；**锁定粒度界于表锁和行锁之间，并发度一般

MySQL表级锁（MyISAM）

  • 偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

锁类型

可否兼容

读锁

写锁

读锁

是

是

否

写锁

否

否

否

  • 结合上表，所以对MyISAM表进行操作，会有以下情况：
  • 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
  • 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
  • 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。
  • MyISAM表的读操作和写操作之间，以及写操作之间是串行的。
  • 当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

加读锁

  • 在用LOCK TABLES给表显式加表锁是时，必须同时取得所有涉及表的锁，并且MySQL支持锁升级。
  • 在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；
  • 同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。
  • 一个session使用LOCK TABLE命令给表film_text加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。
  • 加读锁的session
  • 可以查询当前表的记录；
  • 不能查询其它没有锁定的表；
  • 当向当前表更新或插入锁定表都会提示错误；
  • 其它session
  • 可以查询该表的记录；
  • 可以查询或更新未锁定的表；
  • 插入或更新锁定表会进入阻塞，一直等待到获得锁。

加写锁

  • 当前session对锁定表的查询、更新、插入操作都可以执行；
  • 其它session对该表的查询、更新和插入操作都会阻塞，一直等待到获得锁。

并发锁

  • 在一定条件下，MyISAM也支持查询和操作的并发进行。
  • MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
  • 当concurrent_insert设置为0时，不允许并发插入。
  • 当concurrent_insert设置为1时，此时**MyISAM允许在一个读表的同时，另一个进程从表尾插入记录**。这也是MySQL的默认设置。
  • 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录。

MyISAM的锁调度

  • MyISAM存储引擎的读和写锁是互斥，读操作是串行的。
  • 当一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁时，写进程先获得锁。即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因。
  • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
  • 另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL变暂时将写请求的优先级降低，给读进程一定获得锁的机会。

MySQL行级锁（InnoDB）

  • InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)；二是采用了行级锁。
  • InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。

事务

  • 事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。
  • 原子性(Actomicity):
  • 一致性(Consistent):
  • 隔离性(Isolation):数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
  • 持久性(Durable):

并发事务的问题

  • 更新丢失(Lost Update):
  • 脏读(Dirty Reads):一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些脏的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做脏读。
  • 不可重复读(Non-Repeatable Reads):一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做不可重复读。即前后读取数据的内容不一致。
  • 幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。即前后读取数据的数量不一致。

隔离级别

读数据一致性

脏读

不可重复读

幻读

未提交读(Read uncommitted)

最低级别

是

是

是

已提交读(Read committed)

语句级

否

是

是

可重复读(Repeatable read)

事务级

否

否

是

可序列化(Serializable)

最高级别，事务级

否

否

否

  • Oracle只提供Read committed和Serializable两个标准级别，另外还自己定义的Read only隔离级别；
  • MySQL支持全部4个隔离级别。

InnoDB的行锁模式及加锁方法

  • InnoDB实现了以下两种类型的行锁：
  • 共享锁(s):
  • 排他锁(X):
  • 为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁(Intention Locks)，这两种意向锁都是表锁。
  • 意向共享锁(IS): 事务打算给数据行加共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
  • 意向排他锁(IX): 事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁
  • 如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两两不兼容，该事务就要等待锁释放。
  • 意向锁是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 .. IN SHARE MODE获得共享锁，主要用在需要数据依存关系时确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁。
  • 对于锁定行记录后需要进行更新操作的应用，应该使用SELECT ... FOR UPDATE方式获取排他锁。
  • InnoDB行锁是通过索引上的索引项来实现的，Oracle通过在数据中对相应数据行加锁来实现的。
  • InnoDB这种行锁实现特点意味者：**只有通过索引条件检索数据，InnoDB才会使用行级锁；否则，InnoDB将使用表锁。**即索引失效的话，行锁会变成表锁，将低性能。

间隙锁(Next-Key)

  • 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(GAP)，InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。
  • InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求；另一方面，是为了满足其恢复和复制的需要。

死锁

  • MyISAM表锁是deadlock free的，这是因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。
  • 在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发生死锁是可能的。
  • 死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并退回，另一个事务获得锁，继续完成事务。
  • 通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句，绝大部分都可以避免。

总结

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

  • 共享读锁(S)之间是兼容的，但共享读锁(S)和排他写锁(X)之间，以及排他写锁之间(X)是互斥的，也就是说读和写是串行的。
  • 在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表和插入的锁争用问题。
  • MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIPORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
  • 由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

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

  • InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
  • InnoDB间隙锁机制，以及InnoDB使用间隙锁的原因。
  • 锁冲突甚至死锁很难完全避免。
  • 在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
  • 通过设计和SQL调整等措施减少锁冲突和死锁，包括：
  • 尽量使用较低的隔离级别
  • 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。
  • 选择合理的事务大小，小事务发生锁冲突的几率也更小。
  • 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁*。
  • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
  • 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁。
  • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。