SQL锁问题

锁分类

（1）表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。 [MYISAM、Memory、Innodb]

    表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用

（2）行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。[Innodb]

行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用

（3）页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁 之间，并发度一般。[BDB]

MYISAM表级锁

锁争用

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979 |
| Table_locks_waited    | 0    |
+-----------------------+-------+
####Table_locks_waits值高则代表严重的表级锁争用

锁模式

（1）表共享读锁：可以多个用户共同读
（2）表独占写锁：写操作独占
一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止

如何加表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁
在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁

并发插入

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

•  当concurrent_insert设置为0时，不允许并发插入。
•  当concurrent_insert设置为1时，如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时, 另一个进程从表尾插入记录。这也是MySQL 的默认设置。
•  当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

锁调度

一个进程请求某个 MyISAM 表的读锁，同时另一个进程也请求同一表的写锁，写进程先获得锁。

一些设置来调节 MyISAM 的调度行为。 
     通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
     通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
     通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

InnoDB行锁

锁争用

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   |
+-------------------------------+-------+
###InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值
比较高，代表严重的锁争用

锁模式

共享锁：允许一个事务去读一行，阻止其他事务获得该行的排他锁

• select … lock in share mode;

排他锁：允许获得排他锁的事务更新数据，阻止其他事务获得该行的共享读锁和排他写锁

• select … for update;

	X(排他锁)	IX(意向排他锁)	S(共享锁)	IS(意向共享锁)
X(排他锁)	冲突	冲突	冲突	冲突
IX(意向排他锁)	冲突	兼容	冲突	兼容
S(共享锁)	冲突	冲突	兼容	兼容
IS(意向共享锁)	冲突	兼容	兼容	兼容

 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁.另外，为了允许行锁和表锁共存，
   实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁

锁实现方式

只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

（1）在不通过索引条件查询的时候，InnoDB 确实使用的是表锁，而不是行锁。
（2）由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行.另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁
（4）如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

表锁使用场景

• (1)事务需要更新大部分或全部数据
• (2)事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的 开销

避免死锁方法

• （1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表

• （2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

• （3）隔离级别

  在 REPEATABLE-READ 隔离级别下，如果两个线程同时对相同条件记录用 SELECT…FOR UPDATE 加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录, 如果两个线程都这么做，就会出现死锁.这种情况下，将隔离级别改成 READ COMMITTED，就可避免问题，