• 十一、MySQL锁

     

    　　锁是计算机协调多个进程或线程并发访问某一资源的机制。

    　　Mysql用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这些锁统称为悲观锁(Pessimistic Lock)。

    一、MySQL锁概述

    　　不同的存储引擎支持不同的锁机制。

      • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 (MyISAM和MEMORY存储引擎)

      • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 (InnoDB存储引擎)

      • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 (BDB存储引擎)

    　　表级锁更适合于以查询为主，而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。

    二、MyISAM表锁

    　　MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

    2.1、独占写锁阻塞读例子：

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

    -- 加锁:
LOCK  TABLE  table_name  WRITE;
-- 释放锁:
UNLOCK  table_name;
    session_1 session_2
    获取表account的写锁（write）

    mysql> lock table account write;
    Query OK, 0 rows affected (0.00 sec)

    		  
    当前会话对锁定表的的查询，更新，插入操作都可以执行

    mysql> select * from account;
    +----+---------+
    | id | balance |
    +----+---------+
    | 1 | -10 |
    | 2 | 10 |
    +----+---------+
    2 rows in set (0.00 sec)

    mysql> UPDATE account SET balance= 15 WHERE id= 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    mysql> INSERT INTO `account` (`id`,`balance`) VALUES ('3','10');
    Query OK, 1 row affected (0.00 sec)

    		 其他会话对锁定表的查询被阻塞，需要等待锁被释放

    mysql> select * from account;
    等待
     释放锁

    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)

    		  等待
      session2获取锁查询返回

    mysql> select * from account;
    +----+---------+
    | id | balance |
    +----+---------+
    | 1 | 15 |
    | 2 | 10 |
    | 3 | 10 |
    +----+---------+

    2.2、共享读锁阻塞写例子:

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

    （对加锁的表都是能读，不能写，加锁的那个会话不能读其他未锁的表）

    -- 加锁
lock  table  table_name  read;
-- 释放锁
unlock  table_name
    session_1 session_2
    获取表account的READ锁定

    mysql> lock table account READ;
    Query OK, 0 rows affected (0.00 sec)

    		  
    当前session可以查询该表记录

    mysql> select * from account;
    +----+---------+
    | id | balance |
    +----+---------+
    | 1 | 15 |
    | 2 | 10 |
    | 3 | 10 |
    +----+---------+
    3 rows in set (0.01 sec)

    		 其他session也可以查询该表记录

    mysql> select * from account;
    +----+---------+
    | id | balance |
    +----+---------+
    | 1 | 15 |
    | 2 | 10 |
    | 3 | 10 |
    +----+---------+
    3 rows in set (0.01 sec)
    当前会话不能查询没有锁定的表

    mysql> select * from orders;
    ERROR 1100 (HY000): Table 'orders' was not locked with LOCK TABLES

    		 其他会话能够查询/更新没有锁定的表

    mysql> select * from orders;
    +----+-------------+
    | id | total_price |
    +----+-------------+
    | 1 | 10 |
    +----+-------------+
    1 row in set (0.00 sec)
    当前session插入/更新锁定表都会报错

    mysql> UPDATE account SET balance= 15 WHERE id= 1;
    ERROR 1099 (HY000): Table 'account' was locked with a READ lock and can't be updated
    mysql> INSERT INTO `account` (`id`,`balance`) VALUES ('3','10');
    ERROR 1099 (HY000): Table 'account' was locked with a READ lock and can't be updated

    		 其他session更新锁定表会出现等待

    mysql> UPDATE account SET balance= 15 WHERE id= 1;
    等待
    释放锁

    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)

    		 等待
      获取锁，更新操作完成

    mysql> UPDATE account SET balance= 15 WHERE id= 1;
    Query OK, 0 rows affected (0.35 sec)
    Rows matched: 1 Changed: 0 Warnings: 0

    2.3、如何加表锁

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

    　　给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作。

    2.4、查询表级锁争用情况

    　　可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

    SHOW  STATUS  LIKE  'TABLE%';

    　　如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

    2.5、并发插入（Concurrent Inserts）

    　　MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

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

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

      • 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

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

    　　在下面的例子中，session_1获得了一个表的READ LOCAL锁，该线程可以对表进行查询操作，但不能对表进行更新操作；其他的线程（session_2），虽然不能对表进行删除和更新操作，但却可以对该表进行并发插入操作，这里假设该表中间不存在空洞。

     　　首先将表改成MyISAM。

    session_1 session_2
    获取表account的READ LOCAL锁定：

    mysql> lock table account read local;
    Query OK, 0 rows affected (0.01 sec)

    		  
    当前session不能对锁定表插入/更新操作：

    mysql> UPDATE account SET balance= 15 WHERE id= 1;
    ERROR 1099 (HY000): Table 'account' was locked with a READ lock and can't be updated
    mysql> INSERT INTO `account` (`id`,`balance`) VALUES ('8','10');
    ERROR 1099 (HY000): Table 'account' was locked with a READ lock and can't be updated

    		 其他session可以查询该表记录：

    mysql> select * from account;
    +----+---------+
    | id | balance |
    +----+---------+
    | 1 | 15 |
    | 2 | 10 |
    | 3 | 10 |
    +----+---------+
    3 rows in set (0.01 sec)
    当前session不能查询没有锁定的表

    mysql> select * from orders;
    ERROR 1100 (HY000): Table 'orders' was not locked with LOCK TABLES

    		 其他session可以进行插入操作，但是更新会等待，

    mysql> INSERT INTO `account` (`id`,`balance`) VALUES ('8','10');
    Query OK, 1 row affected (0.00 sec)

    mysql> UPDATE account SET balance= 15 WHERE id= 1;

    等待
    当前session不能访问其他session插入的记录

    mysql> select * from account;
    +----+---------+
    | id | balance |
    +----+---------+
    | 1 | 15 |
    | 2 | 10 |
    | 3 | 10 |
    +----+---------+
    3 rows in set (0.00 sec)
    释放锁

    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)

    		 等待
     当前session释放后，可以获取其他session插入的记录

    mysql> select * from account;
    +----+---------+
    | id | balance |
    +----+---------+
    | 1 | 15 |
    | 2 | 10 |
    | 3 | 10 |
    | 8 | 10 |
    +----+---------+
    4 rows in set (0.00 sec)

    		 session_2获得锁更新操作完成

    mysql> UPDATE account SET balance= 15 WHERE id= 1;
    Query OK, 0 rows affected (0.35 sec)
    Rows matched: 1 Changed: 0 Warnings: 0


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

    2.6、MyISAM的锁调度

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

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

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

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

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

    三、InnoDB锁

    3.1、事务（Transaction）及其ACID属性

    • 原子性（Actomicity）

    • 一致性（Consistent）

    • 隔离性（Isolation）

    • 持久性（Durable）

    3.2、并发事务带来的问题

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

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

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

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

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

    InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

    3.3、事务隔离级别

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

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

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

    　　在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

      • 快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
      • 当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

    在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

    • 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外)
    -- 快照读
    SELECT     * FROM TABLE WHERE ?;

    • 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

    下面语句都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

    SELECT * FROM TABLE WHERE ? LOCK IN SHARE MODE;
