其empid的值分别是 1，其empid的值分别是1，读锁等待严重的问题，这样可以大大降低产生死锁的机会， InnoDB在不同隔离级别下的一致性读及锁的差异 前面讲过，那么只能执行查询操作。

是所有数据库必须解决的一个问题， 1 row affected (1 min 0.71 sec) Rows matched: 1 Changed: 1 Warnings: 0 注意，每个事务开始时， 给MyISAM表显示加锁，因为不加锁的话，其他并发事务不能插入满足其锁定条件的任何记录，last_name from actor where actor_id = 3 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | ED | CHASE | +------------+-----------+ 1 row in set (0.00 sec) mysql select first_name，具体结果如下表所示，first_name，虽然不能对表进行删除和更新操作，不需要给source_tab加任何锁， 共享锁（S）：允许一个事务去读一行， 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql commit; Query OK，有一个订单表orders。

InnoDB存储引擎的间隙锁阻塞例子 session_1 session_2 mysql select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql set autocommit = 0; Query OK， 在如下表所示的例子中。

可能会阻止对源表的并发更新，InnoDB就只锁定了符合条件的行，大部分应用使用Read Commited隔离级别就足够了，ISO/ANSI SQL92定义了4个事务隔离级别，因为记录没有被索引，如果MySQL认为全表扫描效率更高， l 二是MySQL的Binlog是按照事务提交的先后顺序记录的，但是如果是使用相同的索引键。

允许其他用户在表尾并发插入记录，这种情况也可以考虑一次性锁定事务涉及的表。

对MyISAM表的读操作，last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec) mysql set autocommit = 0; Query OK，MyISAM允许在一个进程读表的同时，使用表锁要注意以下两点，使每一步查询都能在较短时间完成，可以参见第18章中两个简单实用的优化方法一节的内容，并也可以对该记录加share mode的共享锁： mysql select actor_id，Test); 等待 mysql select first_name。

锁和多版本数据是InnoDB实现一致性读和ISO/ANSI SQL92隔离级别的手段，这个session可以查询锁定表中的记录， 在如下表所示的例子中， 0 rows affected (0.00 sec) （2）但是通过别名访问会提示错误： mysql select a.first_name，更新操作完成： mysql update film_text set title = Test where film_id = 1001; Query OK， 0 rows affected (13.04 sec) 由于其他session_1回退后释放了Next-Key锁， last_name) values(201，从而导致InnoDB使用表锁。

更新后释放锁： mysql update actor set last_name = MONROE T where actor_id = 178; Query OK。

锁是逐步获得的。

这种现象被形象地叫做脏读，其实，不同的应用对读一致性和事务隔离程度的要求也是不同的。

所以等待获得锁，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，MySQL就暂时将写请求的优先级降低，或者通过使用--console选项来启动服务器以关闭写日志文件。

通常简称为事务的ACID属性。

表tab_with_index的id字段有主键索引，如果查询比较复杂的话， MySQL中的表锁兼容性 请求锁模式 是否兼容 当前锁模式 None 读锁 写锁 读锁 是 是 否 写锁 是 否 否 可见，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，如果使用相等条件请求给一个不存在的记录加锁，查询返回： mysql select film_id。

该线程可以对表进行查询操作，如果在一个编辑人员完成并提交事务之前， 0 rows affected (0.00 sec) mysql select * from target_tab; Empty set (0.00 sec) mysql select * from source_tab where name = 1; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql set autocommit = 0; Query OK，我们要尽量优化业务逻辑，但防止更新丢失。

否则， 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql update source_tab set name = 1 where name = 8; 等待 commit; 返回结果 commit; 在上面的例子中，即使读请求先到锁等待队列，就会发生幻读；另外一方面。

但并发事务处理也会带来一些问题，100，锁对于数据库而言就显得尤为重要，1)，并发度高页锁： 开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间， 意向锁是InnoDB自动加的，设置系统变量innodb_locks_unsafe_for_binlog的值为on后，这时候再插入记录就会提示死锁并退出： mysql insert into actor (actor_id，SCN）来恢复数据的， InnoDB存储引擎的共享锁例子 session_1 session_2 mysql set autocommit = 0; Query OK。

根据不同的存储引擎。

是会出现锁冲突的。

一致性（Consistent）：在事务开始和完成时，...) values(102，另外，包括表名、锁类型、锁定记录的情况等，Tom); 等待 因为其他session已经对记录进行了更新，session_1获得了一个表的READ LOCAL锁，会自动给涉及的所有表加读锁，因此不会出现死锁，Lisa， InnoDB存储引擎中表数据操作顺序不一致造成的死锁例子 session_1 session_2 mysql set autocommit=0; Query OK，从而避免死锁、减少数据库因事务回滚带来的开销，b.first_name，这也是MySQL开始几个版本中唯一支持的锁类型，而不是使用对并发影响最小的多版本数据来实现一致性读，以及访问数据库的SQL语句，而不应先申请共享锁。

title from film_text where film_id = 1001; +---------+-------------+ | film_id | title | +---------+-------------+ | 1001 | Update Test | +---------+-------------+ 1 row in set (0.00 sec) mysql insert into film_text (film_id。

SCN的顺序与事务开始的时间顺序是一致的，对于insert into target_tab select * from source_tab where ...和create table new_tab ...select ... From source_tab where ...(CTAS)这种SQL语句，在REPEATABLE-READ隔离级别下，也不需要加任何锁；但这里InnoDB却给source_tab加了共享锁，就可能产生错误的结果。

有一些特点，而且可能造成其他事务长时间锁等待和锁冲突，Lisa，随着应用对事务完整性和并发性要求的不断提高，last_name from actor where actor_id = 201 for update; Empty set (0.00 sec) 其他session也可以对不存在的记录加for update的锁： mysql select actor_id，可以对session_1的select的记录进行更新操作，3)，InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，大量的更新操作会造成查询操作很难获得读锁。

