行锁与表锁
不同的存储引擎支持不同的锁机制。
MyISAM只支持表锁，InnoDB支持行锁也支持表锁。
开销、加锁速度、死锁、粒度、并发性能比较
-
行锁
开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。 -
表锁
开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。
表锁更适用于以查询为主，只有少量按索引条件更新数据的应用；
行锁更适用于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。
InnoDB的行锁模式及加锁方法
InnoDB实现了以下两种类型的行锁。
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
排他锁的申请前提：没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；
事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。
InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
查询InnoDb锁争用情况
- 显示哪些线程正在运行
mysql> show processlist;
+------+-----------------+-------------------+--------+---------+--------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+-------------------+--------+---------+--------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 508202 | Waiting on empty queue | NULL |
| 2586 | root | 192.168.x.8:39360 | test | Query | 0 | starting | show processlist |
| 2591 | root | 192.168.x.1:33651 | halodb | Sleep | 1739 | | NULL |
+------+-----------------+-------------------+--------+---------+--------+------------------------+------------------+
- 查询行锁争用
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 51005 |
| Innodb_row_lock_time_avg | 25502 |
| Innodb_row_lock_time_max | 51005 |
| Innodb_row_lock_waits | 2 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
- 查看服务器状态
mysql> show status like '%lock%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Com_lock_instance | 0 |
+------------------------------------------+-------+
- 查看innodb引擎的运行时信息
show engine innodb status\G
......
---TRANSACTION 5222, ACTIVE 78 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2607, OS thread handle 139934196270848, query id 681337 192.168.x.10 root
---TRANSACTION 5221, ACTIVE 157 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2586, OS thread handle 139934334068480, query id 681346 192.168.x.8 root starting
show engine innodb status
......
- 查看服务器timeout配置参数
mysql> show variables like '%timeout%';
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
+-----------------------------------+----------+
实际应用分析
举一个商品扣减库存的例子。
初始化表sql
CREATE TABLE `goods` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`stock` int(11) DEFAULT NULL,#库存量
PRIMARY KEY (`id`),
UNIQUE KEY `idx_name` (`name`) USING HASH
) ENGINE=InnoDB;
INSERT INTO `goods` VALUES ('1', 'prod11', '1000');
INSERT INTO `goods` VALUES ('2', 'prod12', '1000');
INSERT INTO `goods` VALUES ('3', 'prod13', '1000');
INSERT INTO `goods` VALUES ('4', 'prod14', '1000');
INSERT INTO `goods` VALUES ('5', 'prod15', '1000');
INSERT INTO `goods` VALUES ('6', 'prod16', '1000');
INSERT INTO `goods` VALUES ('7', 'prod17', '1000');
INSERT INTO `goods` VALUES ('8', 'prod18', '1000');
INSERT INTO `goods` VALUES ('9', 'prod19', '1000');
数据不一致问题
两个用户A、B同时购买id=1的商品，商品库存同时扣减1，那么此时剩余库存应该是多少呢？
可以用加锁的方式解决这种并发问题
- 悲观锁
用户A获取获取 id=1 的商品信息时对该行记录加锁，期间其他用户阻塞等待访问该记录。
悲观锁适合写入频繁的场景。
执行逻辑
begin;
select * from goods where id = 1 for update;
update goods set stock = stock - 1 where id = 1;
commit;
A:
#事务开始
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#加锁
mysql> select * from goods where id = 1 for update;
+----+--------+-------+
| id | name | stock |
+----+--------+-------+
| 1 | prod11 | 997 |
+----+--------+-------+
1 row in set (0.00 sec)
#此时B开始执行update goods set stock = stock - 1 where id = 1;
#可以看到现在Innodb_row_lock_current_waits锁等待value是1
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 97682 |
| Innodb_row_lock_time_avg | 32560 |
| Innodb_row_lock_time_max | 51005 |
| Innodb_row_lock_waits | 3 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
mysql> update goods set stock = stock - 1 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#事务提交结束
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
#B锁等待结束，执行完毕。Innodb_row_lock_current_waits值变为0
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 119400 |
| Innodb_row_lock_time_avg | 39800 |
| Innodb_row_lock_time_max | 51005 |
| Innodb_row_lock_waits | 3 |
+-------------------------------+--------+
5 rows in set (0.01 sec)
B:
mysql> update goods set stock = stock - 1 where id = 1;
Query OK, 1 row affected (21.73 sec) # 因为存在锁等待，执行时间很长
Rows matched: 1 Changed: 1 Warnings: 0
- 乐观锁
每次获取商品时，不对该商品加锁。
在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新，反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新。
乐观锁适合读取频繁的场景。
执行逻辑
#不加锁获取 id=1 的商品对象
select * from goods where id = 1
begin;
#更新 stock 值，这里需要注意 where 条件 “stock = cur_stock”，只有程序中获取到的库存量与数据库中的库存量相等才执行更新
update goods set stock = stock - 1 where id = 1 and stock = cur_stock;
commit;
如果我们需要设计一个商城系统，会选择上面哪种方案呢？
因为查询商品的频率比下单支付的频次高得多，所以我们选择乐观锁方案会更好！