mysql-锁
一、锁概述
1、相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，
（1）MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
（2）BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；
（3）InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
2、MySQL这3种锁的特性可大致归纳如下。开销、加锁速度、死锁、粒度、并发性能。
（1）表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
（2）行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
（3）页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！
二、My ISAM表锁
1、MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
2、MySQL中的表锁兼容性 。
3、给表加锁
（1）加共享读锁 lock table 表名 read。
（2）表独占写锁lock table 表名 write。
4、说明:
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。
MyISAM表的读操作与写操作之间，以及写操作之间是串行的！
（1）对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
（2）对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
（3）MyISAM表的读操作与写操作之间，以及写操作之间是串行的！当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
（4）一个session使用LOCK TABLE命令给表f加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。
5、并发插入（Concurrent Inserts）
MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
1）当concurrent_insert设置为0时，不允许并发插入。
2）当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
3）当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
解决方案大概有如下几种：
（1）使用--low-priority-updates启用mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。
在这种情况下，在先前情形的最后的SELECT语句将在INSERT语句前执行。
（2）为max_write_lock_count设置一个低值，使得在一定数量的WRITE锁定后，给出READ锁定
（3）使用LOW_PRIORITY属性给于一个特定的INSERT，UPDATE或DELETE较低的优先级
（4）使用HIGH_PRIORITY属性给于一个特定的SELECT
（5）使用INSERT DELAYED语句
6、My ISAM锁的调度
（1）思考？
一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁？Mysql如何处理呢？
答案：写进程先获得锁，即使读请求先到锁等待队列，写请求后到，写请起后到，写锁也会插到读锁队列。Mysql认为写请求比读请求更重要。MyISAM不太适合于有大量更新操作和查询操作的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。读的这个进程会一直等到这个写的进程完成之后，它才会得到这个请求。
（2）解决方法
执行 set low_priority_updates =1;使该连接发出的更新请求优先级降低。它的优先级降低那么实际上这个查询操作就更能获得我们这个锁。 其中insert，delete也可以通过此种方法指定。
三、InnoDB行锁和表锁
1、InnoDB行锁是通过给索引上的索引项加锁来实现的，InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
2、行锁
- 读锁：允许其他线程上读锁，但是不允许上写锁。
lock in share mode 如： select * from user where user_name='wzzf' lock in share mode - 写锁：不允许其他线程上任何锁。
注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效for update 如：select * from user where user_name='wzzf' for update
3、行锁必须要索引才能实现，否则会自动锁全表，两个事务可以用同一个索引，下面给出例子：
读锁由于不排除其他线程再加读锁比较难测试，所以下面用写锁测试，先测试没加所以字段进行加锁，对不同记录进行加锁，如果都加锁成功说明是加了行锁，反之则是默认锁全表。
（1）下面的consumer_chain_order_number是不会重复的，但没有索引
-- 马上显示查询结果 BEGIN; SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_number=26911523448454 for update -- 一直没有结束知道等待超时 BEGIN; SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_number=55181523448554 for update
说明了默认锁全表
（2）接下来试下有索引的字段--这个表的主键
BEGIN; SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_id=1 for update BEGIN; SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_id=2 for update
都查询出来了结果，说明锁住了行
4、由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
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不同的记录，但是因为使用了相同的索引，所以需要等待锁： mysql> select * from tab_with_index where id = 1 and name = '4' for update; 等待
两个事务不能锁同一个索引
事务A先执行：
select math from zje where math>60 for update;
事务B再执行：
select math from zje where math<60 for update；
这样的话，事务B是会阻塞的。如果事务B把 math索引换成其他索引就不会阻塞，
但注意，换成其他索引锁住的行不能和math索引锁住的行有重复。
5、当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
表tab_with_index的id字段有主键索引，name字段有普通索引： 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 = '2' for update; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) 由于访问的记录已经被session_1锁定，所以等待获得锁。： mysql> select * from tab_with_index where name = '4' for update;
6、即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
7、insert ，delete ， update在事务中都会自动默认加上排它锁。select则要显示加上相应的锁
由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，
否则MySQL将会执行Table Lock (将整个资料表单给锁住)。 举个例子: 假设有个表单products ，里面有id跟name二个栏位，id是主键。 例1: (明确指定主键，并且有此笔资料，row lock) SELECT * FROM products WHERE id='3' FOR UPDATE; SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE; 例2: (明确指定主键，若查无此笔资料，无lock) SELECT * FROM products WHERE id='-1' FOR UPDATE; 例2: (无主键，table lock) SELECT * FROM products WHERE name='Mouse' FOR UPDATE; 例3: (主键不明确，table lock) SELECT * FROM products WHERE id<>'3' FOR UPDATE; 例4: (主键不明确，table lock) SELECT * FROM products WHERE id LIKE '3' FOR UPDATE; 注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
假设kid 是表table 的 一个索引字段 且值不唯一 1.如果kid 有多个值为12的记录那么： update table set name=’feie’ where kid=12; 会锁表 2.如果kid有唯一的值为1的记录那么： update table set name=’feie’ where kid=1; 不会锁表 总结：用索引字段做为条件进行修改时， 是否表锁的取决于这个索引字段能否确定记录唯一， 当索引值对应记录不唯一，会进行锁表，相反则行锁。 如果有两个delete kid1 与 kid2是索引字段 语句1 delete from table where kid1=1 and kid2=2; 语句2 delete from table where kid1=1 and kid2=3; 这样的两个delete 是不会锁表的 语句1 delete from table where kid1=1 and kid2=2; 语句2 delete from table where kid1=1 ; 这样的两个delete 会锁表 总结：同一个表，如果进行删除操作时，尽量让删除条件统一，否则会相互影响造成锁表
8、意向锁
意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。
意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。
所以，可以说InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：
意向锁是InnoDB自动加的，不需用户干预。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；
三、乐观锁和悲观锁
1、乐观锁
乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。
通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
举例：
下单操作包括3步骤：
（1）查询出商品信息
select (status,status,version) from t_goods where id=#{id}
（2）根据商品信息生成订单
（3）修改商品status为2
update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};
除了自己手动实现乐观锁之外，现在网上许多框架已经封装好了乐观锁的实现
2、悲观锁
与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。
说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。
共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。
四、间隙锁（Next-Key锁）
SELECT * FROM emp WHERE empid > 100 FOR UPDATE- 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
- 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
SET AUTOCOMMIT=0; LOCAK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and here]; COMMIT; UNLOCK TABLES;
六、关于死锁
总结
- 尽量使用较低的隔离级别
- 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。
- 选择合理的事务大小，小事务发生锁冲突的几率也更小。
- 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。
- 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
- 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
- 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁。
- 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。
