MySQL的锁

   
锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。

1 MySql的三种锁

 

1.1 表锁

  • 开销小,加锁快
  • 不会出现死锁
  • 锁定粒度大,发生锁冲突的概率最高,并发度最低

概述

   
相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。

MySQL大致可归纳为以下3种锁:

  • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
  • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
  • 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

 

----------------------------------------------------------------------

 

1.2行锁

  • 开销大,加锁慢
  • 会出现死锁
  • 锁定粒度小,发生锁冲突的概率最低,并发度最高

MySQL表级锁的锁模式(MyISAM)

MySQL表级锁有两种模式:表共享锁(Table
Read Lock)和表独占写锁(Table Write Lock)。

  • 对MyISAM的读操作,不会阻塞其他用户对同一表请求,但会阻塞对同一表的写请求;
  • 对MyISAM的写操作,则会阻塞其他用户对同一表的读和写操作;
  • MyISAM表的读操作和写操作之间,以及写操作之间是串行的。

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

 

1.3页锁

  • 开销和加锁时间介于表锁和行锁之间
  • 会出现死锁
  • 锁定粒度介于表锁和行锁之间,并发度一般

MySQL表级锁的锁模式

   
MySQL的表锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table
Write Lock)。锁模式的兼容如下表

1.4 不同的引擎支持不同的锁机制

  • MyISAM和MEMORY支持表锁
  • BDB支持页锁,也支持表锁
  • Innodb既支持行锁,也支持表锁,默认行锁

//查询表锁争用情况
检查`table_locks_waited`和`table_locks_immediate`状态变量来分析
show status like 'table%'
//table_locks_waited 的值越高,则说明存在严重的表级锁的争用情况

MySQL中的表锁兼容性

当前锁模式/是否兼容/请求锁模式

None

读锁

写锁

读锁
写锁

   
可见,对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写请求;MyISAM表的读和写操作之间,以及写和写操作之间是串行的!(当一线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待,直到锁被释放为止。

 

 

2 表锁的锁模式

是否兼容 请求none 请求读锁 请求写锁
当前处于读锁
当前处于写锁
session_1 session_2
锁定film_text的Write锁定 lock table fime_text write
对当前seesion做 select,insert,update… 对其进行查询操作select
释放锁 unlock tables 等待
获得锁,查询返回

MyISAM表的读操作,不会阻塞其他用户对同一张表的读请求,但会阻塞对同一张表的写请求

session_1 session_2
锁定film_text的Write锁定 lock table fime_text write
对当前seesion做 select,insert,update… 对其进行查询操作select
释放锁 unlock tables 等待
获得锁,查询返回

MyISAM

  • 执行查询语句前,会自动给涉及的所有表进行表加读锁
  • 执行更新(update,delete,insert)会自动给涉及到的表加写锁

这个过程不需要用户干预,因此不需要用户直接用lock table命令

对于给MyISAM显示加锁,一般是为了在一定程度上模拟事务操作,实现对某一个时间点多个表一致性读取

如何加表锁

 
  MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此用户一般不需要直接用LOCK
TABLE命令给MyISAM表显式加锁。在本书的示例中,显式加锁基本上都是为了方便而已,并非必须如此。

   
给MyISAM表显示加锁,一般是为了一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。例如,有一个订单表orders,其中记录有订单的总金额total,同时还有一个订单明细表order_detail,其中记录有订单每一产品的金额小计subtotal,假设我们需要检查这两个表的金额合计是否相等,可能就需要执行如下两条SQL:

SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;

这时,如果不先给这两个表加锁,就可能产生错误的结果,因为第一条语句执行过程中,order_detail表可能已经发生了改变。因此,正确的方法应该是:

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)的原因

一个session使用LOCK TABLE
命令给表film_text加了读锁,这个session可以查询锁定表中的记录,但更新或访问其他表都会提示错误;同时,另外一个session可以查询表中的记录,但更新就会出现锁等待。

当使用LOCK
TABLE时,不仅需要一次锁定用到的所有表,而且,同一个表在SQL语句中出现多少次,就要通过与SQL语句中相同的别名锁多少次,否则也会出错!