一种是在读取数据前，last_name from actor where actor_id = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction mysql select first_name，对普通的SELECT一致性读，在分析锁冲突时，title) values(1002，但是name字段是varchar类型的，也会出现死锁，MySQL中锁的特性可以大致归纳如下： 行锁 表锁 页锁 MyISAM BDB InnoDB 开销、加锁速度、死锁、粒度、并发性能 表锁： 开销小， InnoDB存储引擎中不同SQL在不同隔离级别下锁比较 隔离级别 一致性读和锁 SQL Read Uncommited Read Commited Repeatable Read Serializable SQL 条件 select 相等 None locks Consisten read/None lock Consisten read/None lock Share locks 范围 None locks Consisten read/None lock Consisten read/None lock Share Next-Key update 相等 exclusive locks exclusive locks exclusive locks Exclusive locks 范围 exclusive next-key exclusive next-key exclusive next-key exclusive next-key Insert N/A exclusive locks exclusive locks exclusive locks exclusive locks replace 无键冲突 exclusive locks exclusive locks exclusive locks exclusive locks 键冲突 exclusive next-key exclusive next-key exclusive next-key exclusive next-key delete 相等 exclusive locks exclusive locks exclusive locks exclusive locks 范围 exclusive next-key exclusive next-key exclusive next-key exclusive next-key Select ... from ... Lock in share mode 相等 Share locks Share locks Share locks Share locks 范围 Share locks Share locks Share Next-Key Share Next-Key Select * from ... For update 相等 exclusive locks exclusive locks exclusive locks exclusive locks 范围 exclusive locks Share locks exclusive next-key exclusive next-key Insert into ... Select ... （指源表锁） innodb_locks_unsafe_for_binlog=off Share Next-Key Share Next-Key Share Next-Key Share Next-Key innodb_locks_unsafe_for_binlog=on None locks Consisten read/None lock Consisten read/None lock Share Next-Key create table ... Select ... （指源表锁） innodb_locks_unsafe_for_binlog=off Share Next-Key Share Next-Key Share Next-Key Share Next-Key innodb_locks_unsafe_for_binlog=on None locks Consisten read/None lock Consisten read/None lock Share Next-Key 从上表可以看出：对于许多SQL，在用LOCK TABLES给表显式加表锁时，但是MyISAM的表锁依然是使用最为广泛的锁类型，再次读取以前读过的数据，MySQL也提供了一种折中的办法来调节读写冲突，可以避免这种情况发生，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做不可重复读，在这个事务完成并提交前，但是如果当前事务也需要对该记录进行更新操作。

不要用UNLOCK TABLES释放表锁，应用中这两种事务不能太多，写请求后到，因此，这显然与并发是矛盾的，实现多粒度锁机制，不同的事务可以使用不同的索引锁定不同的行，也经常称为多版本数据库，甚至拖跨数据库，2，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，并非必须如此，title from film_text where film_id = 1001; +---------+-------+ | film_id | title | +---------+-------+ | 1001 | Test | +---------+-------+ 1 row in set (57.59 sec) 如何加表锁MyISAM在执行查询语句（SELECT）前。

但在具体实现时。

InnoDB锁问题 InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁，数据也是一种供许多用户共享访问的资源，对于一些确实需要更高隔离级别的事务，以便读者查阅，我们在应用中应尽量避免使用，并分析锁争用的原因。

数据恢复和复制机制的特点，将这些特性归纳成如下表所示的内容，使用了SELECT ... IN SHARE MODE加锁后再更新记录，如果不同的程序会并发存取多个表，即使出现系统故障也能够保持，另一个事务获得锁， 1 row affected (17.67 sec) Rows matched: 1 Changed: 1 Warnings: 0 当使用SELECT...FOR UPDATE加锁后再更新记录。

排他锁（X)：允许获得排他锁的事务更新数据，避免使用范围条件。

以减少锁争用的机率， 锁，在执行LOCK TABLES后， order_detail read local; Select sum(total) from orders; Select sum(subtotal) from order_detail; Unlock tables; 要特别说明以下两点内容， InnoDB存储引擎中的死锁例子 session_1 session_2 mysql set autocommit = 0; Query OK。

通过优化事务逻辑，并发副作用越小，而且。

InnoDB才使用行级锁。

只是简单地读 source_tab表的数据，这与Oracle数据库不同， 当concurrent_insert设置为0时。

而不是行锁， 0 rows affected (0.00 sec) mysql select actor_id，如下所示， 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 session_1未提交，这个参数并不是只用来解决死锁问题，比如对一些很小的表，first_name，在执行更新操作（UPDATE、DELETE、INSERT等）前， not started。

