Mysql的锁

1-概念

DDL(操作字段)：
		操作数据库、表、列等（这些对象进行操作），使用的关键字：CREATE、 ALTER、 DROP。
		
DML(操作数据)
		是对表中的数据进行增、删、改的操作。不要与DDL混淆了。 INSERT 、UPDATE、 DELETE。

两段锁

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）
加锁阶段：
			在该阶段可以进行加锁操作。
			在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），
			在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。
			加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
解锁阶段：
			当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

2-锁的级别分类 (表级锁, 行级锁, 页面锁)

	表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。  
	              MyISAM和MEMORY存储引擎, InnoDB存储引擎
	行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 
	               InnoDB存储引擎的默认锁
	页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 . 
	               主要是BerkeleyDB存储引擎。

2-1 行级锁

注意:

(1)-行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁, 是表锁。

(2)- 两个事务不能锁同一个索引

	 S锁(共享锁/读锁):  shared lock
				又称读锁，
				若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，
				其他事务只能再对A加S锁，而不能加X锁，
				直到T释放A上的S锁。
				这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
		    上共享锁的写法：lock in share mode
			例如： select  math from zje where math>60 lock in share mode；

		X锁(排他锁)  exclusive lock
				又称写锁。
				若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，
				其他事务不能再对A加任何锁(包括共享锁和排他锁)，直到T释放A上的锁。
				(之所以执行update,delete,insert进入等待状态, 是以为这些语句都会自动给涉及到的数据加上排他锁)
				但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制
			上排它锁的写法：for update
		    例如：select math from zje where math >60 for update；

		第一步:   执行窗口1前两行, 
		第二步:   执行窗口2, 程序waiting.,,,,,,,
		第三步:   执行窗口1, 最后一行, 窗口2执行出现结果

2-2-表级锁(表锁/元数据锁Meta_data lock,MDL)

表锁:

表锁的语法是 lock tables … read/write。
可以用unlock tables主动释放锁，
也可以在客户端断开的时候自动释放。
需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 
如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，
则其他线程写t1、读写t2的语句都会被阻塞。
同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。
连写t1都不允许，自然也不能访问其他表。
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。
而对于InnoDB这种支持  行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

MDL（metadata lock)

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

因此，在MySQL 5.5版本中引入了MDL，
	当对一个表做增删改查操作的时候，加MDL读锁；
       当要对表做结构变更操作的时候，加MDL写锁。
       
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。
因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

虽然MDL锁是系统默认会加的，但却是你不能忽略的一个机制。

	    比如下面这个例子，我经常看到有人掉到这个坑里：
		给一个小表加个字段，导致整个库挂了。
		
		你肯定知道，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。
		在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。
		而实际上，即使是小表，操作不慎也会出问题。
		
		我们来看一下下面的操作序列，假设表t是一个小表(备注：这里的实验环境是MySQL 5.6)。

• 我们可以看到session A先启动，这时候会对表t加一个MDL读锁。
• 由于sessionB需要的也是MDL读锁，因此可以正常执行。
• 之后session C会被blocked，是因为sessionA的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。
• 如果只有sessionC自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。
• 前面我们说了，所有对表的增删改查操作都需要先申请MDL读锁，就都被 锁住，等于这个表现在完全不可读写了。

如何安全地给小表加字段？

1: kill事务
首先我们要解决长事务，事务不提交，就会一直占着MDL锁。
在MySQL的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。

SELECT CONCAT_WS('','kill',' ',t.trx_mysql_thread_id,';')a FROM information_schema.INNODB_TRX t;

如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。

但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？
这时候kill可能未必管用，因为新的请求马上就来了。

比较理想的机制是，以下2种

2: 手动修改数据库的超时时间: lock_wait_timeout 参数
将参数修改为30s, 默认是31536000s(1年…)

3: 在alter table语句里面设定等待时间，
如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

MariaDB已经合并了AliSQL的这个功能，所以这两个开源分支目前都支持DDL NOWAIT/WAIT n
这个语法。

ALTER  TABLE  tbl_name  NOWAIT  add  column  ... 
ALTER  TABLE  tbl_name  WAIT  N  add  column  ...

大表添加字段

大多数的alter table操作都本质:
lock — copy to new table — rename — unlock的过程，
如果数据很多, 锁表时间会很长

# 创建新表
CREATE TABLE main_table_new LIKE main_table;	
# 新表添加字段
ALTER TABLE main_table_new ADD COLUMN location varchar(256);	
# 旧表数据添加至新表(这里可以在使用触发器, 然后旧表数据分批处理)
INSERT INTO main_table_new (fields_in_main_table) SELECT * FROM main_table;		
# 重新命名新表
RENAME TABLE main_table TO main_table_old, main_table_new TO main_table;	
# 删除旧表	
DROP TABLE main_table_old;