MYSQL的悲观锁for update

FOR UPDATE 的作用是在查询的时候为行加上排它锁，当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。
针对金钱、库存等对数据的准确性有较高要求，记住一个原则：一锁二判三更新。

1、MYSQL的悲观锁实践表结构设计

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- Table structure for payment
DROP TABLE IF EXISTS `payment`;
CREATE TABLE `payment`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `serial` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `idx` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `payment_idx`(`idx`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

-- Records of payment
INSERT INTO `payment`(id, serial, idx) VALUES (1, '111111', '11');
INSERT INTO `payment`(id, serial, idx) VALUES (2, '222222', '22');
INSERT INTO `payment`(id, serial, idx) VALUES (3, '333333', '33');


set autocommit = 0; start transaction;
sql语句:如select * from payment for update;
commit; / rollback;

2、明确指定[主键/索引]，查有数据，ROW_LOCK

id非键锁行：
主键：
select * from payment where id=1 for update;
主键：
select * from payment where id=1 for update;
select * from payment where id=2 for update;

3、明确指定[普通字段]，查有数据，TABLE_LOCK

此处与Oracle不同，Oracle为 ROW_LOCK

serial非主键锁表：
链接1：
select * from payment where serial=111111 for update;
链接2：
select * from payment where serial=222222 for update;
select * from payment where id=2 for update;

4、明确指定[主键/索引/字段]，查无数据，NO_LOCK

select * from payment where id=99 for update;

5、无条件查询，TABLE_LOCK

select * from payment for update;