背景知识

事务（Transaction）及其ACID属性

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

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

并发事务处理带来的问题

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

更新丢失（Lost Update）

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题 最后的更新覆盖了由其他事务所做的更新 。

例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。

脏读（Dirty Reads）

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

不可重复读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了,这种现象就叫做“不可重复读”。

幻读（Phantom Reads）

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

事务隔离级别

在上面讲到的并发事务处理带来的问题中，“更新丢失”通常是应该完全避免的。但并不能单靠数据库事务控制器来防止更新丢失，对要更新的数据加必要的锁是一种有效的方式，但是加什么锁，怎么加，就是应用的责任了。

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

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

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

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

4种隔离级别比较

读数据一致性及允许的并发副作用/隔离级别

读数据一致性

脏读

不可重复读

幻读

未提交读（Read uncommitted）

最低级别，只能保证不读取物理上损坏的数据

是

已提交度（Read committed）

语句级

否

是

可重复读（Repeatable read）

事务级

否

是

可序列化（Serializable）

最高级别，事务级

否

各具体数据库并不一定完全实现了上述4个隔离级别:

Oracle只提供Readcommitted和Serializable两个标准隔离级别，另外还提供自己定义的Read only隔离级别。
SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。
MySQL 支持全部4个隔离级别。

悲观锁与乐观锁：

悲观锁

每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁

每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

MySQL中的锁

Mysql中不同的存储引擎支持不同的锁机制。比如MyISAM和MEMORY存储引擎采用的表级锁，BDB采用的是页面锁，也支持表级锁，InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。

各级锁特性如下：

表级锁：开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发性也最高。
页面锁：开销和加锁界于表锁和行锁之间，会出现死锁；锁定粒度界与表锁和行锁之间，并发一般。

MyISAM表锁

MyISAM只支持表锁，这也是早期MySQL的唯一支持的锁。而随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁和BDB和支持行锁的InnoDB。

表锁有共享读锁和独占写锁两种模式。读锁与读锁是兼容的，读锁与写锁是互斥的，写锁与写锁是互斥的。也就是说，MyISAM表的一个连接的读操作是不会阻塞其它连接的读操作的，但是会阻塞其它连接的写操作，而一个连接的写操作会阻塞其它连接的读操作和写操作。

MyISAM在执行查询(SELECT)时会自动给涉及到的表加上读锁，在执行更新(UPDATE、DELETE、INSERT)等，会自动给涉及到的表加上写锁。但是我们也可以显示加锁/解锁：

查询表级锁争用情况

通过检查table_locks_waited和table_locks_immediate状态变量分析系统上表锁争夺情况：

png

table_locks_waited锁定等待时间越长，则说明存在较严重的表级别锁争用情况。

锁模式

mysql的表锁有两种模式：表共享读锁（table read lock）和表独占写锁（table write lock）：

png

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

加表锁

MyISAM在执行查询前，会自动执行表的加锁、解锁操作，一般情况下不需要用户手动加、解锁，但是有的时候也需要显示加锁。

例如：检索某一个时刻t1，t2表中数据数量。常用代码如下：

select count(t1.id1) as 'sum' from t1;
select count(t2.id1) as 'sum' from t2;

但是，这是不正确的，很有可能当你在检索t1的那个时间点，t2的数据已经发生了变化，也就是说你检查出的t1和t2数据结果不是在同一个时间点上。正确的做法是：

    lock table t1 read, t2 read;
    select count(t1.id1) as 'sum' from t1;
    select count(t2.id1) as 'sum' from t2;
    unlock tables;

注意事项

在锁定表时候，如果加上关键字local，满足MyISAM表的并发插入问题。eg： lock table t3 read local。
使用lock tables给表加锁时候，必须同时给所有涉及到的表加锁，因为加锁之后，当前会话，就不能操作没有加锁的表。

并发插入问题

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

concurrent_insert为0时候，不允许插入。
concurrent_insert为1时候，如果mysql没有空洞（中间没有被删除的行），MyISAM运行一个进程读表的时候，另一个进程从表尾插入记录，这也是mysql默认设置。
concurrent_insert为2时候，无论MyISAM表中有没有空洞，都允许在表尾并行的插入。

MyISAM锁调度问题

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的，那么如果读写两个进程同时请求同一张表，Mysql将会使写进程先获得锁。不仅仅如此，即使读请求先到达锁等待队列，写锁后到达，写锁也会先执行。因为mysql认为写请求比读请求更加重要。这也正是MyISAM不适合含有大量更新操作和查询操作应用的原因。

调节办法：

通过指定启动参数low-priority-updates,使MyISAM引擎默认给与读请求优先的权限。
通过执行set low_PRIORITY_UPDATES=1，降低更新请求的优先级。
指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性。

InnoDB锁

InnoDB支持事务，支持行锁和表锁用的比较多。

在MySQL InnoDB中共有七种类型的锁：

共享/排他锁（shared and exclusion locks）

事务拿到某一行记录的共享S（share locks）锁时，才可以读取这一行。
事务拿到某一行记录的排他X（exclusive locks）锁时，才可以修改或者删除这一行。
多个事务可以拿到一把S锁时，可以并行。其他情况都不能并行。

一旦写数据的任务没有完成，数据是不能被其他事务读取的，即写事务没有提交，读相关数据的select也会被阻塞；当读事务没有提交时，写事务也会被阻塞。这对并发量有较大的影响。共享/排他锁的潜在问题是，不能充分的并行，解决思路是数据多版本。因此：