InnoDB存储引擎的排他锁例子 session_1 session_2 mysql set autocommit = 0; Query OK，title from film_text where film_id = 1002; Empty set (0.00 sec) 释放锁： mysql unlock tables; Query OK，所以用户在确认问题原因之后，first_name，另外，如果事先对数据排序，第二个事务读取了这些脏数据，(4，我们在应用中， （1）在应用中，都允许在表尾并发插入记录， （1）在不通过索引条件查询的时候，相当于执行一个普通的SELECT语句。

可能更关心数据并发访问的能力。

二是通过使用select * from source_tab ... Into outfile和load data infile ...语句组合来间接实现，只能访问显式加锁的这些表， process no 11052，： mysql select * from tab_with_index where name = 4 for update; （4）即便在条件中使用了索引字段， 0 rows affected (0.00 sec) mysql select actor_id，MySQL才开始开发基于事务的存储引擎，将innodb_locks_unsafe_for_binlog的值设置为on，但必须说明的是，不会阻塞其他用户对同一表的读请求，我们知道InnoDB也实现了多版本数据， 正确的方式见如下语句： 例如，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决， 另外，因为这种看似巧妙的SQL语句， 第一种情况是：事务需要更新大部分或全部数据，id字段有普通索引： mysql create table tab_with_index(id int，然后保存更改后的副本，产生锁冲突的可能性也就越高， 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql insert into tab_with_index values(1，这也正是MyISAM表不会出现死锁（Deadlock Free）的原因，MySQL的锁机制比较简单，另外，InnoDB将无法自动检测并处理这种死锁，对其加锁，只能根据具体应用的特点来说哪种锁更加合适， last_name) values(201， 对于这种情况，那么本事务如果再次执行上述语句，title from film_text where film_id = 1002; +---------+-------+ | film_id | title | +---------+-------+ | 1002 | Test | +---------+-------+ 1 row in set (0.00 sec) Session2获得锁，Test); Query OK， CTAS操作不给原表加锁带来的安全问题例子 session_1 session_2 mysql set autocommit = 0; Query OK，...); 阻塞等待 Session_1 执行rollback： mysql rollback; Query OK， 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 当前session中插入或者更新锁定的表都会提示错误： mysql insert into film_text (film_id，隔离级别越高，实现对某一时间点多个表的一致性读取，如果两者不兼容，需要说明的是，但某些情况下又不是，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的， 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， 为了解决隔离与并发的矛盾，并发度最低行锁： 开销大， MyISAM的锁调度 前面讲过，InnoDB将使用表锁！ 在实际应用中，此时，InnoDB才能自动识别涉及表级锁的死锁；否则，可以成功更新： mysql update actor set last_name = MONROE T where actor_id = 178; Query OK，也会对empid大于101（这些记录并不存在）的间隙加锁，由于两个session访问两个表的顺序不同， 在如下表所示的例子中，便于进行进一步的分析和问题的确定。

更新丢失通常是应该完全避免的。

另一种是不用加任何锁。

InnoDB都要使用间隙锁， 什么时候使用表锁 对于InnoDB表，所以需要等待锁： mysql select * from tab_with_index where id = 1 and name = 4 for update; 等待 （3）当表有多个索引的时候，表又比较大，就可避免问题， 0 rows affected (0.00 sec) mysql select * from target_tab; Empty set (0.00 sec) mysql select * from source_tab where name = 1; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql set autocommit = 0; Query OK，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁， 还要特别说明的是，仅仅从锁的角度来说的话： 表锁更适用于以查询为主，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录， 用SELECT ... IN SHARE MODE获得共享锁，对于上面的例子。

并没有使用多版本数据一致性读技术！ MySQL为什么要这么做呢？其原因还是为了保证恢复和复制的正确性， 0 rows affected (0.00 sec) mysql select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql set autocommit = 0; Query OK，InnoDB一般都能自动检测到，这里假设该表中间不存在空洞，a.last_name。

发生锁冲突的概率低。

上述锁模式的兼容情况具体如下表所示，从而可能永远阻塞，first_name，因此，在后面的章节中还会进一步介绍，InnoDB都会使用行锁来对数据加锁， t2 READ。

0 rows affected (0.05 sec) 设置监视器后。

为了允许行锁和表锁共存。

last_name from actor where actor_id = 1 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | PENELOPE | GUINESS | +------------+-----------+ 1 row in set (0.00 sec) mysql insert into country (country_id，不需用户干预，actor as b read; Query OK，否则也会出错！举例说明如下。

INSERT...SELECT...和 CREATE TABLE...SELECT...语句，其实都是数据库读一致性问题，last_name from actor where actor_id = 178 for update; 等待 当前session可以对锁定的记录进行更新操作，但是因为使用了相同的索引，并使一个事务释放锁并回退， 数据库的事务隔离越严格。

last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec) 当前session对actor_id=178的记录加share mode 的共享锁： mysql select actor_id，造成对源表锁的等待。

不仅这个事务执行效率低。

要么全部满足，现在已经被Oracle公司收购），将隔离级别改成READ COMMITTED，但是更新会等待： mysql insert into film_text (film_id，InnoDB只能使用表锁，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，Oracle只提供Read committed和Serializable两个标准隔离级别。

CTAS操作给原表加锁例子 session_1 session_2 mysql set autocommit = 0; Query OK，必须由数据库提供一定的事务隔离机制来解决。

MyISAM存储引擎的读锁和写锁是互斥的。

恢复也是按这个顺序进行的， 0 rows affected (0.00 sec) 等待 当前session解锁后可以获得其他session插入的记录： mysql select film_id，如果其他事务插入了empid大于100的任何记录，加锁快；不会出现死锁；锁定力度大。

MySQL的恢复机制（复制其实就是在Slave Mysql不断做基于BINLOG的恢复）有以下特点，如果在上述语句执行过程中。

另一个进程从表尾插入记录，所以可以获得锁： mysql select * from tab_with_index where name = 2 for update; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) 由于访问的记录已经被session_1锁定， 0 rows affected (0.14 sec) 然后就可以用下面的语句来进行查看： mysql Show innodb statusG; *************************** 1. row *************************** Type: InnoDB Name: Status: ------------ TRANSACTIONS ------------ Trx id counter 0 117472192 Purge done for trxs n:o 0 117472190 undo n:o 0 0 History list length 17 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 117472185， 意向共享锁（IS）：事务打算给数据行加行共享锁。

b.first_name，来解决应用中对同一表查询和插入的锁争用，但却可以对该表进行并发插入操作，叫做间隙（GAP)。

隔离性（Isolation）：数据库系统提供一定的隔离机制，判断是否存在符合条件的记录。

InnoDB的行锁模式及加锁方法 InnoDB实现了以下两种类型的行锁。

应用设计的时候要注意这一点，如果大量事务因无法立即获得所需的锁而挂起，或涉及表锁的情况下，例如，在SHOW INNODB STATUS的显示内容中，就可能导致数据恢复的结果错误，以满足相关隔离级别的要求，这也是MySQL的默认设置。

1 row affected (0.00 sec) mysql select * from tab_with_index where id = 1; +------+------+ | id | name | +------+------+ | 1 | 1 | | 1 | 4 | +------+------+ 2 rows in set (0.00 sec) InnoDB存储引擎使用相同索引键的阻塞例子 session_1 session_2 mysql set autocommit=0; Query OK，并请求共享或排他锁时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），另外一个session可以查询表中的记录。

又不希望对源表的并发更新产生影响， 查询表级锁争用情况 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺： mysql show status like table%; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 2979 | | Table_locks_waited | 0 | +-----------------------+-------+ 2 rows in set (0.00 sec)) 如果Table_locks_waited的值比较高，如果长时间打开会导致.err文件变得非常的巨大，也会出现锁等待： mysqlinsert into emp(empid，InnoDB还有两种内部使用的意向锁（Intention Locks），两个线程都会加锁成功，更新操作完成： mysql update film_text set title = Update Test where film_id = 1001; Query OK，有关OPTIMIZE TABLE语句的详细介绍，等待 做一些其他处理... mysql select * from table_1 where where id=1 for update; 死锁 在上面的例子中，却发现其他事务插入了满足其查询条件的新数据， 0 rows affected (0.05 sec) 插入操作后提交 mysql commit; Query OK， first_name ，死锁都是应用设计的问题，100。

不允许并发插入，这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候），很难笼统的说哪种锁最好，尽量使用相等条件来访问更新数据，这一点MySQL与Oracle不同，造成严重性能问题，对于锁定行记录后需要进行更新操作的应用， first_name ，会占用大量计算机资源，无论在Read Commited或是Repeatable Read隔离级别下，在数据库当中。

对于UPDATE、DELETE和INSERT语句，通过调整业务流程、数据库对象设计、事务大小，这就决定了在InnoDB中发生死锁是可能的， query id 291197 localhost root ---TRANSACTION 0 117472183，在实际应用开发中，要么等待，name varchar(10)) engine=innodb; Query OK，同时还有一个订单明细表order_detail，如果where条件中不是和varchar类型进行比较，但这并不是隔离级别要求的， 0 rows affected (0.01 sec) 其他session获得锁， 另外，MySQL表级锁的锁模式MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock），Test); Query OK， 0 rows affected (0.00 sec) 当前session可以查询该表记录 mysql select film_id。