2.1实例

订单表orders
记录各订单的总金额total

订单明细表order_detail
记录各订单每一产品的金额小计subtotal

假设我们需要检查这两个表的金额合计是否相符

select sum(total) from orders;
select sum(subtotal) from order_tail;

如果不给表加锁,可能出现错误,在第一条执行的过程,第二张表发生了该表,正确的方法

lock tables orders read local,order_detail read local;
select sum(total) from orders;
select sum(subtotal) from order_tail;
unlock  tables

并发锁

   
在一定条件下,MyISAM也支持查询和操作的并发进行。

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

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

可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入锁争用。例如,将concurrent_insert系统变量为2,总是允许并发插入;同时,通过定期在系统空闲时段执行OPTIONMIZE
TABLE语句来整理空间碎片,收到因删除记录而产生的中间空洞。

 

2.2 注意点

在用lock tables给表显式加表锁时,必须同时取得所有涉及的表的锁,并且MySQL支持锁升级
即在执行lock tables后,只能访问显式加锁的这些表,不能访问未加锁的表

如果加的是读锁,那么只能执行查询,不能更新

其实,在自动加锁的情况下也基本如此,MySQL问题一次获得SQL语句所需要的全部锁
这也正是MyISAM的表不会出现死锁(Deadlock Free)的原因

session_1 session_2
获得表film_textd 写锁 lock table film_text read;
可以查询select * from film_text 可以查询可以查询select * from film_text
不能查询没有锁定的表 select * from film 可以查询或更新未锁定的表 select * from film
插入或更新锁定表会提示错误 update…from film_text 更新锁定表会等待 update…from film_text
释放锁 unlock tables 等待
获得锁,更新成功

MyISAM的锁调度

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

  • 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
  • 通过执行命令SET
    LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。

虽然上面3种方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题。

另外,MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL变暂时将写请求的优先级降低,给读进程一定获得锁的机会。

   
上面已经讨论了写优先调度机制和解决办法。这里还要强调一点:一些需要长时间运行的查询操作,也会使写进程“饿死”!因此,应用中应尽量避免出现长时间运行的查询操作,不要总想用一条SELECT语句来解决问题。因为这种看似巧妙的SQL语句,往往比较复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

 

 

----------------------------------------------------------------------

2.3 tips

当使用lock tables时,不仅需要一次锁定用到的所有表,而且
同一个表在SQL语句中出现多少次,就要通过与SQL语句中别名锁多少次

lock table actor read

会提示错误

select a.first_name.....

需要对别名分别锁定

lock table actor as a read,actor as b read;

InnoDB锁问题

   
InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。

行级锁和表级锁本来就有许多不同之处,另外,事务的引入也带来了一些新问题。

 

3MyISAM的并发锁

在一定条件下,MyISAM也支持并发插入和读取

MyISAM有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2

删除操作不会重整整个表,只是把行标记为删除,在表中留下”空洞”,MyISAM倾向于在可能时填满这些空洞,插入时就会重用
这些空间,无空洞则把新行插到表尾

  • 0,不允许并发插入,所有插入对表加互斥锁
  • 1,只要表中无空洞,就允许并发插入.如果MyISAM允许在一个读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
  • 2,无论MyISAM表中有无空洞,都强制在表尾并发插入记录,若无读线程,新行插入空洞中

可以利用MyISAM的并发插入特性,来解决应用中对同表查询和插入的锁争用
例如,将concurrent_insert系统变量为2,总是允许并发插入