SELECT * FROM TABLE WHERE ? FOR UPDATE;
INSERT INTO TABLE VALUES (…);
UPDATE TABLE SET ? WHERE ?;
DELETE FROM TABLE WHERE ?;

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

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

    　　

      读数据一致性 脏读 不可重复度 幻读
    读未提交
    Read uncommitted    		 最低级别，只能保证不读取物理上损坏的数据 ✔ ✔ ✔
    读已提交
    Read commited    		 语句级 ✘ ✔ ✔
    可重复读
    Repeatable read    		 事务级 ✘ ✘ ✔
    可序列化
    Serializable    		 最高级别，事务级 ✘ ✘ ✘


    3.4、获取InnoDB行锁争用情况

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

     SHOW STATUS LIKE 'InnoDB_row_lock%';

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

    3.5、InnoDB的行锁模式及加锁方法

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

    • 共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

    • 排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

    　　对于共享锁大家可能很好理解，就是多个事务只能读数据不能改数据。 对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。 另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

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

    • 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

      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方式获得排他锁。

    说明：

    • 共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预。
    • 对于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...For update 方式，获取排它锁。（用共享锁，在读了之后再写会阻塞，会导致死锁）
    • 这里说说Myisam：MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁。
    • InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

    3.6、InnoDB行锁实现方式

    　　InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，Oracle是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

    (1)、在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

    CREATE TABLE tab_no_index(id INT,NAME VARCHAR(10)) ENGINE=INNODB;
INSERT INTO tab_no_index VALUES(1,'1'),(2,'2'),(3,'3'),(4,'4');
    session_1 session_2

    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from tab_no_index where id=1;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.00 sec)

    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)


    mysql> select * from tab_no_index where id=2;
    +------+------+
    | id | name |
    +------+------+
    | 2 | 2 |
    +------+------+
    1 row in set (0.00 sec)
     

    mysql> select * from tab_no_index where id=1 for update;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.00 sec)

    		  
     

     mysql> select * from tab_no_index where id=2 for update;

     等待

    　　在上面的例子中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如下例所示：

    　　创建tab_with_index表，id字段有普通索引：

    CREATE TABLE tab_with_index(id INT,NAME VARCHAR(10)) ENGINE=INNODB;