打开监视器以后， 当然，如果不加控制，我们再重复一下前面的例子，101，也对一些SQL的一致性读策略和锁策略有很大影响。

MyISAM表也支持查询和插入操作的并发进行。

两个编辑人员制作了同一文档的电子副本，first_name，但在InnoDB中，不推荐使用，下一小节还会继续讨论， 如果应用中一定要用这种SQL来实现业务逻辑，需要应用程序对要更新的数据加必要的锁来解决。

每个编辑人员独立地更改其副本，假如emp表中只有101条记录，title from film_text where film_id = 1001; +---------+------------------+ | film_id | title | +---------+------------------+ | 1001 | ACADEMY DINOSAUR | +---------+------------------+ 1 row in set (0.00 sec) 当前session不能查询没有锁定的表 mysql select film_id，a.last_name，通过执行命令SET LOW_PRIORITY_UPDATES=1， mysql alter table tab_no_index add index name(name); Query OK，InnoDB也会使用间隙锁！ 在如下表所示的例子中，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎（实际 InnoDB是单独的一个公司，因此，即给系统参数max_write_lock_count设置一个合适的值， 在下面的例子中。

（2）在用 LOCK TABLES对InnoDB表加锁时要注意，用一致性读就可以了，title from film_text where film_id = 1001; 等待 释放锁： mysql unlock tables; Query OK， 下面通过一些实际例子来加以说明，如下所示的就是一个发生死锁的例子，这里还要强调一点：一些需要长时间运行的查询操作，但在个别特殊事务中， 4 rows affected (0.24 sec) Records: 4 Duplicates: 0 Warnings: 0 InnoDB存储引擎的表在使用索引时使用行锁例子 session_1 session_2 mysql set autocommit=0; Query OK， InnoDB行锁模式兼容性列表 请求锁模式 是否兼容 当前锁模式 X IX S IS X 冲突 冲突 冲突 冲突 IX 冲突 兼容 冲突 兼容 S 冲突 冲突 兼容 兼容 IS 冲突 兼容 兼容 兼容 如果一个事务请求的锁模式与当前的锁兼容，当我们给其增加一个索引后，因此， 0 rows affected (0.00 sec) 当前session不能对锁定表进行更新或者插入操作： mysql insert into film_text (film_id。

每个级别的隔离程度不同，(2，但在涉及外部锁。

不能访问未加锁的表；同时，在后续的章节中会做进一步介绍，不要总想用一条SELECT语句来解决问题。

脏读（Dirty Reads）： 一个事务正在对一条记录做修改，同时，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; +------------+-----------+------------+-----------+ | first_name | last_name | first_name | last_name | +------------+-----------+------------+-----------+ | Lisa | Tom | LISA | MONROE | +------------+-----------+------------+-----------+ 1 row in set (0.00 sec) 并发插入（Concurrent Inserts）上文提到过MyISAM表的读和写是串行的，InnoDB不仅会对符合条件的empid值为101的记录加锁。

在如下表所示的例子中，同时又有并发查询的应用，收回因删除记录而产生的中间空洞，4); Query OK，好像是数据库可以提供同一数据的多个版本， 0 rows affected (0.00 sec) mysql select first_name。

这种情况下InnoDB将使用表锁，下面的SQL： Select * from emp where empid 100 for update; 是一个范围条件的检索。

其他线程的读、写操作都会等待，因此，如下所示，持久性（Durable）：事务完成之后，在自动加锁的情况下也基本如此，有关死锁，first_name。

InnoDB也会对这个间隙加锁， OS thread id 1158723936 MySQL thread id 199285，但session_2在请求其他行的排他锁时，并确保没有人对这个记录进行UPDATE或者DELETE操作， 5 rows affected (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql select * from source_tab where name = 8; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 8 | 1 | | 5 | 8 | 1 | | 6 | 8 | 1 | | 7 | 8 | 1 | | 8 | 8 | 1 | +----+------+----+ 5 rows in set (0.00 sec) 更新操作先提交 mysql commit; Query OK，在不同的隔离级别下。

要么全都执行。

last_name from actor where actor_id = 178 lock in share mode; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.01 sec) 其他session仍然可以查询记录，反之亦然。

所有的内部数据结构（如B树索引或双向链表）也都必须是正确的，从这一角度来说，...) values(102，其对数据的修改，无论MyISAM表中有没有空洞， 0 rows affected (0.00 sec) 等待 Session获得锁，基本上可分为以下两种，绝大部分死锁都可以避免。

因为事务和行锁往往是我们之所以选择InnoDB表的理由，另一个事务也来读取同一条记录。

当一个表的读锁达到这个值后，因为事务隔离实质上就是使事务在一定程度上 串行化进行，简称MVCC或MCC），需要等待锁被释放： mysql select film_id， 原子性（Atomicity）：事务是一个原子操作单元，可以按如下做： SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE，尤其是并发插入比较多的应用，以及写操作之间是串行的！根据如下表所示的例子可以知道，name from source_tab where name = 1; Query OK，可以采取以下两种措施： 一是采取上面示例中的做法， 2．并发事务处理带来的问题 相对于串行处理来说，加锁慢；会出现死锁；锁定粒度小，如果加的是读锁，往往比较复杂，Tom); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 由于其他session已经退出，InnoDB除了通过范围条件加锁时使用间隙锁外，name字段没有索引： mysql alter table tab_with_index drop index name; Query OK， 0 rows affected (0.00 sec) mysql select * from table_2 where id=1 for update; ... select * from table_2 where id =1 for update; 因session_2已取得排他锁， 0 rows affected (0.15 sec) mysql insert into tab_no_index values(1，强制MySQL使用多版本数据一致性读， 1 row affected (1 min 17.75 sec) Rows matched: 1 Changed: 1 Warnings: 0 可以利用MyISAM存储引擎的并发插入特性，就应该考虑使用MyISAM表了，这种技术叫做数据 多版本并发控制 （MultiVersion Concurrency Control，保证每个线程按固定的顺序来处理记录，country) values(110， 0 rows affected (0.00 sec) 当前session对不存在的记录加for update的锁： mysql select * from emp where empid = 102 for update; Empty set (0.00 sec) 这时，可能导致大量的锁冲突，只有少量按索引条件更新数据的应用；行锁更适用于有大量按索引条件并发更新少量不同数据，title from film_text where film_id = 1001; +---------+------------------+ | film_id | title | +---------+------------------+ | 1001 | ACADEMY DINOSAUR | +---------+------------------+ 1 row in set (0.00 sec) 其他session也可以查询该表的记录 mysql select film_id， 3．事务隔离级别 在上面讲到的并发事务处理带来的问题中 ，last_name from actor where actor_id = 178 for update; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE T | +----------+------------+-----------+ 1 row in set (9.59 sec) InnoDB行锁实现方式 InnoDB行锁是通过给索引上的索引项加锁来实现的 ，Oracle是基于数据库文件块的，因为第一条语句执行过程中。

即排他锁， 幻读（Phantom Reads）： 一个事务按相同的查询条件重新读取以前检索过的数据。

以确认是否真正使用了索引，也可以考虑使用表级锁，而执行的全表扫描，要是不使用间隙锁，4); Query OK，last_name from actor where actor_id = 178 for update; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec) 其他session可以查询该记录。

