9.MYSQL:MyISAM表级锁（共享读锁 & 独占写锁）串行+并发

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。下面举例说明并发操作带来的数据不一致性问题：

现有两处火车票售票点，同时读取某一趟列车车票数据库中车票余额为 X。两处售票点同时卖出一张车票，同时修改余额为 X -1写回数据库，这样就造成了实际卖出两张火车票而数据库中的记录却只少了一张。 产生这种情况的原因是因为两个事务读入同一数据并同时修改，其中一个事务提交的结果破坏了另一个事务提交的结果，导致其数据的修改被丢失，破坏了事务的隔离性。并发控制要解决的就是这类问题。

---

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，数据也是一种供许多用户共享的资源。如何保证数据访问的一致性、有效性是所有数据库的一个问题，锁冲突也是影响数据库并发访问性能的一个重要的因素，从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

---

相对于其它的数据库而言，MYSQL的锁机制比较简单的，其最显著的特点就是不同的搜索引擎具有不同的锁机制。

MYISAM和MEMORY支持表级锁;

BDB支持页级锁；

INNODB既支持表级锁，也支持行级锁，默认的是行级锁。

---

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

show status like '%table%';

得出的结果如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

---

MySQL这三种锁的特征：开销、加锁速度、死锁、粒度、并发性能
表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度最高；
页面锁：开销介于前两者之间，会出现死锁，锁定粒度介于前两者之间，并发度一般；
仅从锁的角度来说：

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

行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时仅有并发查询的应用，如一些在线事务处理（OLTP）系统。

---

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write LocK）。

1、加共享读锁

lock table 表名 read;

2、解锁

unlock tables;

3、表独占写锁

lock table 表名 write;

注意：

1）对MYISAM表的读操作，不会阻塞其他session的读请求，但是在加锁session没有unlock期间，会阻塞其它session对于该表的写请求，只有当加锁session进行unlock之后，数据库会继续执行阻塞的写操作；

2）对MyISAM表的写操作，则会阻塞其他用户对同一张表的读和写操作；

3）MyISAM表的读操作与写的操作之间，以及写操作之间是串行的！当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作，其他线程的读、写操作都会等待，直到锁被释放为止。

---

注意一点：

1) 如果要使用表的别名，记住：用别名加锁，才能使用别名存取。

2)一个session对表加共享读锁之后，这个会话可以访问加锁的表，但是在这个session访问其他的表就会报错！

---

MyISAM表的读操作与写的操作之间，以及写操作之间是串行的！当然也支持并发操作！

lock table 表名 read local;  --->并发

lock table 表名 write local;  --->并发

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

1）当concurrent_insert 设置为0时，不允许并发插入！

2）当concurrent_insert设置为1时，如果MYISAM表中之前没有过删除操作，则MYISAM表允许一个表读的时候，另一个表从表尾插入记录。这也是MYSQL的默认设置。

3）当concurrent_insert设置为2时，不管有没有删除操作，都可以表尾并发插入记录！

---

MYSQL认为写请求的优先级高于读请求！

---

概念和区别

SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)，for update之后并不会阻塞其他session的快照读取操作，除了select ...lock in share mode和select ... for update这种显示加锁的查询操作。

通过对比，发现for update的加锁方式无非是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读。

应用场景

在我看来，SELECT ... LOCK IN SHARE MODE的应用场景适合于两张表存在关系时的写操作，拿mysql官方文档的例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，那么从业务角度讲，此时我直接insert一条child_id=100记录到child表是存在风险的，因为刚insert的时候可能在parent表里删除了这条c_child_id=100的记录，那么业务数据就存在不一致的风险。正确的方法是再插入时执行select * from parent where c_child_id=100 lock in share mode,锁定了parent表的这条记录，然后执行insert into child(child_id) values (100)就ok了。

但是如果是同一张表的应用场景，举个例子，电商系统中计算一种商品的剩余数量，在产生订单之前需要确认商品数量>=1,产生订单之后应该将商品数量减1。
1 select amount from product where product_name='XX';
2 update product set amount=amount-1 where product_name='XX';

显然1的做法是是有问题，因为如果1查询出amount为1，但是这时正好其他session也买了该商品并产生了订单，那么amount就变成了0，那么这时第二步再执行就有问题。
那么采用lock in share mode可行吗，也是不合理的，因为两个session同时锁定该行记录时，这时两个session再update时必然会产生死锁导致事务回滚。以下是操作范例(按时间顺序)

session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_jjj lock in share mode;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj        |
+-----+------------+
2 rows in set (0.00 sec)

session2(同样锁定了相同的行)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_jjj lock in share mode;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj        |
+-----+------------+
2 rows in set (0.00 sec)

session1(这时session1再update时就会引起锁等待)
mysql> update test_jjj set name='jjj1' where name='jjj';

session2(这时session2同样update就会检测到死锁，回滚session2，注意执行时间不要超过session1的锁等待超时检测时间，即不要超过innodb_lock_wait_timeout设置的值)
mysql> update test_jjj set name='jjj1' where name='jjj';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

session1(此时session1执行完成)
mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 1 row affected (29.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0

通过该案例可知lock in share mode的方式在这个场景中不适用，我们需要使用for  update的方式直接加X锁，从而短暂地阻塞session2的select...for update操作;以下是操作范例

session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (0.00 sec)

session2(此时session2处于锁等待状态，得不到结果)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_jjj for update;

session1(这时session1 update之后提交，可完成)
mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

session2(session1提交之后session2刚才的查询结果就出来了，也就可以再次update往下执行了)
mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (37.19 sec)
mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (37.19 sec)

mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

通过对比，lock in share mode适用于两张表存在业务关系时的一致性要求，for  update适用于操作同一张表时的一致性要求。