mysql单个表回退到某个点_MySQL锁模型与事物

MySQL中不同的存储引擎支持不同的锁机制，比如，MySAM和MEMORY存储引擎采用表级锁，InnoDB支持表级锁和行级锁(默认采用行级锁)，BDB(被InnoDB取代)支持表级锁和页面锁。

查看mysq提供的存储引擎：mysql> show engines;

查看mysql当前默认的存储引擎：mysql> show variables like '%storage_engine%';

查看某个表用的引擎：mysql> show create table 表名;

一、表级锁

1、表级锁特点

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，推荐使用表级锁。

命令mysql> show status like 'Table%';

Table_locks_immediate 指的是能够立即获得表级锁的次数，而Table_locks_waited指的是不能立即获取表级锁而需要等待的次数。如果 Table_locks_waited的值比较大的话，则说明存在着较严重的表级锁争用情况，这是可能需要创建一个专有的缓存表，或者通过其它方式来减小表的大小，或者降低表级锁命令调用的频率。

2、表级锁的锁模式

读锁：成功申请读锁的前提是当前没有线程对该表使用写锁，否则该语句会被阻塞。申请读锁成功后，其他线程也可以对该表进行读操作，但不允许有线程对其进行写操作，包括当前线程。 用法：申请——LOCK TABLE table_name [ AS alias_name ] READ，释放——UNLOCK tables。

写锁：成功申请写锁的前提是当前没有线程对表加读锁和其他写锁，否则会被阻塞。写锁可以加优先级，当多个线程同时申请多种锁(LOW_PRIORITY,READ,WRITE)时，LOW_PRIORITY的优先级最低。 用法：申请——LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE。

3、如何给表加锁

MyISAM执行SELECT前会自动把涉及的所有的表加读锁，在执行UPDATE、DELETE、INSERT前会自动把涉及的所有的表加写锁。用户一般不需要直接用LOCK TABLE命令给MyISAM表加锁。

显式地给MyISAM表加锁，一般是为了模拟事物操作，实现在某一个时间点多个表的一致性读取。例如，order表记录了每个订单的总金额(total)，order_detail记录了每个订单中每个产品的金额小计(subtotal)，如果需要检查俩个表的金额是否一致，则：

Lock tables orders read local, order_detail read local;

Select sum(total) from orders;

Select sum(subtotal) from order_detail;

Unlock tables;

注：local选项作用是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录

4、一个表锁导致阻塞的例子

Session1

Session2

CREATE TABLE my_contacts # 创建表联系人

(

id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

NAME VARCHAR (30) NOT NULL,

gender CHAR(1) NOT NULL DEFAULT 'M',  #'M'或'F'

birthday DATE,   # 1980-09-15

phone VARCHAR (11) NOT NULL,

information BLOB

) ENGINE = MYISAM ;

INSERT  INTO my_contacts(NAME,gender,birthday,phone,information) VALUES ('ZhangSan','F','1990-09-09','13513513513','Friend');

INSERT  INTO my_contacts(NAME,gender,birthday,phone,information) VALUES ('LiSi','F','1991-08-12','18989107021','strange boy');

LOCK TABLE my_contacts WRITE;

SELECT * FROM my_contacts WHERE NAME="ZhangSan";

返回查询结果

SELECT * FROM my_contacts WHERE NAME="LiSi"; 阻塞… …

UNLOCK TABLES;

阻塞… …

返回查询结果

5、注意事项

Lock table时，如果查询语句中用到别名以及其他的表，那么别名和其他的表均需要锁住。如果锁住了别名，那么查询语句中就不能直接用表名了。在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

LOCK TABLE my_contacts READ;

SELECT a.name ,b.math FROM my_contacts a, scores b WHERE a.`name`=b.`name`;

以上查询语句中，my_contacts被锁住了，但使用了别名a，a没有被锁住；表scores也没有被锁住，且scores也用了别名，修改为：

LOCK TABLE my_contacts as a READ, scores as b READ;

SELECT a.name ,b.math FROM my_contacts a, scores b WHERE a.`name`=b.`name`;

6、并发插入

一般而言，MyISAM表的读和写是串行的。在一定条件下，MyISAM表也支持查询和插入并发执行。

MyISAM有一个系统变量concurrent_insert，专用于控制并行插入的行为，其值可以为0、1或2。为0时，不允许并发插入；为1时，(默认设置)，如果MyISAM表中没有空洞(即表的中间没有被删除的行)，则允许一个进程读表，另一个进程在表尾插入；为2时，允许在表尾并发插入记录。

Session1

Session2

SHOW VARIABLES LIKE "%current_insert%";

current_insert的值为AUTO，(1)

LOCK TABLE my_contacts READ LOCAL;

当前session可以查询，不能更新；其它session可以并发插入(如果无空洞)，不能删除更新

INSERT INTO my_contacts (NAME,gender,birthday,phone,information) VALUES ('LiHong','F','1960-01-01','18956234756','');

插入失败，my_contacts被locked；

UPDATE my_contacts SET gender = 'M' WHERE NAME = 'ZhangSan';

更新失败，my_contacts被locked；

INSERT INTO my_contacts (NAME,gender,birthday,phone,information) VALUES ('WangHui','M','1990-01-01','13758694231','');

并发插入成功；

SELECT id, gender FROM my_contacts WHERE NAME  = 'ZhangSan';

查询当前session内的记录成功；

SELECT * FROM my_contacts WHERE NAME  = 'WangHui';

无法查询到并发插入的记录；

UPDATE my_contacts SET gender = 'M' WHERE NAME = 'LiSi';

更新操作被阻塞… …

UNLOCK TABLES;

被阻塞… …

SELECT * FROM my_contacts WHERE NAME  = 'WangHui';

查询其它session插入的记录成功

更新记录成功

当一个表获得READ LOCAL锁后，该线程可以对表进行查询，不能更新，插入等，但其它线程可以并发插入，(concurrent_insert=2或者concurrent_insert=1且无空洞)，但不能删除和更新。

MyISAM的并发插入特性可用来解决对同一表查询和插入的锁争用。例如，设置concurrent_insert=2，且通过空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录产生的中间空洞。

7、MyISAM的粒调度

MySQL一般认为写请求比度请求更重要，所以一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，写进程会先获得锁，不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这时，可以通过一些设置来调节MyISAM 的调度行为。

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

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

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

给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

需要注意的是，一些需要长时间运行的查询操作，也会使写进程“饿死”！应用中应尽量避免长时间运行的查询操作，可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

二、行级锁

InnoDB与MyISAM的最大不同点是：1、支持事物(Transaction)，2、支持行级锁。锁定粒度最细的一种锁，能大大减少数据库操作的冲突，由于其粒度小，加锁的开销最大。

1、事物

事务是由一组SQL语句