mysql select for update

mysql select for update

摘要

mysql select for update

准备

CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`name_noidx` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx` (`name`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `db_test`.`product` (`id`, `name`, `name_noidx`) VALUES ('1', 'a', 'a');
INSERT INTO `db_test`.`product` (`id`, `name`, `name_noidx`) VALUES ('2', 'b', 'b');
INSERT INTO `db_test`.`product` (`id`, `name`, `name_noidx`) VALUES ('3', 'c', 'c');
INSERT INTO `db_test`.`product` (`id`, `name`, `name_noidx`) VALUES ('4', 'd', 'd');

查询隔离级别

select @@global.tx_isolation,@@tx_isolation;
REPEATABLE-READ    REPEATABLE-READ

验证有索引时for update

— idx for update , 锁单行
— idx 无for update , 无锁

SET AUTOCOMMIT=0;
BEGIN WORK;

SELECT * FROM product WHERE `name`='a' ; 
UPDATE product SET `name` = 'newname' where `name`='a' ;
COMMIT WORK;

验证无索引时for update

— 无idx for update , 锁表
— 无idx 无for update , 锁表

SET AUTOCOMMIT=0;
BEGIN WORK;

SELECT * FROM product WHERE `name_noidx`='a' ; 
UPDATE product SET `name_noidx` = 'newname' where `name_noidx`='a' ;
COMMIT WORK;

非主键字段作为条件 必须有索引并且查询条件是精确的(=) 才能行锁,否者会是表锁.

备注事务的隔离级别

事务隔离级别(名词解释)

数据库提供了四种事务隔离级别, 不同的隔离级别采用不同的锁类开来实现.
在四种隔离级别中, Serializable的级别最高, Read Uncommited级别最低.
大多数数据库的默认隔离级别为: Read Commited,如Sql Server , Oracle.
少数数据库默认的隔离级别为Repeatable Read, 如MySQL InnoDB存储引擎
即使是最低的级别,也不会出现 第一类 丢失 更新问题 .
Read Uncommited :读未提交数据( 会出现脏读,不可重复读,幻读 ,避免了 第一类丢失 更新 ) [更新时锁定表]
Read Commited :读已提交的数据(会出现不可重复读,幻读)
Repeatable Read :可重复读(会出现幻读)
Serializable :串行化

丢失 更新 :

当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新,这将导致数据丢失。
例:
事务A和事务B同时修改某行的值,
1.事务A将数值改为1并提交
2.事务B将数值改为2并提交。
这时数据的值为2,事务A所做的更新将会丢失。
解决办法:对行加锁,只允许并发一个更新事务。

脏读:

一个事务读到另一个事务未提交的更新数据(如果隔离级别使用了Read Commited,那么就能防止这种现象,不再出现读取别人没提交的数据)
例:
1.Mary的原工资为1000, 财务人员将Mary的工资改为了8000(但未提交事务)
2.Mary读取自己的工资 ,发现自己的工资变为了8000,欢天喜地!
3.而财务发现操作有误,回滚了事务,Mary的工资又变为了1000, 像这样,Mary记取的工资数8000是一个脏数据。

不可重复读:

在同一个事务中,多次读取同一数据,返回的结果有所不同. 换句话说就是,后续读取可以读到另一个事务已提交的更新数据. 相反”可重复读”在同一事务多次读取数据时,能够保证所读数据一样,也就是后续读取不能读到另一事务已提交的更新数据.
例:
1.在事务1中,Mary 读取了自己的工资为1000,操作并没有完成
2.在事务2中,这时财务人员修改了Mary的工资为2000,并提交了事务.
3.在事务1中,Mary 再次读取自己的工资时,工资变为了2000
解决办法:如果只有在修改事务完全提交之后才可以读取数据,则可以避免该问题。

幻读:

一个事务读取到另一个事务已提交的insert数据.
例:
a事务查询多条数据,b事务插入或删除数据并提交,a事务执行相同的查询,结果多出几行或少了几行