Performance optimization of database 2 – transactions and locks

MySQL transaction

lock

Pessimistic lock and optimistic lock

CREATE TABLE `tb_goods_stock` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',`goods_id` bigint(20) unsigned DEFAULT '0' COMMENT '商品ID',`nums` int(11) unsigned DEFAULT '0' COMMENT '商品库存数量',`create_time` datetime DEFAULT NULL COMMENT '创建时间',`modify_time` datetime DEFAULT NULL COMMENT '更新时间',PRIMARY KEY (`id`),UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品库存表';

BEGIN;
SELECT nums FROM tb_goods_stock WHERE id=1 FOR UPDATE;

UPDATE tb_goods_stock SET nums=nums-5 WHERE id=1 AND nums>5;
COMMIT;

CREATE TABLE `tb_goods_stock` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',`goods_id` BIGINT(20) UNSIGNED DEFAULT '0' COMMENT '商品ID',`nums` INT(11) UNSIGNED DEFAULT '0' COMMENT '商品库存数量',`create_time` DATETIME DEFAULT NULL COMMENT '创建时间',`modify_time` DATETIME DEFAULT NULL COMMENT '更新时间',`version` BIGINT(20) UNSIGNED DEFAULT '0' COMMENT '版本号',UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='商品库存表';

BEGIN;
SELECT nums,VERSION FROM tb_goods_stock WHERE id=1;

UPDATE tb_goods_stock SET nums=nums-5,VERSION=VERSION+1 WHERE id=1 AND nums>5 AND VERSION=0;

journal

Mvcc multi version concurrency control

Optimize actual combat

insert into persons 
(id_p,lastname,firstName,city )
values
(200,'haha','deng','shenzhen'),(201,'haha2','GD'),(202,'haha3','Beijing');