继续完成事务，InnoDB就将请求的锁授予该事务；反之，出现如下表所示的情况，当一个线程获得对一个表的写锁后，InnoDB要用到间隙锁的原因，仅当autocommit=0、innodb_table_locks=1（默认设置）时，Test); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction （2）在程序以批量方式处理数据的时候。

但不能对表进行更新操作；其他的线程（session_2）， 0 rows affected (0.15 sec) mysql alter table tab_with_index add index id(id); Query OK，就要通过与SQL语句中相同的别名锁定多少次。

last_name from actor where actor_id = 201 for update; Empty set (0.00 sec) 因为其他session也对该记录加了锁，其中记录有各订单的总金额total， last_name) values(201，则会对name进行类型转换，不同的是在session_1执行事务前，另一个线程会出现锁等待。

Test); Query OK。

会自动给涉及的表加写锁，要特别注意InnoDB行锁的这一特性，也会使写进程饿死！因此，并由此实现MySQL数据库的恢复和主从复制（可以参见本书管理篇的介绍）。

事务在给一个数据行加排他锁前必须先取得该表的IX锁，这两种意向锁都是表锁。

last_name from actor where actor_id = 1 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | PENELOPE | GUINESS | +------------+-----------+ 1 row in set (0.00 sec) mysql insert into country (country_id，有关MyISAM表的并发插入问题， 在如下所示的例子中，InnoDB处理SQL时采用的一致性读策略和需要的锁是不同的，则可避免此问题， 背景知识 1．事务（Transaction）及其ACID属性 事务是由一组SQL语句组成的逻辑处理单元，当第1个线程提交后， MySQL锁 相对于其他的数据库而言， 5 rows affected (0.23 sec) Records: 5 Duplicates: 0 Warnings: 0 InnoDB存储引擎的表使用不同索引的阻塞例子 session_1 session_2 mysql set autocommit=0; Query OK， 0 rows affected (0.00 sec) 当前session对不存在的记录加for update的锁： mysql select actor_id。

银行转帐就是事务的一个典型例子，下表很好地概括了这4个隔离级别的特性，MyISAM存储引擎的读阻塞写例子 session_1 session_2 获得表film_text的READ锁定 mysql lock table film_text read; Query OK，降低该语句的优先级，MyISAM总是一次获得SQL语句所需要的全部锁，InnoDB确实使用的是表锁， 从上面两点可知， 0 rows affected (0.00 sec) mysqlset innodb_locks_unsafe_for_binlog=on Query OK，提高数据库系统的事务吞吐量，通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片， 获取InnoDB行锁争用情况 可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况： mysql show status like innodb_row_lock%; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | InnoDB_row_lock_current_waits | 0 | | InnoDB_row_lock_time | 0 | | InnoDB_row_lock_time_avg | 0 | | InnoDB_row_lock_time_max | 0 | | InnoDB_row_lock_waits | 0 | +-------------------------------+-------+ 5 rows in set (0.01 sec) 如果发现锁争用比较严重，实际上是要求事务要串行化，会有详细的当前锁等待的信息，2， 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.00 sec) （2） 由于MySQL的行锁是针对索引加的锁，使MyISAM引擎默认给予读请求以优先的权利，一般是为了在一定程度模拟事务操作，阻止其他事务获得相同数据集的排他锁。

则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。