1.事务(Transaction)及其ACID属性

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

  • 原性性(Actomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
  • 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以操持完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
  • 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
  • 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

3.1 MyISAM的锁调度

MyISAM的读和写锁互斥,读操作串行的

一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同表的写锁,MySQL如何处理呢?
答案是写进程先获得锁。不仅如此,即使读进程先请求先到锁等待队列,写请求后到,写锁也会插到读请求之前!
这是因为MySQL认为写请求一般比读请求重要
这也正是MyISAM表不适合有大量更新和查询操作应用的原因
因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞

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

  • 启动参数low-priority-updates
    给予读请求以优先的权利
  • 执行命令SET LOW_PRIORITY_UPDATES=1
    使该连接发出的更新请求优先级降低。
  • 指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性
    降低该语句的优先级

另外,MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL便暂时将写请求的优先级降低,给读进程一定获得锁的机会


2.并发事务带来的问题

   
相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题,主要包括以下几种情况。

  • 更新丢失(Lost
    Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题
  • 脏读(Dirty
    Reads):一个事务正在对一条记录做修改,在这个事务并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”的数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
  • 不可重复读(Non-Repeatable
    Reads):一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了!这种现象叫做“不可重复读”。
  • 幻读(Phantom
    Reads):一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。

 

4 InnoDB锁问题

MyISAM最大不同

  • 支持事务
  • 采用行锁

行锁和表锁本来就有许多不同之处,另外,事务的引入也带来了一些新问题

3.事务隔离级别

在并发事务处理带来的问题中,“更新丢失”通常应该是完全避免的。但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决,因此,防止更新丢失应该是应用的责任。

“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式,基本可以分为以下两种。

一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改。

另一种是不用加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot),并用这个快照来提供一定级别(语句级或事务级)的一致性读取。从用户的角度,好像是数据库可以提供同一数据的多个版本,因此,这种技术叫做数据多版本并发控制(MultiVersion
Concurrency Control,简称MVCC或MCC),也经常称为多版本数据库。

   
数据库的事务隔离级别越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的,同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。

   
为了解决“隔离”与“并发”的矛盾,ISO/ANSI
SQL92定义了4个事务隔离级别,每个级别的隔离程度不同,允许出现的副作用也不同,应用可以根据自己业务逻辑要求,通过选择不同的隔离级别来平衡"隔离"与"并发"的矛盾

4.1 事务及其ACID

事务是由一组SQL语句组成的逻辑处理单元,事务具有ACID属性

  • 原子性(Actomicity)
    事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
  • 一致性(Consistent)
    在事务开始和完成时,数据都必须保持一致状态
    这意味着所有相关的数据规则都必须应用于事务的修改,以操持完整性
    事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的
  • 隔离性(Isolation)
    一个事务所做的修改在最终提交前对其他事务不可见
  • 持久性(Durability)
    一旦事务提交,它对于数据的修改会持久化到DB

事务4种隔离级别比较

隔离级别/读数据一致性及允许的并发副作用 读数据一致性 脏读 不可重复读 幻读
未提交读(Read uncommitted)
最低级别,只能保证不读取物理上损坏的数据
已提交度(Read committed) 语句级
可重复读(Repeatable read) 事务级
可序列化(Serializable) 最高级别,事务级

   
最后要说明的是:各具体数据库并不一定完全实现了上述4个隔离级别,例如,Oracle只提供Read
committed和Serializable两个标准级别,另外还自己定义的Read
only隔离级别:SQL Server除支持上述ISO/ANSI
SQL92定义的4个级别外,还支持一个叫做"快照"的隔离级别,但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL支持全部4个隔离级别,但在具体实现时,有一些特点,比如在一些隔离级下是采用MVCC一致性读,但某些情况又不是。

 

 

4.2 事务带来的问题

相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持可以支持更多的用户
但并发事务处理也会带来一些问题,主要包括以下几种情况

  • 更新丢失(Lost Update)
    当多个事务选择同一行,然后基于最初选定值更新该行时,由于事务隔离性,最后的更新覆盖了其他事务所做的更新
    例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题
  • 脏读(Dirty Reads)
    一个事务正在对一条记录做修改,在该事务提交前,这条记录的数据就处于不一致状态
    这时,另一个事务也来读取同一条记录,读取了这些未提交的数据
  • 不可重复读(Non-Repeatable Reads)
    一个事务在读取某些数据已经发生了改变、或某些记录已经被删除
  • 幻读(Phantom Reads)
    一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据
You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图