mybatis mysql 乐观锁_MyBatis实现乐观锁和悲观锁

使用mysql做数据库，mybatis做orm的系统中，mybatis的乐观锁和悲观锁实际上就是mysql的乐观锁和悲观锁。

实例中使用springboot整合mybatis，一并记录了。

添加依赖：

mysql

mysql-connector-java

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.1.1

application.properties配置：

mybatis.type-aliases-package=com.lin.learn.mysql

spring.datasource.driverClassName = com.mysql.jdbc.Driver

spring.datasource.url = jdbc:mysql://192.168.0.103:3306/mysql?useUnicode=true&characterEncoding=utf-8

spring.datasource.username = root

spring.datasource.password = root

实体类：

public class TestEntity {

private int id;

private int count;

private int version;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public int getCount() {

return count;

}

public void setCount(int count) {

this.count = count;

}

public int getVersion() {

return version;

}

public void setVersion(int version) {

this.version = version;

}

}

加上相关注解：

@SpringBootApplication

@MapperScan("com.lin.learn.mysql")

public class App {

public static void main(String[] args) {

ApplicationContext applicationContext = SpringApplication.run(App.class, args);

}

}

乐观锁

就是使用一个version字段标识数据的当前版本，每次更新数据的时候同时更新version = version + 1，where条件中需要加上version等于当前事务查询出的数据的version，如果version的值已经改变，则更新失败。

Mapper：

public interface TestMapper {

@Select("select * from `test` where `id` = #{id} for update")

@Results({

@Result(column = "id", property = "id", javaType = Integer.class),

@Result(column = "count", property = "count", javaType = Integer.class),

@Result(column = "version", property = "version", javaType = Integer.class)

})

public TestEntity getById(@Param("id") int id);

@Update("update `test` set `count` = #{count}, `version` = #{version} + 1 where `id` = #{id} and `version` = #{version}")

public int update(TestEntity testEntity);

}

业务代码，就是数据库一个数据修改：

import org.springframework.stereotype.Component;

import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

@Component

public class TestService {

@Resource

private TestMapper mapper;

public void increaseCount(int id) {

while (true) {

try {

TestEntity testEntity = mapper.getById(id);

testEntity.setCount(testEntity.getCount() + 1);

int updateCount = mapper.update(testEntity);

if(updateCount > 0) {

break;

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

}

测试：

import com.lin.learn.mysql.TestService;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.context.ApplicationContext;

@SpringBootApplication

@MapperScan("com.lin.learn.mysql")

public class App {

public static void main(String[] args) {

ApplicationContext applicationContext = SpringApplication.run(App.class, args);

TestService service = applicationContext.getBean(TestService.class);

new Thread(new Runnable() {

@Override

public void run() {

for(int i = 0; i < 10; i++) {

service.increaseCount(3);

}

}

}).start();

new Thread(new Runnable() {

@Override

public void run() {

for(int i = 0; i < 10; i++) {

service.increaseCount(3);

}

}

}).start();

new Thread(new Runnable() {

@Override

public void run() {

for(int i = 0; i < 10; i++) {

service.increaseCount(3);

}

}

}).start();

}

}

上面代码执行后数据库数据最终结果得到正确的数据。

悲观锁

本质上就是用select for update锁行，需要注意的是如果where子句条件没有命中索引将导致锁表。并且查询和更新操作都需要在同一个事务里里面。

在Mapper添加新的更新方法：

import org.apache.ibatis.annotations.*;

public interface TestMapper {

@Select("select * from `test` where `id` = #{id} for update")

@Results({

@Result(column = "id", property = "id", javaType = Integer.class),

@Result(column = "count", property = "count", javaType = Integer.class),

@Result(column = "version", property = "version", javaType = Integer.class)

})

public TestEntity getById(@Param("id") int id);

@Update("update `test` set `count` = #{count}, `version` = #{version} + 1 where `id` = #{id} and `version` = #{version}")

public int update(TestEntity testEntity);

@Update("update `test` set `count` = #{count} where `id` = #{id}")

public void updateNoSafe(TestEntity testEntity);

}

在业务代码里面添加新的方法：

import org.springframework.stereotype.Component;

import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

@Component

public class TestService {

@Resource

private TestMapper mapper;

public void increaseCount(int id) {

while (true) {

try {

TestEntity testEntity = mapper.getById(id);

testEntity.setCount(testEntity.getCount() + 1);

int updateCount = mapper.update(testEntity);

if(updateCount > 0) {

break;

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

@Transactional

public void increaseNoSafe(int id) {

try {

TestEntity testEntity = mapper.getById(id);

testEntity.setCount(testEntity.getCount() + 1);

mapper.updateNoSafe(testEntity);

} catch (Exception e) {

e.printStackTrace();

}

}

}

这里是用@Transaction注解声明这个更新数据的事务。

测试：

import com.lin.learn.mysql.TestService;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.context.ApplicationContext;

@SpringBootApplication

@MapperScan("com.lin.learn.mysql")

public class App {

public static void main(String[] args) {

ApplicationContext applicationContext = SpringApplication.run(App.class, args);

TestService service = applicationContext.getBean(TestService.class);

new Thread(new Runnable() {

@Override

public void run() {

for(int i = 0; i < 10; i++) {

service.increaseNoSafe(3);

}

}

}).start();

new Thread(new Runnable() {

@Override

public void run() {

for(int i = 0; i < 10; i++) {

service.increaseNoSafe(3);

}

}

}).start();

new Thread(new Runnable() {

@Override

public void run() {

for(int i = 0; i < 10; i++) {

service.increaseNoSafe(3);

}

}

}).start();

}

}

最终结果能得到正确的数据。