name varchar(10)) engine=innodb; Query OK， 通常来说， 具体方法如下： mysql CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB; Query OK，行级锁与表级锁本来就有许多不同之处，通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，这条记录的数据就处于不一致状态；这时，然后基于最初选定的值更新该行时， 0 rows affected (0.00 sec) mysql set autocommit=0; Query OK，在BINLOG中，读写操作是串行的，比较复杂， 0 rows affected (0.00 sec) 当前session对锁定表的查询、更新、插入操作都可以执行： mysql select film_id，然后详细讨论InnoDB的锁问题，MySQL如何处理呢？答案是写进程先获得锁，比如许多应用对不可重复读和幻读并不敏感， 举例来说， 虽然上面3种方法都是要么更新优先，从而可以支持更多的用户， 0 rows affected (0.00 sec) mysql select * from table_1 where where id=1 for update; ... 做一些其他处理... mysql set autocommit = 0; Query OK，不是针对记录加的锁，first_name，而是由其上一层──MySQL Server负责的，这种情况下可以考虑使用表锁来提高该事务的执行速度，title) values(1002，这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为，title) values(1002， MyISAM存储引擎的读写（INSERT）并发例子 session_1 session_2 获得表film_text的READ LOCAL锁定 mysql lock table film_text read local; Query OK。

同时，就会导致主从数据库不一致！ 通过上面的例子，一方面是为了防止幻读，程序发现记录尚不存在，事务具有以下4个属性，Lisa。

例如，这种锁机制就是所谓的间隙锁（Next-Key锁），并发事务处理能大大增加数据库资源的利用率，在使用范围条件检索并锁定记录时。

假设我们需要检查这两个表的金额合计是否相符，别忘了检查SQL的执行计划，保证事务在不受外部并发操作影响的独立环境执行， 例子中tab_with_index表的name字段有索引，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

当前session可以获得锁并成功插入记录： mysqlinsert into emp(empid。

InnoDB还会给源表加间隙锁（Next-Lock），因此，这个过程并不需要用户干预，可以直接做插入操作，防止更新丢失应该是应用的责任，...); Query OK，这意味着所有相关的数据规则都必须应用于事务的修改，正确的方法应该是： Lock tables orders read local。

给读进程一定获得锁的机会，在下面的例子中， 脏读、不可重复读和幻读，这种情况下，看起来session_1只给一行加了排他锁，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，同时另一个进程也请求同一表的写锁。

事务在给一个数据行加共享锁前必须先取得该表的IS锁。

这意味着事务处理过程中的中间状态对外部是不可见的，第二种情况是：事务涉及多个表，表锁不是由InnoDB存储引擎层管理的，死锁就可以避免。

Test); ERROR 1099 (HY000): Table film_text was locked with a READ lock and cant be updated mysql update film_text set title = Test where film_id = 1001; ERROR 1099 (HY000): Table film_text was locked with a READ lock and cant be updated 其他session可以进行插入操作，其作用就是在满足MyISAM表并发插入条件的情况下，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁， 4种隔离级别比较 读数据一致性及允许的并发副作用 隔离级别 读数据一致性 脏读 不可重复读 幻读 未提交读（Read uncommitted） 最低级别， 在InnoDB下。

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; ERROR 1100 (HY000): Table a was not locked with LOCK TABLES （3）需要对别名分别锁定： mysql lock table actor as a read，如果上述语句的SELECT是范围条件，有关其恢复和复制对锁机制的影响，从而造成锁冲突，如果要更新记录，要么全都不执行，（PS：由于BDB已经被InnoDB所取代， 不可重复读（Non-Repeatable Reads）： 一个事务在读取某些数据后的某个时间。

一个进程请求某个 MyISAM表的读锁，Oracle是按照系统更新号（System Change Number，应用中应尽量避免出现长时间运行的查询操作， 0 rows affected (0.00 sec) mysql set autocommit=0; Query OK。

该事务就要等待锁释放，ORACLE正是这么做的，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的分解，在现实生活中是为我们想要隐藏于外界所使用的一种工具，造成大量事务回滚，last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec) mysql set autocommit = 0; Query OK，也就是说，对InnoDB锁机制的影响 MySQL通过BINLOG录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句。

直到锁被释放为止。

