InnoDB transaction model

At first, lst's understand some technologies used in mysql transaction model:

1. Next-Key Locking

http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html 写道
InnoDB performs the row-level locking in such a way that when it searches or scans an index of a table, it sets shared or exclusive locks on the index records it encounters.

The next-key locks that InnoDB sets on index records also affect the “gap” before that index record. If a user has a shared or exclusive lock on record R in an index, another user cannot insert a new index record immediately before R in the index order.

 from the introduction, we kown that the next-key locking is the composite of normal lock and 'gap' lock. Besides locks the selected records, it also locks the 'gap'  the range index scan iterated in the index order.  The explaination may obscure. So i am going to take an example to illustrate it.

There are my testing environment:

window xp sp2
Mysql 5.0.20

create table parent
(
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `parent` VALUES (3,'p3'),(5,'p5');

(1) in the transaction one(T1 for short) execute the following SQL:

select * from parent where id > 2 for update;

 the records 3 and 5 will be displayed.

Then, in the transaction two(T2 for short) execute an update sql :

update parent set name = 'newp3' where id=3;

 If allthings goes well,  an error message will displayed after several seconds :

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 Than , execute three insert sql:

insert into parent values(1,'p1');
insert into parent values(2,'p2');
insert into parent values(4,'p4');

 the first one execute successfully and the others are fail with the same error message as listed above.

(2) restore the records,  and change the sql executed in T1 to :

select * from parent where id < 3 for update;

 then the same three sql in T2 executed again. the result reverse : the first two fail and the last success.

(3) restore the records, then in T1 execute:

select * from parent where id = 3 for update;

 after that, execute the following in T2:

insert into parent values(2,'p2');
update parent set name='new' where id=3;
insert into parent values(4,'p4');

 The result is the two insert sql success and the rest fail.

From the results of  three different  condition,  I think we can clearly realize the meaning of 'gap' lock and  rows actually be locked.

2. Consistent Read

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html 写道
A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by those transactions that committed before that point of time, and no changes made by later or uncommitted transactions.

If you are running with the default REPEATABLE READ isolation level, all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses
InnoDB uses a consistent read for select in clauses like INSERT INTO ... SELECT and UPDATE ... (SELECT) that do not specify FOR UPDATE or IN SHARE MODE if the innodb_locks_unsafe_for_binlog option is set and the isolation level of the transaction is not set to serializable.

 

Four transaction levels supported by mysql

1. READ UNCOMMITTED

SELECT statements are performed in a non-locking fashion,
may occur the problem of 'dirty read' .

2. READ COMMITTED

All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements lock only the index records, not the gaps before them

UPDATE and DELETE statements using a unique index with a unique search condition lock only the index record found

In range-type UPDATE and DELETE statements, InnoDB must set next-key or gap locks

 3. REPEATABLE READ

SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE statements that use a unique index with a unique search condition lock only the index record found

With other search conditions, these operations employ next-key locking

 4. SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE.

 In the rest of the article, i will concentrate on the two widely used isolation levels : READ COMMITTED(RC for short) and REPEATABLE READ(rr for short). introduce their characteristic compare their difference.

(1)     from the introducation of consistent read above, we kown that  it is the default mode of the two isolation levels to process SELECT statment. But there is an important different  between them :  the consistent read , in RR, with the same transaction always read the same snapshot  established by the first of read; in contrast, each consistent read set and reads its own fresh snapshot. in RC. Let's make an experiment.

In RR:

T1 :
start transaction;
select * from parent; // list 1,3,5
T2:
start transaction;
select * from parent; // list 1,3,5
delete from parent where id=5;  commit;
select * from parent; // list 1,3
T1:
select * from parent; //also list 1,3,5
In RR:

T1 :
start transaction;
T2:
start transaction;
select * from parent; // list 1,3,5
delete from parent where id=5;
select * from parent; // list 1,3
commit;
T1:
select * from parent; // list 1,3

 The different results of the two situation make it clear that every consistent read always read the snapshot established in the time point the first read executed. So only the operation result of  transaction  committed  before it can be seen.

In RC:

T1 :
start transaction;
select * from parent; // list 1,3,5
T2:
start transaction;
select * from parent; // list 1,3,5
delete from parent where id=5;
select * from parent; // list 1,3
commit;
T1:
select * from parent; // list 1,3
T2:
start transaction;
select * from parent; // list 1,3
delete from parent where id=3;
select * from parent; // list 1
commit;
T1:
select * from parent; // list 1

 Obviously, each consistent read fresh its snapshot and see the lastest committed result.

(2)      different lock mechanism is used for SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements in RC.

In RC:

T1:
select * from parent; // list 1,3,5
select * from parent where id > 2 for update; // list 3,5
T2:
select * from parent; // list 1,3,5
update parent set name='xxx' where id=3; // error
insert into parent values(4,'p4'); //success
select * from parent; // list 1,3,4,5

 Comparing the result with the example one in the first chapter,  the insert operation here work successfully even though the record id 4 is in the scope of index 'gap' scanned by the select... for update statment. That is to say, in RC,  the select...for update statment does not put  'gap' lock on table.

可以发我一个复杂一点的吗-- 用户表 CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `username` varchar(50) DEFAULT NULL COMMENT '用户名', `phone` varchar(11) NOT NULL COMMENT '手机号', `password` varchar(100) DEFAULT NULL COMMENT '密码', `wechat_openid` varchar(100) DEFAULT NULL COMMENT '微信OpenID', `credit_score` int(11) DEFAULT 100 COMMENT '信用评分', `is_verified` tinyint(1) DEFAULT 0 COMMENT '是否实名认证', `verification_type` tinyint(1) DEFAULT NULL COMMENT '认证类型(1-身份证,2-营业执照)', `verification_number` varchar(50) DEFAULT NULL COMMENT '认证号码', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `idx_phone` (`phone`), UNIQUE KEY `idx_wechat_openid` (`wechat_openid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'; -- 商品表 CREATE TABLE `product` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商品ID', `seller_id` bigint(20) NOT NULL COMMENT '卖家ID', `category_id` bigint(20) NOT NULL COMMENT '分类ID', `title` varchar(100) NOT NULL COMMENT '标题', `description` text COMMENT '描述', `original_price` decimal(10,2) DEFAULT NULL COMMENT '原价', `price` decimal(10,2) NOT NULL COMMENT '现价', `condition_level` tinyint(1) NOT NULL COMMENT '成色等级(1-全新,2-九成新,3-八成新,4-一般)', `is_disinfacted` tinyint(1) DEFAULT 0 COMMENT '是否消毒', `disinfection_certificate` varchar(255) DEFAULT NULL COMMENT '消毒证明图片', `baby_age_min` int(11) DEFAULT NULL COMMENT '适用最小月龄', `baby_age_max` int(11) DEFAULT NULL COMMENT '适用最大月龄', `status` tinyint(1) DEFAULT 1 COMMENT '状态(1-上架,2-下架,3-已售出)', `views` int(11) DEFAULT 0 COMMENT '浏览量', `likes` int(11) DEFAULT 0 COMMENT '点赞数', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_seller_id` (`seller_id`), KEY `idx_category_id` (`category_id`), KEY `idx_status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表'; -- 交易表 CREATE TABLE `transaction` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '交易ID', `buyer_id` bigint(20) NOT NULL COMMENT '买家ID', `seller_id` bigint(20) NOT NULL COMMENT '卖家ID', `product_id` bigint(20) NOT NULL COMMENT '商品ID', `price` decimal(10,2) NOT NULL COMMENT '交易价格', `status` tinyint(1) DEFAULT 1 COMMENT '状态(1-待支付,2-已支付,3-已发货,4-已收货,5-已完成,6-已取消)', `payment_method` varchar(20) DEFAULT NULL COMMENT '支付方式', `payment_time` datetime DEFAULT NULL COMMENT '支付时间', `shipping_time` datetime DEFAULT NULL COMMENT '发货时间', `receive_time` datetime DEFAULT NULL COMMENT '收货时间', `complete_time` datetime DEFAULT NULL COMMENT '完成时间', `cancel_reason` varchar(255) DEFAULT NULL COMMENT '取消原因', `logistics_company` varchar(50) DEFAULT NULL COMMENT '物流公司', `logistics_number` varchar(50) DEFAULT NULL COMMENT '物流单号', `is_self_pickup` tinyint(1) DEFAULT 0 COMMENT '是否同城自提', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_buyer_id` (`buyer_id`), KEY `idx_seller_id` (`seller_id`), KEY `idx_product_id` (`product_id`), KEY `idx_status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交易表'; -- 评价表 CREATE TABLE `evaluation` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '评价ID', `transaction_id` bigint(20) NOT NULL COMMENT '交易ID', `reviewer_id` bigint(20) NOT NULL COMMENT '评价者ID', `reviewee_id` bigint(20) NOT NULL COMMENT '被评价者ID', `product_id` bigint(20) NOT NULL COMMENT '商品ID', `rating` tinyint(1) NOT NULL COMMENT '评分(1-5)', `content` text COMMENT '评价内容', `images` varchar(500) DEFAULT NULL COMMENT '评价图片,多个图片用逗号分隔', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `idx_transaction_id` (`transaction_id`), KEY `idx_reviewer_id` (`reviewer_id`), KEY `idx_reviewee_id` (`reviewee_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评价表'; -- 商品分类表 CREATE TABLE `category` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '分类ID', `parent_id` bigint(20) DEFAULT 0 COMMENT '父分类ID', `name` varchar(50) NOT NULL COMMENT '分类名称', `description` varchar(255) DEFAULT NULL COMMENT '分类描述', `icon` varchar(255) DEFAULT NULL COMMENT '分类图标', `sort` int(11) DEFAULT 0 COMMENT '排序', `is_leaf` tinyint(1) DEFAULT 0 COMMENT '是否叶子节点', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_parent_id` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表'; -- 收藏表 CREATE TABLE `collection` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '收藏ID', `user_id` bigint(20) NOT NULL COMMENT '用户ID', `product_id` bigint(20) NOT NULL COMMENT '商品ID', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `idx_user_product` (`user_id`,`product_id`), KEY `idx_product_id` (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收藏表'; -- 育儿知识表 CREATE TABLE `knowledge` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '知识ID', `title` varchar(100) NOT NULL COMMENT '标题', `content` text NOT NULL COMMENT '内容', `category` varchar(50) DEFAULT NULL COMMENT '分类', `baby_age_min` int(11) DEFAULT NULL COMMENT '适用最小月龄', `baby_age_max` int(11) DEFAULT NULL COMMENT '适用最大月龄', `author` varchar(50) DEFAULT NULL COMMENT '作者', `source` varchar(100) DEFAULT NULL COMMENT '来源', `views` int(11) DEFAULT 0 COMMENT '浏览量', `likes` int(11) DEFAULT 0 COMMENT '点赞数', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_category` (`category`), KEY `idx_baby_age` (`baby_age_min`,`baby_age_max`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='育儿知识表'; 数据库有这些
最新发布
06-18
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值