MySQL锁机制_两条sql同时更新一条数据不加锁

锁是数据库系统区别于文件系统的一个关键特性。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不例外。

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。根据加锁范围，MySQL里面的锁大致可以分成三类，全局锁，表级锁，行锁。MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）;BDB存储引擎采用的是页面锁（page-level locking）,但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking）,也支持表级锁，但默认情况下是采用行级锁。

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

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

1）全局锁

     全局锁就是对整个数据库实例加锁，MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock(FTWRL)。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（增删改）、数据定义语句（DDL）和更新类事务的提交语句。

全局锁的典型使用场景是做全库逻辑备份。也就是把整库每个表都select出来存成文本。【Mysql官方自带的逻辑备份工具是mysqldump。当MySQL存储引擎支持事务并且隔离级别是可重复读，这时当mysqldump使用参数--single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于innodb MVCC的支持，这个过程中数据是可以正常更新的。single-transaction方法只适用于所有的表使用事务引擎的库，如果有的表使用了不支持事务的引擎，那么备份就只能通过FTWRL方法】对于MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，破坏了备份的一致性。这时，需要使用FTWRL命令了。

2）表级锁

    表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的思索问题。锁定颗粒度大所带来的负面影响就是出现锁定资源争用的概率也会最高，致使并发大打折扣。

    MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock,MDL）。

    （一）表锁：表锁的语法是 lock tables ... read/write 。与FTWRL类似，可以用unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。表锁分为共享锁（读锁）和 排他锁 （写锁），读锁与读锁兼容，读锁与写锁、写锁与写锁之间互斥。举个例子，如果在某个线程A中执行lock tables t1 read, t2 write;这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。

     如何加表锁：MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行更新操作（update、delete、insert等）前，会自动给涉及的表加表锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显示加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁的原因。

eg1:  lock tables orders read,order_detail read ;

       select sum(total) from orders;

       select sum(subtotal) from order_detail;

      unlock tables;   

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

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

        select a.first_name, a.last_name,b.first_name,b.last_name from actor a, actor b  where a.first_name = b.first_name and a.first_name='Lisa'  and a.last_name = 'Tom' and a.last_name <> b.last_name;

    并发插入（Concurrent Inserts）  

   LOCK TABLES ... READ LOCAL, 加了“local”选项，MyISAM表支持查询和插入操作的并发执行。MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为，其值分别可以为0、1或2。当设置为0时，不允许并发插入。当设置为1时，如果MyISAM表中没有空洞（即表中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。当设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

    MyISAM的锁调度

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

   i  通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。

   ii 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

   iii通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面3中方法都是要么更新优先、要么查询优先的方法，但还是可以用其来解决查询相对重要的应用中，读锁等待严重的问题。另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

使用表级锁定的主要是MyISAM、MEMORY、CSV等一些非事务性存储引擎。

（二）元数据锁（MDL）：MDL不需要显示使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

•    读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。

•    读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完成才能开始执行。

     MDL会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。事务中的MDL锁，在语句执行时开始申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

（三）表锁和MDL是怎么协同工作的？

        MDL和表锁是两个不同的结构。举例说明：要在MyISAM表上更新一行，那么会加MDL读锁和表的写锁；然后同时另外一个线程要更新这个表上另外一行，也要加MDL读锁和表的写锁。第二个线程的MDL读锁是能成功加上的，但是被表的写锁堵住了。从语句现象上看，就是第二个线程要等第一个线程执行完成。当多种锁同时存在时，必须得“全部不互斥”才能并行，只要有一个互斥，就得等。

3）行级锁

    行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有引擎都支持行锁，比如MyISAM引擎就不支持行锁，不支持行锁就意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这会影响到业务并发度。Innodb支持行级锁，这也是MyISAM被InnoDB替代的重要原因之一。

行锁就是针对数据表中行记录的锁。在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。此外，行级锁定也最容易发生死锁。使用行级锁定的主要是InnoDB存储引擎。

   行锁与MDL锁：DML(数据库管理语言)会产生读MDL锁，也就是update会持有MDL读锁。读和读不互斥，但对于行锁来说，两个update同时更新一条数据是互斥的。当多种锁同时存在时，必须得“全部不互斥”才能并行，只要有一个互斥，就得等。

    （一）InnoDB存储引擎实现了两种标准的行级锁：

•      共享锁（S Lock）:允许事务读一行数据。但不能修改，增加，删除数据。

•      排他锁（X Lock）：获准排他锁的事务既能读数据，又能修改数据。

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

     如果一个事务t1已经获得了行r的共享锁，那么另外的事务t2可以获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容。但若有其他的事务想获得行r的排他锁，则必须等待事务t1,t2释放行r的共享锁--这种情况称为锁不兼容。

    （二）InnoDB存储引擎支持多粒度锁定

      这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持了一种额外的锁方式，称为意向锁。意向锁为表级别的锁，innoDB支持两种意向锁：

•    意向共享锁（intention shared lock, lS）:事务有意向对标中的某些行加共享锁（S锁）

•    意向排他锁（intention exclusive lock, IX）:事务有意向对表中的某些行加排他锁（X锁）

     意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB会先获取该数据行所在数据表的对应意向锁。由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫意外的任何请求。

      表级意向锁与表锁的兼容性：

 

（三）意向锁的作用

    意向锁可以让表锁和行锁共存。

（四）一致性锁定读

       用户有时候需要显示的对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对与select的只读操作。InnoDB存储引擎对于select语句支持两种一致性的锁定读操作：

•        select  ... for update;     对读取的行记录加一个X锁，其他事务不能对已锁定的行加任何锁。

•        select  ... lock in share mode;   对读取的行记录加一个S锁，其他事务可以向被锁定的加S锁，但是如果加X锁，则会被阻塞。</