介绍
mysql乐观锁是mvcc的一种实现。
mvcc就是multiple version concurrent control。
所谓乐观锁,就是假设数据不会冲突。所以你可以尽情地update,submit。如果发现有并发问题了,mysql可以将失败结果返回给用户。
悲观锁就是一开始就要加锁,心情很悲观。
我们知道innodb有行锁,两个用户同时update一条数据的时候,稍慢的用户会阻塞等待,直到抢到锁的事务commit之后,他才能修改。
这里的等待是很耗性能的。
利用一个版本控制,就可以摆脱多事务之间的依赖性,使得事务很快,因为它永远不会阻塞。
例子
sql:
create table t_goods(
id int(11) primary key auto_increment,
status int(11) not null,
name varchar(50) not null,
version int(11) not null
)engine=innodb,charset=utf8
insert into t_goods values(null,1,'TV',1);
insert into t_goods values(null,2,'PC',2);
注意这里的version
字段就是我们的版本控制。
实体类:
@Alias("goods")
public class Goods {
private int id;
private int status;
private String name;
private int version;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getVersion() {
return version;
}
public void setVersion(int version) {
this.version = version;
}
@Override
public String toString() {
return "Goods{" +
"id=" + id +
", status=" + status +
", name='" + name + '\'' +
", version=" + version +
'}';
}
}
mapper:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.mybatis.lecture.dao.GoodsMapper">
<update id="updateGoodsUseCAS">
update t_goods
set status=#{status},name=#{name},version=version+1
where id=#{id} and version=#{version}
</update>
<!-- Goods getGoodsById(int id);-->
<select id="getGoodsById" resultType="goods">
select * from t_goods where id=#{id}
</select>
</mapper>
接口:
public interface GoodsMapper {
Integer updateGoodsUseCAS(Goods goods);
Goods getGoodsById(int id);
}
测试:
public class TestGoods {
public SqlSessionFactory getsqlSessionFactory() throws IOException {
String resources = "mybatis-config.xml";
InputStream resourceAsStream = Resources.getResourceAsStream(resources);
return new SqlSessionFactoryBuilder().build(resourceAsStream);
}
@Test
public void goodsDaoTest() throws Exception{
SqlSessionFactory sqlSessionFactory = getsqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
GoodsMapper goodsDao = sqlSession.getMapper(GoodsMapper.class);
int goodsId = 1;
//According to the same id, the product information is queried and assigned to two objects.
Goods goods1 = goodsDao.getGoodsById(goodsId);
Goods goods2 = goodsDao.getGoodsById(goodsId);
//Print current merchandise information
System.out.println(goods1);
System.out.println(goods2);
//Update Commodity Information 1
goods1.setStatus(2);//Modify status 2
int updateResult1 = goodsDao.updateGoodsUseCAS(goods1);
System.out.println("Modification of Commodity Information 1 " + (updateResult1 == 1 ? "Success" : "fail"));
//Update Commodity Information 2
goods2.setStatus(2);//Modify status 2
int updateResult2 = goodsDao.updateGoodsUseCAS(goods2);
System.out.println("Modifying Commodity Information 2 " + (updateResult2 == 1 ? "Success" : "fail"));
sqlSession.commit();
}
}
id为1的数据我们取两次,赋给good1和good2。
DEBUG 06-30 20:28:00,315 ==> Preparing: select * from t_goods where id=? (BaseJdbcLogger.java:143)
DEBUG 06-30 20:28:00,413 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:143)
DEBUG 06-30 20:28:00,488 <== Total: 1 (BaseJdbcLogger.java:143)
Goods{id=1, status=1, name='TV', version=1}
Goods{id=1, status=1, name='TV', version=1}
有两人都想将数据1的status改成2,但是只有一个人会成功。
其中一人的sql:
DEBUG 06-30 20:28:00,495 ==> Preparing: update t_goods set status=?,name=?,version=version+1 where id=? and version=? (BaseJdbcLogger.java:143)
DEBUG 06-30 20:28:00,496 ==> Parameters: 2(Integer), TV(String), 1(Integer), 1(Integer) (BaseJdbcLogger.java:143)
另一个人的sql:
DEBUG 06-30 20:28:00,526 ==> Preparing: update t_goods set status=?,name=?,version=version+1 where id=? and version=? (BaseJdbcLogger.java:143)
DEBUG 06-30 20:28:00,528 ==> Parameters: 2(Integer), TV(String), 1(Integer), 1(Integer) (BaseJdbcLogger.java:143)
完全是一模一样的。
但是一个人成功了:
DEBUG 06-30 20:28:00,525 <== Updates: 1 (BaseJdbcLogger.java:143)
Modification of Commodity Information 1 Success
另一个人失败了:
DEBUG 06-30 20:28:00,531 <== Updates: 0 (BaseJdbcLogger.java:143)
Modifying Commodity Information 2 fail
因为成功的那个人把版本号加1了,所以第二个人update的时候sql是错的,因此失败。
这里,看起来就好像没有锁一样。