精华内容
下载资源
问答
  • 今天我们来讲讲MySQL的各种,这里存储引擎我们使用InnoDB准备工作创建 tb_innodb_lockdroptableifexiststest_innodb_lock;CREATETABLEtest_innodb_lock(aINT(11),bVARCHAR(20))ENGINEINNODBDEFAULTcharset=utf8;...

    今天我们来讲讲MySQL的各种锁,这里存储引擎我们使用InnoDB

    准备工作

    创建表 tb_innodb_lock

    droptableifexiststest_innodb_lock;

    CREATETABLEtest_innodb_lock(

    aINT(11),

    bVARCHAR(20)

    )ENGINEINNODBDEFAULTcharset=utf8;

    insertintotest_innodb_lockvalues(1,'a');

    insertintotest_innodb_lockvalues(2,'b');

    insertintotest_innodb_lockvalues(3,'c');

    insertintotest_innodb_lockvalues(4,'d');

    insertintotest_innodb_lockvalues(5,'e');

    创建索引

    createindexidx_lock_aontest_innodb_lock(a);

    createindexidx_lock_bontest_innodb_lock(b);

    MySQL 各种锁演示

    先将自动提交事务改成手动提交:set autocommit=0;

    我们启动两个会话窗口 A 和 B,模拟一个抢到锁,一个没抢到被阻塞住了。

    行锁(写&读)

    A 窗口执行

    updatetest_innodb_locksetb='a1'wherea=1;

    SELECT*fromtest_innodb_lock;

    9c382bcb4b70e466c12d167527794506.png

    我们可以看到 A 窗口可以看到更新后的结果

    B 窗口执行

    SELECT*fromtest_innodb_lock;

    b2ca6c95eaf938a17b1a70ebcec4af66.png

    我们可以看到 B 窗口不能看到更新后的结果,看到的还是老数据,这是因为 a = 1 的这行记录被 A 窗口执行的 SQL 语句抢到了锁,并且没有执行 commit 提交操作。所以窗口 B 看到的还是老数据。这就是 MySQL 隔离级别中的"读已提交"。

    窗口 A 执行 commit 操作

    COMMIT;

    窗口 B 查询

    SELECT*fromtest_innodb_lock;

    16f80e388d70f2db043b01878210b8ca.png

    这个时候我们发现窗口 B 已经读取到最新数据了

    行锁(写&写)

    窗口 A 执行更新 a = 1 的记录

    updatetest_innodb_locksetb='a2'wherea=1;

    这时候并没有 commit 提交,锁是窗口 A 持有。

    窗口 B 也执行更新 a = 1 的记录

    updatetest_innodb_locksetb='a3'wherea=1;

    9d5a53fca10ad8dc1183f4dd088c90c9.png

    可以看到,窗口 B 一直处于阻塞状态,因为窗口 A 还没有执行 commit,还持有锁。窗口 B 抢不到 a = 1 这行记录的锁,所以一直阻塞等待。

    窗口 A 执行 commit 操作

    COMMIT;

    窗口 B 的变化

    8e28a6966ceac044ec817226c8dd067e.png

    可以看到这个时候窗口 B 已经执行成功了

    表锁

    当索引失效的时候,行锁会升级成表锁,索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer,我们加上引号,就变成了 String,这个时候索引就会失效了。

    窗口 A 更新 a = 1 的记录

    updatetest_innodb_locksetb='a4'wherea=1ora=2;

    窗口 B 更新 a = 2 的记录

    updatetest_innodb_locksetb='b1'wherea=3;

    2ce4134b955867d6f3f074b7e7a5fea8.png

    这个时候发现,虽然窗口 A 和 B 更新的行不一样,但是窗口 B 还是被阻塞住了,就是因为窗口 A 的索引失效,导致行锁升级成了表锁,把整个表锁住了,索引窗口 B 被阻塞了。

    窗口 A 执行 commit 操作

    COMMIT;

    窗口 B 的变化

    49bf948925517a0d240c3a31064a1d16.png

    可以看到这个时候窗口 B 已经执行成功了

    间隙锁

    什么是间隙锁

    当我们采用范围条件查询数据时,InnoDB 会对这个范围内的数据进行加锁。比如有 id 为:1、3、5、7 的 4 条数据,我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中,但是不存在这些数据记录,这些 2、4、6 就被称为间隙。

    间隙锁的危害

    范围查找时,会把整个范围的数据全部锁定住,即便这个范围内不存在的一些数据,也会被无辜的锁定住,比如我要在 1、3、5、7 中插入 2,这个时候 1-7 都被锁定住了,根本无法插入 2。在某些场景下会对性能产生很大的影响

    间隙锁演示

    我们先把字段 a 的值修改成 1、3、5、7、9

    窗口 A 更新 a = 1~7 范围的数据

    updatetest_innodb_locksetb='b5'wherea>1anda<7;

    窗口 B 在 a = 2 的位置插入数据

    insertintotest_innodb_lockvalues(2,"b6");

    0a0cf67dd7943f9c0816c0199bbf85ee.png

    这个时候发现窗口 B 更新 a = 2 的操作一直在等待,因为 1~7 范围的数据被间隙锁,锁住了。只有等窗口 A 执行 commit,窗口 B 的 a = 2 才能更新成功

    行锁分析

    执行 SQL 分析命令

    showstatuslike'innodb_row_lock%';

    db2744ead97e54e7bab943578bd80c6c.png

    Variable_name 说明

    Innodb_row_lock_current_waits:当前正在等待锁定的数量。

    Innodb_row_lock_time:从系统启动到现在锁定的时长。

    Innodb_row_lock_time_avg:每次等待锁所花平均时间。

    Innodb_row_lock_time_max:从系统启动到现在锁等待最长的一次所花的时间。

    Innodb_row_lock_waits:系统启动后到现在总共等待锁的次数。

    结语

    大家可以根据 Variable_name 这几个参数考虑是否要进行优化,如果锁定时间,锁定次数过大,那就该考虑优化了。优化手段可以参考之前索引优化的文章。

    展开全文
  • 锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也一种供许多...概述相对其他数据库而言,MySQL机制比较简单,其最显著的特点不同...

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

    概述

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

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

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

    行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

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

    MySQL表级锁的锁模式(MyISAM)

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

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

    对MyISAM的写操作,则会阻塞其他用户对同一表的读和写操作;

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

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

    MySQL表级锁的锁模式

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

    MySQL中的表锁兼容性

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

    None

    读锁

    写锁

    读锁

    写锁

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

    如何加表锁

    MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此用户一般不需要直接用LOCK

    TABLE命令给MyISAM表显式加锁。在本书的示例中,显式加锁基本上都是为了方便而已,并非必须如此。

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

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

    要特别说明以下两点内容。

    上面的例子在LOCK TABLES时加了‘local’选项,其作用就是在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾插入记录

    在用LOCKTABLES给表显式加表锁是时,必须同时取得所有涉及表的锁,并且MySQL支持锁升级。也就是说,在执行LOCK

    TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。其实,在自动加锁的情况下也基本如此,MySQL问题一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁(Deadlock

    Free)的原因

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

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

    并发锁

    在一定条件下,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语句来整理空间碎片,收到因删除记录而产生的中间空洞。

    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语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

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

    InnoDB锁问题

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

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

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

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

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

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

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

    持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

    2.并发事务带来的问题

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

    更新丢失(Lost

    Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题

    脏读(Dirty

    Reads):一个事务正在对一条记录做修改,在这个事务并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”的数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。

    不可重复读(Non-Repeatable Reads):一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了!这种现象叫做“不可重复读”。

    幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。

    3.事务隔离级别

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

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

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

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

    Concurrency Control,简称MVCC或MCC),也经常称为多版本数据库。

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

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

    事务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一致性读,但某些情况又不是。

    获取InonoD行锁争用情况

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

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

    InnoDB的行锁模式及加锁方法

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

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

    排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。

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

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

    意向排他锁(IX):事务打算给数据行加排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

    InnoDB行锁模式兼容性列表

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

    X

    IX

    S

    IS

    X

    冲突

    冲突

    冲突

    冲突

    IX

    冲突

    兼容

    冲突

    兼容

    S

    冲突

    冲突

    兼容

    兼容

    IS

    冲突

    兼容

    兼容

    兼容

    如果一个事务请求的锁模式与当前的锁兼容,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行锁实现方式

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

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

    间隙锁(Next-Key锁)

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

    举例来说,假如emp表中只有101条记录,其empid的值分别是1,2,...,100,101,下面的SQL:

    SELECT *FROM emp WHERE empid >100FOR UPDATE

    是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。

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

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

    什么时候使用表锁

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

    第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。

    第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。

    当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表。

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

    (1)使用LOCK TALBES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层MySQL

    Server负责的,仅当autocommit=0、innodb_table_lock=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL

    Server才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁;否则,InnoDB将无法自动检测并处理这种死锁。

    (2)在用LOCAK TABLES对InnoDB锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCAK

    TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK产不能释放用LOCAK

    TABLES加的表级锁,必须用UNLOCK TABLES释放表锁,正确的方式见如下语句。

    例如,如果需要写表t1并从表t读,可以按如下做:

    关于死锁

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

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

    通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句,绝大部分都可以避免。下面就通过实例来介绍几种死锁的常用方法。

    (1)在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序为访问表,这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可能避免。

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

    (3)在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应该先申请共享锁,更新时再申请排他锁,甚至死锁。

    (4)在REPEATEABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT...ROR

    UPDATE加排他锁,在没有符合该记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ

    COMMITTED,就可以避免问题。

    (5)当隔离级别为READ

    COMMITED时,如果两个线程都先执行SELECT...FOR

    UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第3个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK释放获得的排他锁。

    尽管通过上面的设计和优化等措施,可以大减少死锁,但死锁很难完全避免。因此,在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

    如果出现死锁,可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。

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

    总结

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

    (1)共享读锁(S)之间是兼容的,但共享读锁(S)和排他写锁(X)之间,以及排他写锁之间(X)是互斥的,也就是说读和写是串行的。

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

    (3)MyISAM默认的锁调度机制是写优先,这并不一定适合所有应用,用户可以通过设置LOW_PRIPORITY_UPDATES参数,或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。

    (4)由于表锁的锁定粒度大,读写之间又是串行的,因此,如果更新操作较多,MyISAM表可能会出现严重的锁等待,可以考虑采用InnoDB表来减少锁冲突。

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

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

    (2)InnoDB间隙锁机制,以及InnoDB使用间隙锁的原因。

    (3)在不同的隔离级别下,InnoDB的锁机制和一致性读策略不同。

    (4)MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。

    (5)锁冲突甚至死锁很难完全避免。

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

    尽量使用较低的隔离级别

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

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

    给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。

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

    尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。

    不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁。

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

    展开全文
  • Mysql有很多这种机制,比如行锁,表锁等,读,写等,都在做操作之前先上;这些统称为悲观(Pessimistic Lock)。下面本篇就来带大家了解一下mysql中的,介绍表锁和行锁的区别,希望对你们有所帮助。...

    Mysql有很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁;这些锁统称为悲观锁(Pessimistic Lock)。下面本篇就来带大家了解一下mysql中的锁,介绍表锁和行锁的区别,希望对你们有所帮助。

    679af53e51279aac81fec54ac60bbc62.png

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

    mysql中表锁和行锁的区别

    行锁

    特点:锁的粒度小,发生锁冲突的概率低、处理并发的能力强;开销大、加锁慢、会出现死锁

    加锁的方式:自动加锁。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁;对于普通SELECT语句,InnoDB不会加任何锁。

    表锁

    特点:开销小、加锁快、无死锁;锁粒度大,发生锁冲突的概率高,高并发下性能低

    加锁的方式:自动加锁。查询操作(SELECT),会自动给涉及的所有表加读锁,更新操作(UPDATE、DELETE、INSERT),会自动给涉及的表加写锁。

    展开全文
  • 关于mysql行还是锁表,这个问题,今天算是有了一点头绪,mysql 中 innodb是锁行的,但是项目中居然出现了死锁,锁表的情况。为什么呢?先看一下这篇文章。做项目时由于业务逻辑的需要,必须对数据的一行或多行...

    关于mysql的锁行还是锁表,这个问题,今天算是有了一点头绪,mysql 中 innodb是锁行的,但是项目中居然出现了死锁,锁表的情况。为什么呢?先看一下这篇文章。

    做项目时由于业务逻辑的需要,必须对数据表的一行或多行加入行锁,举个最简单的例子,图书借阅系统。假设 id=1 的这本书库存为 1 ,但是有 2 个人同时来借这本书,此处的逻辑为

    问题就来了,当 2 个人同时来借的时候,有可能第一个人执行 select 语句的时候,第二个人插了进来,在第一个人没来得及更新 book 表的时候,第二个人查到数据了,其实是脏数据,因为第一个人会把 restnum 值减 1 ,因此第二个人本来应该是查到 id=1 的书 restnum 为 0 了,因此不会执行 update ,而会告诉它 id=1 的书没有库存了,可是数据库哪懂这些,数据库只负责执行一条条 SQL 语句,它才不管中间有没有其他 sql 语句插进来,它也不知道要把一个 session 的 sql 语句执行完再执行另一个 session 的。因此会导致并发的时候 restnum 最后的结果为 -1 ,显然这是不合理的,所以,才出现锁的概念, Mysql 使用 innodb 引擎可以通过索引对数据行加锁。以上借书的语句变为:

    这样,第二个人执行到 select 语句的时候就会处于等待状态直到第一个人执行 commit 。从而保证了第二个人不会读到第一个人修改前的数据。

    那这样是不是万无一失了呢,答案是否定的。看下面的例子。

    跟我一步一步来,先建立表

    其中 num 字段加了索引

    然后插入数据,运行,

    然后打开 2 个 mysql 控制台窗口,其实就是建立 2 个 session 做并发操作

    ********************************************************************

    在第一个 session 里运行:

    出现结果:

    然后在第二个 session 里运行:

    出现结果:

    好了,到这里什么问题都没有,是吧,可是接下来问题就来了,大家请看:

    回到第一个 session ,运行:

    ********************************************************************************************

    问题来了, session 竟然处于等待状态 ,可是 num=11 的行不是被第一个 session 自己锁住的么,为什么不能更新呢?好了,打这里大家也许有自己的答案,先别急,再请看一下操作。

    把 2 个 session 都关闭,然后运行:

    其实就是把 num=11 和 22 的记录各删去 3 行,

    然后重复 “***********************” 之间的操作

    竟然发现,运行 update book set name='abc' where num=11; 后,有结果出现了,说明没有被锁住,

    这是为什么呢,难道 2 行数据和 5 行数据,对 MySQL 来说,会产生锁行和锁表两种情况吗。经过跟网友讨论和翻阅资料,仔细分析后发现:

    在以上实验数据作为测试数据的情况下,由于 num 字段重复率太高,只有 2 个值,分别是 11 和 12. 而数据量相对于这两个值来说却是比较大的,是 10 条, 5 倍的关系。

    那么 mysql 在解释 sql 的时候,会忽略索引,因为它的优化器发现:即使使用了索引,还是要做全表扫描,故而放弃了索引,也就没有使用行锁,却使用了表锁。简单的讲,就是 MYSQL 无视了你的索引,它觉得与其行锁,还不如直接表锁,毕竟它觉得表锁所花的代价比行锁来的小。以上问题即便你使用了 force index 强制索引,结果还是一样,永远都是表锁。

    所以 mysql 的行锁用起来并不是那么随心所欲的,必须要考虑索引。再看下面的例子。

    大部分会认为结果一样没什么区别,其实差别大了,区别就是第一条 sql 语句会产生表锁,而第二个 sql 语句是行锁,为什么呢?因为第一个 sql 语句用了子查询外围查询故而没使用索引,导致表锁。

    好了,回到借书的例子,由于 id 是唯一的,所以没什么问题,但是如果有些表出现了索引有重复值,并且 mysql 会强制使用表锁的情况,那怎么办呢?一般来说只有重新设计表结构和用新的 SQL 语句实现业务逻辑,但是其实上面借书的例子还有一种办法。请看下面代码:

    上面是个小技巧,通过把数据库模式临时设置为严格模式,当 restnum 被更新为 -1 的时候,由于 restnum 是 unsigned 类型的,因此 update 会执行失败,无论第二个 session 做了什么数据库操作,都会被回滚,从而确保了数据的正确性,这个目的只是为了防止并发的时候极小概率出现的 2 个 session 的 sql 语句嵌套执行导致数据脏读。当然最好的办法还是修改表结构和 sql 语句,让 MYSQL 通过索引来加行锁, MySQL 测试版本为 5.0.75-log 和 5.1.36-community

    所以,可以总结出。Mysql innodb虽是锁行的,但是如果没有索引,或者索引如上,那就要锁表了。

    展开全文
  • 什么是MySQL锁表?为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。MySQL有三种的级别:页级、级、行级。MyISAM和MEMORY存储引擎采用的是表(table-level locking);BDB...
  • 什么是mysql锁表

    2020-04-12 20:32:48
    首先我们了解一下数据库查询机制,首先我们用工具进行连接查询时,会创建一个connection,此时数据库会将查询语句解析成一棵“树”,各个引擎底层的结构不一样,mysql的话在innodb用的b-tree,俗称b+树,那么再到...
  • MySQL中的和事务隔离级别是什么发布时间:2020-06-23 12:13:23来源:亿速云阅读:152作者:LeahMySQL中的和事务隔离级别是什么?可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家...
  • (0)不为什么,数据库的事务机制就是这样,insert时全表锁,因为要生成主键字段、索引等等,update行级。同时对一张进行读写操作,会产生‘脏’数据,导致读、写两端的数据不一致。当然了,这要在绝对意义上...
  • 什么是MySQL锁表?为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。MySQL有三种的级别:页级、级、行级。MyISAM和MEMORY存储引擎采用的是表(table-level locking);BDB...
  • 什么是锁锁是一种用于保证在并发场景下每个事务仍能以一致性的方式读取和修改数据的方式,当一个事务对某一条数据上之后,其他事务就不能修改或者只能阻塞等待的释放,所以的粒度大小一定程度上可以影响到访问...
  • 以下的文章主要MySQL锁表的... MySQL锁表 为了能有快速的MySQL除了 InnoDB 和 BDB 这两种以下的文章主要MySQL锁表的概念的介绍,以及介绍MySQL表锁在什么设想的情况下就不利了,如果你对MySQL表锁的相...
  • 关于mysql行还是锁表,这个问题,今天算是有了一点头绪,mysql 中 innodb是锁行的,但是项目中居然出现了死锁,锁表的情况。为什么呢?先看一下这篇文章。做项目时由于业务逻辑的需要,必须对数据的一行或多行...
  • 什么是MySQL锁表?为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。MySQL有三种的级别:页级、级、行级。MyISAM和MEMORY存储引擎采用的是表(table-level locking);BDB...
  • 延伸阅读:五分钟了解Mysql的行级一分钟深入Mysql的意向锁mysql锁相关讲解及其应用——《深究mysql锁》了解前,一定要先看这篇,了解什么是MVCC,如果我们学习,没有MVCC的知识,理解起来会总觉得不明朗。...
  • 什么是行级、页级?(1)行级:引擎innoDB,对一行记录进行加锁。(2):引擎 MyISAM,对整个进行加锁。(3)页级:引擎 BDB,对查询出来的一页数据进行加锁。复制代码==innoDB支持行级...
  • 什么是MySQL锁表?为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。MySQL有三种的级别:页级、级、行级。MyISAM和MEMORY存储引擎采用的是表(table-level locking);BDB...
  • 其实对于mysql的select是否会锁表,这个完全取决于采用的是什么存储引擎。这里我就拿大家最熟悉的存储引擎INNODB和MYISAM来说明这个问题。对于myisam的select是会锁定的,会导致其他操作挂起,处于等待状...
  • 从应用的角度来看数据库可分为悲观、乐观从数据库(InnoDB)的角度看,数据库可以分为行级1.1 什么是行级InnoDB 存储引擎默认情况下,使用行级。行级锁是 MySQL 中锁定粒度最细的一种,它住...
  • mysql锁什么用?2020-06-30 22:02:29mysql锁的用处:1、共享时,其他用户能读,不能改变量表数据,只对本人产生影响;2、排它时,其他用户既不能读,也不能改数据;3、保证数据的原子性,完整性,一致性。...
  • 说在前面:基于innodb讨论1.insert时全表锁,update行级(非绝对-成功使用索引时行,否则锁表)2.是否使用行锁分析3.行表锁总结:1) 开销小,吞吐量会减小2)行级 消耗大,吞吐量也大3)MyISAM只支持级...
  • MySql锁表

    2019-09-26 17:13:04
    部分一:mysql select是否会锁表 转载自:https://blog.csdn.net/wscrf/article/details/78749744 ...其实对于mysql的select是否会锁表,这个完全取决于采用的是什么存储引擎。 这里我就拿大家最熟悉的存...
  • mysql数据库有三种的级别,分别:页级和行级粒度(推荐教程:mysql教程)粒度就是我们通常所说的级别。数据库引擎具有多粒度锁定,允许一个事务锁定不同类型的资源。 为了尽量减少锁定的开销...
  • mysql什么时候锁表

    万次阅读 2018-04-27 22:11:18
    (0)不为什么,数据库的事务机制就是这样,insert时全表锁,因为要生成主键字段、索引等等,update行级。同时对一张进行读写操作,会产生‘脏’数据,导致读、写两端的数据不一致。当然了,这要在绝对意义...
  • 其实对于mysql的select是否会锁表,这个完全取决于采用的是什么存储引擎。这里我就拿大家最熟悉的存储引擎INNODB和MYISAM来说明这个问题。对于myisam的select是会锁定的,会导致其他操作挂起,处于等待...
  • 概述锁是MySQL中锁定粒度最大的一种,表示对当前操作的整张加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持级锁定。级锁定分为共享读(共享)与独占写...
  • mysql 锁表还是

    2014-07-24 17:15:27
    关于mysql行还是锁表,这个问题,今天算是有了一点头绪,mysql 中 innodb是锁行的,但是项目中居然出现了死锁,锁表的情况。为什么呢?先看一下这篇文章。   做项目时由于业务逻辑的需要,必须对数据的一行...
  • 概述锁是MySQL中锁定粒度最大的一种,表示对当前操作的整张加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持级锁定。级锁定分为共享读(共享)与独占写...
  • 某日,生产环境上的用户突然无故锁表,原以为只是偶发的bug。所以第一时间想到的解决方案简单粗暴:重启数据库(service mysqld restart)。问题得以解决。10min后,该再次锁表。终于意识到问题并没有那么简单。...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,276
精华内容 510
关键字:

mysql表是什么锁

mysql 订阅