普通锁：本质是串行。
读写锁：可以实现读读并发。
数据多版本：可以实现读写并发，但会增加系统资源的消耗，因为会进行复制。
适用场景：对已有数据行的修改与删除，必须加强互斥锁X锁。

意向锁（intention locks）

当一个事务可能要加共享/排他锁，则会先声明一个意向。意向锁，是一个表级别的锁。

意向共享锁(intention shared lock, IS):事务有意向对表中的某些行加共享S锁。事务要获取某些行的S锁，必须获得表的IS锁。
意向排它锁(intention exclusive lock, IX):事务有意向对表中的某些行加排它X锁。事务要获取某些行的X锁，则必须先获得表的IX锁。

意向锁直接并不相互互斥，它是一种很弱的锁。它会和排他锁和共享锁互斥。

’'

兼容

互斥

适用场景：当事务有意向获取某些行的X锁时。

记录锁（record locks）

记录锁用于封锁索引记录，它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

select * from t where id=1 for update;

快照读(SnapShot Read)并不加锁

select * from t where id=1;

适用场景：对某一条记录上进行写操作时或查询时用 for update 显示加锁。

间隙锁（Gap locks）

间隙锁封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

    select * from t 
        where id between 8 and 15 
        for update;
    封锁区间，以阻止其他事务id=10的记录插入

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。

使用场景：区间写操作时（between and），或用for update 显示加锁。

临键锁（Next-key locks）

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。临键锁的主要目的是为了避免幻读。

插入意向锁（insert intention locks）

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。它是一种实施在索引上，锁定索引某个区间范围的锁。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突（只有位置相同，比如id相同，才会阻塞），不会阻塞彼此。总的来说：

InnoDB使用共享锁，可以提高读读并发。
为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性。
InnoDB使用插入意向锁，可以提高插入并发。
使用场景：多个事务同时进行插入，并表中没有自增列。当且仅当插入位置相同时，事务之间才会阻塞。

自增锁（auto-inc locks）

InnoDB 在RR（read repeatable）隔离级别下，能解决幻读问题。何为幻读：幻读就是事务A读取了事务B新增的数据。也就是说事务A重复读取某一数据时，事务B新插入的数据也满足事务A读取的数据，然后出现了第一次未读取到的数据。

自增锁是一种特殊的表级别锁，专门针对事务插入自增类型的列。当一个事务正往表中插入记录时，其他所有事务的插入必须等待。以便第一个事务插入的行是连续的主键值。

当在自增列的情况下：

    事务A执行一条insert，得到一条（1, …）的记录，因为是自增列，所以不用显示指定其id，InnoDB会自动增长，但是此时事务并未提交

    事务B执行insert，假设不会阻塞，则得到一条（2, …）的记录

    事务A再进行insert，会得到一条（3, ...） 的记录，如果这时进行select * from table_test where id > 0 ;得到的数据只有id为1和3的，这时事务A就会很奇怪，对于自增列，连续插入了两条记录，为什么id不连续。所以事务B会被阻塞

使用场景：自增列（插入时）。

InnoDB行锁争用情况

png

共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是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。
```
InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！。

png

MyISAM和InnoDB的区别。

设计目标

InnoDB设计目标是处理大容量的数据，而MyISAM追求的是性能，两者产生的差异也是基于这点。 InnoDB是MySQL的默认存储引擎。

事务处理

InnoDB支持事务和外键，MyISAM不支持。MyISAM强调的是性能，InnoDB支持的功能更加完整。InnoDB支持事务带来了一个好处，发生故障时可以通过事务日志来恢复数据库，MyISAM特别要命的一点是崩溃后不能安全恢复，所以对于表比较大的情况不要用。

效率和锁

两种存储引擎的效率差异来自于锁的方式差异，MyISAM是表锁，对数据库进行写操作时会锁住整个表，效率很低；确定要修改数据的范围时，InnoDB是行锁，只锁一行的数据，写操作很快。但也有特例，比如UPDATE student SET age=10 WHERE name LIKE ‘王%’，这种情况不能确定要UPDATE的行位置，InnoDB同样会锁住整个表。

索引

MyISAM支持全文索引，InnoDB不支持。

COUNT(*)

MyISAM保存了表的行数，InnoDB没有。也就是说，执行SELECT COUNT(*) FROM student的操作时，MyISAM可以直接给出结果，而InnoDB要先扫描全表。不过对于加了where条件的查询操作，效果是一样的。

AUTO_INCREMENT

InnoDB下只能对自增字段单独建索引，MyISAM下可以和其它列一起建联合索引。

死锁

所谓死锁: 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程.表级锁不会产生死锁.所以解决死锁主要还是对于最常用的InnoDB。

遇到死锁的处理方式 ``` mysql -uxxx -pxxx -h服务器ip –port=服务器端口;（如果服务器设置了ip和端口访问的话，一定要带ip和端口）

mysql> show processlist; #查看正在执行的sql （show full processlist;查看全部sql） mysql> kill id #杀死sql进程；如果进程太多找不到，就重启mysql吧 /ect/init.d/mysql restart 或/ect/init.d/mysql stop（如果关不掉就直接kill -9 进程id）再/ect/init.d/mysql start 去看看mysql日志文件是否保存死锁日志：常用目录：/var/log/mysqld.log；（该目录还有其它相关日志文件就都看看） ```

当然，怎么解决还是要看具体什么问题.

数据库 (4)

置顶文章

最新发表

链接

数据库-mysql的各种锁