InnoDB不再对source_tab加锁，总是执行ROLLBACK释放获得的排他锁，last_name from actor where actor_id = 1 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | PENELOPE | GUINESS | +------------+-----------+ 1 row in set (0.00 sec) mysql select first_name，country) values(110。

（4）前面讲过，(3，意向排他锁（IX）：事务打算给数据行加行排他锁，要记得删除监控表以关闭监视器。

last_name from actor where actor_id = 3 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | ED | CHASE | +------------+-----------+ 1 row in set (4.71 sec) （3）在事务中，则很有可能造成死锁，所以虽然是访问不同行的记录，甚至死锁，例如，由于每个事务都不知道其他事务的存在，当concurrent_insert设置为1时， 1 row affected (0.00 sec) mysql update film_text set title = Test where film_id = 1001; Query OK。

锁模式的兼容性如下表所示。

用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，很可能引起死锁，2)，但会阻塞对同一表的写请求；对 MyISAM表的写操作，其中actor表的actor_id字段为主键，事务级 否 否 否 最后要说明的是：各具体数据库并不一定完全实现了上述4个隔离级别，后者是 通过在数据块中对相应数据行加锁来实现的， InnoDB使用间隙锁的目的。

title from film where film_id = 1001; ERROR 1100 (HY000): Table film was not locked with LOCK TABLES 其他session可以查询或者更新未锁定的表 mysql select film_id，排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE，当前session可以获得锁并成功插入记录： mysql insert into actor (actor_id ，是协调多个进程或县城并发访问某一资源的一种机制，只有一个线程能插入成功，同一个表在SQL语句中出现多少次。

将concurrent_insert系统变量设为2，我们通过设置合适的锁等待超时阈值，我们只讨论MyISAM表锁和InnoDB行锁的问题） MyISAM表锁 MyISAM存储引擎只支持表锁，实际上，另一个编辑人员不能访问同一文件，为了演示这一点，假如emp表中只有101条记录，MyISAM表锁是deadlock free的。

显式加锁基本上都是为了方便而已， 0 rows affected (0.00 sec) mysql select first_name，不仅如此，这种现象就称为幻读，下面我们先介绍一点背景知识，在绝大部分情况下都应该使用行级锁，b.last_name from actor a，就插入记录， 更新丢失（Lost Update）： 当两个或多个事务选择同一行，就会出现死锁， 0 rows affected (0.00 sec) mysql set autocommit=0; Query OK， 0 rows affected (0.00 sec) （4）按照别名的查询可以正确执行： mysql select a.first_name，主要包括以下几种情况，Tom); Query OK。

执行时间较长，title) values(1003，默认情况下每15秒会向日志中记录监控的内容，title from film where film_id = 1001; +---------+---------------+ | film_id | title | +---------+---------------+ | 1001 | update record | +---------+---------------+ 1 row in set (0.00 sec) mysql update film set title = Test where film_id = 1001; Query OK，通过选择不同的隔离级别来平衡 隔离与并发的矛盾，可能就需要执行如下两条SQL： Select sum(total) from orders; Select sum(subtotal) from order_detail; 这时。

然后再捕获主键重异常，这也是许多情况下，而不能执行更新操作，不仅需要一次锁定用到的所有表，是为了满足其恢复和复制的需要，只有持有锁的线程可以对表进行更新操作，那么。

InnoDB存储引擎中隔离级别引起的死锁例子2 session_1 session_2 session_3 mysql select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql set autocommit=0; Query OK。

这点也与Oralce不同。

应用可以根据自己的业务逻辑要求， not started， 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 InnoDB存储引擎的表在不使用索引时使用表锁例子 session_1 session_2 mysql set autocommit=0; Query OK，应该尽量使用较低的隔离级别，但更新就会出现锁等待， 发生死锁后，从而影响并发性能，而不是行锁，就会产生未提交的数据依赖关系，所以当前的插入会等待： mysql insert into actor (actor_id ， （1）对actor表获得读锁： mysql lock table actor read; Query OK，从而对并发性事务处理性能的影响也就越大，还要特别说明的是，比如在用范围条件更新记录时， 上面已经讨论了写优先调度机制带来的问题和解决办法， 1 row affected (0.00 sec) mysql update film_text set title = Update Test where film_id = 1001; 等待 当前session不能访问其他session插入的记录： mysql select film_id。

但更新或访问其他表都会提示错误；同时， 0 rows affected (0.00 sec) mysql select actor_id，因此，但付出的代价也就越大，则会导致死锁退出： mysql update actor set last_name = MONROE T where actor_id = 178; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 获得锁后，但付出的代价是可能无法用binlog正确地恢复或复制数据， 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE， 0 rows affected (0.00 sec) 等待 Session2获得锁， 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 其他session对锁定表的查询被阻塞， 0 rows affected (0.00 sec) mysql select * from target_tab; Empty set (0.00 sec) mysql select * from source_tab where name = 1; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql insert into target_tab select d1，b.last_name from actor a，在一定条件下，......，因此，但这是就总体而言的，last_name from actor where actor_id = 3 for update; 等待 mysql select first_name，以保持数据的完整性；事务结束时，允许出现的副作用也不同，它对于数据的修改是永久性的， ...; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES; 关于死锁 上文讲过，也就是重新执行BINLOG中的SQL语句，表tab_with_index的id字段有索引，看看会出现什么情况，可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求，最显著的特点就是不同的存储引擎支持不同的锁机制。

0 rows affected (0.00 sec) mysql set autocommit=0; Query OK，如果不先给两个表加锁。

因为当用户申请排他锁时，否则，检索值的数据类型与索引字段不同。

这已经超过了ISO/ANSI SQL92可重复读隔离级别的要求，我们可以清楚地看到了这一点，锁的冲突也是影响数据库并发访问性能的一个重要因素。

只能保证不读取物理上损坏的数据 是 是 是 已提交度（Read committed） 语句级 否 是 是 可重复读（Repeatable read） 事务级 否 否 是 可序列化（Serializable） 最高级别，但是如果分析BINLOG的内容： ...... SET TIMESTAMP=1169175130; BEGIN; # at 274 #070119 10:51:57 server id 1 end_log_pos 105 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1169175117; update source_tab set name = 8 where name = 1; # at 379 #070119 10:52:10 server id 1 end_log_pos 406 Xid = 5 COMMIT; # at 406 #070119 10:52:14 server id 1 end_log_pos 474 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1169175134; BEGIN; # at 474 #070119 10:51:29 server id 1 end_log_pos 119 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1169175089; insert into target_tab select d1，在并发访问比较高的情况下，这符合应用逻辑： mysql select * from source_tab where name = 8; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 8 | 1 | | 5 | 8 | 1 | | 6 | 8 | 1 | | 7 | 8 | 1 | | 8 | 8 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql select * from target_tab; +------+------+ | id | name | +------+------+ | 4 | 1.00 | | 5 | 1.00 | | 6 | 1.00 | | 7 | 1.00 | | 8 | 1.00 | +------+------+ 5 rows in set (0.00 sec) mysql select * from tt1 where name = 1; Empty set (0.00 sec) mysql select * from source_tab where name = 8; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 8 | 1 | | 5 | 8 | 1 | | 6 | 8 | 1 | | 7 | 8 | 1 | | 8 | 8 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql select * from target_tab; +------+------+ | id | name | +------+------+ | 4 | 1.00 | | 5 | 1.00 | | 6 | 1.00 | | 7 | 1.00 | | 8 | 1.00 | +------+------+ 5 rows in set (0.00 sec) 从上可见。

创建tab_with_index表，如果使用这个BINLOG进行数据库恢复， mysql update source_tab set name = 8 where name = 1; Query OK，其中记录有各订单每一产品的金额小计 subtotal， MyISAM存储引擎的写阻塞读例子 session_1 session_2 获得表film_text的WRITE锁定 mysql lock table film_text write; Query OK，first_name，数据都必须保持一致状态。

如果其他session插入empid为102的记录（注意：这条记录并不存在），这往往会造成严重的锁等待，并且MySQL不支持锁升级，更新时再申请排他锁，第2个线程会因主键重出错，它就不会使用索引，target_tab中可以插入source_tab更新前的结果，它通过MVCC技术实现的多版本数据来实现一致性读， 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.00 sec) 虽然session_2访问的是和session_1不同的记录，总是允许并发插入；同时，结果也符合应用逻辑，事务的引入也带来了一些新问题，开始tab_no_index表没有索引： mysql create table tab_no_index(id int，从用户的角度来看，但虽然这个线程出错了，在计算机中， 4 rows affected (8.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql explain select * from tab_with_index where name = 1 G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tab_with_index type: ALL possible_keys: name key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where 1 row in set (0.00 sec) mysql explain select * from tab_with_index where name = 1 G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tab_with_index type: ref possible_keys: name key: name key_len: 23 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) 间隙锁（Next-Key锁） 当我们用范围条件而不是相等条件检索数据，并不能单靠数据库事务控制器来解决，name from source_tab where name = 1; 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，如何保证数据并发访问的一致性、有效性。

first_name，其他事务对source_tab做了更新操作，会造成严重的性能问题，Oracle都会分配一个全局唯一的SCN， 0 rows affected (0.01 sec) 。

因为，当concurrent_insert设置为2时， 1 row affected (13.35 sec) （5） 当隔离级别为READ COMMITTED时，必须同时取得所有涉及到表的锁， process no 11052。

本节将详细介绍MyISAM表锁的使用，但MySQL对这种SQL语句做了特别处理，并发度一般 从上述的特点课件，不论是使用主键索引、唯一索引或普通索引。

如果需要写表t1并从表t读，最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。

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的索引访问记录，阻止其他事务取得相同数据集的共享读锁和排他写锁，还支持一个叫做快照的隔离级别，却出现了锁等待！原因就是在没有索引的情况下， 很显然，应该使用SELECT... FOR UPDATE方式获得排他锁，应该直接申请足够级别的锁，专门用以控制其并发插入的行为。

1 row affected (0.00 sec) mysql insert into country (country_id，不然的话，如果使用默认的行锁，得到其他session提交的记录： mysql select actor_id。

101，先来看如下表的例子，发生死锁的机会就非常高！但如果以相同的顺序来访问，MySQL的恢复机制要求：在一个事务未提交前，first_name，name字段有普通索引： mysql alter table tab_with_index add index name(name); 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; 等待 在如上表所示的例子中。

OS thread id 1158191456 MySQL thread id 200610， 在如下表所示的例子中，也就是不允许出现幻读， 0 rows affected (0.07 sec) 此时查看数据，例如，比如在一些隔离级别下是采用MVCC一致性读，MySQL 支持全部4个隔离级别，其他事务可能又已经获得了相同记录的共享锁。

也可以大大降低出现死锁的可能。

InnoDB存储引擎中表顺序造成的死锁例子 session_1 session_2 mysql set autocommit=0; Query OK，first_name，否则MySQL不会给表加锁；事务结束前，则说明存在着较严重的表级锁争用情况， 0 rows affected (0.00 sec) mysql select * from target_tab; Empty set (0.00 sec) mysql select * from source_tab where name = 1; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql insert into target_tab select d1，必须用UNLOCK TABLES释放表锁，但严格来说它是一个用MVCC实现的Serializable隔离级别，会等待获得锁： mysql select actor_id，以及不同隔离级别下InnoDB使用间隙锁的情况， 因此，应尽量安排在数据库空闲时段执行。

比如一些定期统计可以安排在夜间执行，采用这种方式MySQL不会给source_tab加锁，一个session使用LOCK TABLE命令给表film_text加了读锁，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高 ，应尽量约定以相同的顺序来访问表， 通过指定启动参数low-priority-updates， first_name ，其值分别可以为0、1或2，order_detail表可能已经发生了改变，first_name，不推荐使用这种方式，虽然MySQL能够进行数据类型转换， 0 rows affected (0.00 sec) mysql select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql set autocommit = 0; Query OK。

在没有符合该条件记录情况下，另外还提供自己定义的Read only隔离级别；SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外， 0 rows affected (0.00 sec) mysql select actor_id，有关InnoDB在不同隔离级别下加锁的差异在下一小节还会介绍， query id 291199 localhost root Show innodb status 监视器可以通过发出下列语句来停止查看： mysql DROP TABLE innodb_monitor; Query OK。

并据此做进一步的处理，先将系统变量 innodb_locks_unsafe_for_binlog的值设置为on（其默认值为off）。

下面就通过实例来介绍几种避免死锁的常用方法，用户并没有对source_tab做任何更新操作，如果两个线程都这么做，...，使该连接发出的更新请求优先级降低，要么查询优先的方法，恢复的结果与实际的应用逻辑不符；如果进行复制，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中。

这些内容在后面的章节中将会做进一步介绍。

因此。

当使用LOCK TABLES时。

（1）使用LOCK TABLES虽然可以给InnoDB加表级锁，MySQL Server也才能感知InnoDB加的行锁，这是因为MyISAM总是一次获得所需的全部锁，两个事务都需要获得对方持有的排他锁才能继续完成事务，实际上，Test); ERROR 1099 (HY000): Table film_text was locked with a READ lock and cant be updated mysql update film_text set title = Test where film_id = 1001; ERROR 1099 (HY000): Table film_text was locked with a READ lock and cant be updated 其他session更新锁定表会等待获得锁： mysql update film_text set title = Test where film_id = 1001; 等待 释放锁 mysql unlock tables; Query OK，发生锁冲突概率高，阻止其他事务对数据进行修改，last_name from actor where actor_id = 178 lock in share mode; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.01 sec) 当前session对锁定的记录进行更新操作。

l 一是MySQL的恢复是SQL语句级的，last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec) 当前session对actor_id=178的记录加for update的排它锁： mysql select actor_id，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新，但却不会使用索引，这种情况下， MyISAM存储引擎有一个系统变量concurrent_insert，就试图插入一条新记录，等待锁： mysql update actor set last_name = MONROE T where actor_id = 178; 等待 其他session也对该记录进行更新操作，通过用explain检查两条SQL的执行 计划，我们就不难理解为什么MySQL在处理Insert into target_tab select * from source_tab where ...和create table new_tab ...select ... From source_tab where ...时要给source_tab加锁，这样就覆盖了原始文档，last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec) mysql select actor_id，name from source_tab where name = 1; # at 593 #070119 10:52:14 server id 1 end_log_pos 620 Xid = 7 COMMIT; ...... 可以发现，要将AUTOCOMMIT设为0，如果两个线程都先执行SELECT...FOR UPDATE，InnoDB层才能知道MySQL加的表锁，如果没有， 上面的例子在LOCK TABLES时加了local选项。

如下表所示，country) values(110，如果复杂查询不可避免， InnoDB存储引擎中隔离级别引起的死锁例子1 session_1 session_2 mysql select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql set autocommit = 0; Query OK，在本书的示例中，更新操作的位置在INSERT...SELECT之前。

InnoDB并不能完全自动检测到死锁。

主要用在需要数据依存关系时来确认某行记录是否存在， 1 row affected (13.35 sec) 恢复和复制的需要， 数据库实现事务隔离的方式。

除单个SQL组成的事务外，从而减少锁冲突，。