ALTER TABLE tab_with_index ADD INDEX id(id);
INSERT INTO tab_with_index VALUES(1,'1'),(2,'2');
    session_1 session_2
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from tab_with_index where id=1;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.00 sec)

    		 mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from tab_with_index where id=2;
    +------+------+
    | id | name |
    +------+------+
    | 2 | 2 |
    +------+------+
    1 row in set (0.00 sec)

    mysql> select * from tab_with_index where id=1 for update;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.00 sec)

    		  
     

    mysql> select * from tab_with_index where id=2 for update;
    +------+------+
    | id | name |
    +------+------+
    | 2 | 2 |
    +------+------+
    1 row in set (0.01 sec)

     

    (2)、由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

    　　在下面的例子中，表tab_with_index的id字段有索引，name字段没有索引：

    ALTER TABLE tab_with_index DROP INDEX NAME
INSERT INTO tab_with_index  VALUES(1,'4')

    　　InnoDB存储引擎使用相同索引键的阻塞例子

    session_1 session_2
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)    		 mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT * FROM tab_with_index WHERE id = 1 AND NAME ='1' for update ;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.02 sec)

    		  
     

    虽然session_2访问的是session_1不同的记录，但是因为使用了相同的索引，所以需要等待锁：

    mysql> SELECT * FROM tab_with_index WHERE id = 1  AND NAME ='4' for update ;
    等待

    （3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
    　　      在下面的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引：

    ALTER TABLE tab_with_index ADD INDEX NAME(NAME)

    　　InnoDB存储引擎的表使用不同索引的阻塞例子

    session_1 session_2
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    		 mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT * FROM tab_with_index WHERE id = 1 for update ;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    | 1 | 4 |
    +------+------+
    2 rows in set (0.00 sec)

    		  
      session_2使用name的索引访问记录，因为记录没有被锁定，
    所以可以获得锁：

    mysql> SELECT * FROM tab_with_index WHERE NAME ='4' for update ;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 4 |
    +------+------+
    1 row in set (0.03 sec)
     

    由于访问的记录已经被session_1锁定，所以等待获取锁：

    mysql> SELECT * FROM tab_with_index WHERE NAME ='4' for update ;
    等待

     

    （4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。 比如，在tab_with_index表里的name字段有索引，但是name字段是varchar类型的，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

    EXPLAIN SELECT * FROM tab_with_index WHERE NAME = 1

    3.7、间隙锁（Next-Key锁）

    　　当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。 举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL

    SELECT * FROM  EMP WHERE EMPID > 100 FOR UPDATE;

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

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

    session_1 session_2

    mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set, 1 warning (0.00 sec)

    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set, 1 warning (0.00 sec)

    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    当前session对不存在的记录加for update的锁：

    mysql> SELECT * FROM tab_with_index WHERE id = 5 for update ;
    Empty set (0.00 sec)

    		  
      这时，如果其他session插入id=5的记录（注意这条记录并不存在），也会出现锁等待：
    mysql> INSERT INTO tab_with_index  VALUES(5,'4')；
    等待
    session_1执行rollback：

    mysql> rollback;
    Query OK, 0 rows affected (0.06 sec)

    		  
      由于其他session_1回退释放了Next-key锁，当前session可以获得锁并成功插入记录
    mysql> INSERT INTO tab_with_index  VALUES(5,'4')；
    Query OK, 1 row affected (34.87 sec)

     

     

    四、乐观锁、悲观锁

    4.1、悲观锁

    　　悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

    当前是

    　　使用悲观锁，我们必须关闭mysql数据库的自动提交属性，采用手动提交事务的方式，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

    　　需要注意的是，在事务中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 则不受此影响。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)。

    　　补充：MySQL select…for update的Row Lock与Table Lock

    　　使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键（或有索引的地方），MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

    4.2、乐观锁

    　　乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做（一般是回滚事务）。

    　　如何实现乐观锁呢，一般来说有以下2种方式：、

      • 使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
      • 乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

    　　总结：两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

    　　另外，高并发情况下个人认为乐观锁要好于悲观锁，因为悲观锁的机制使得各个线程等待时间过长，极其影响效率，乐观锁可以在一定程度上提高并发度。

    小结

    对于MyISAM的表锁，主要讨论了以下几点：

    • 共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。

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

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

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

    对于InnoDB表，本文主要讨论了以下几项内容

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

    • 介绍了InnoDB间隙锁（Next-key)机制，以及InnoDB使用间隙锁的原因。

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

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

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

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

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

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

    　　尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；

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

     
  • 相关阅读:
    ⑦linux pidstat
    ⑥linux mpstat
    ⑤linux 系统负载
    ④linux 进程优先级
    ③linux 进程管理
    ②linux 监控进程状态
    ①linux 进程概述于生命周期
    ④linux 自动挂载
    ③linux Gdisk
    ②linux fdisk
  • 原文地址：https://www.cnblogs.com/jdy1022/p/14025823.html
Copyright © 2020-2023  润新知