使用mysql做数据库,mybatis做orm的系统中,mybatis的乐观锁和悲观锁实际上就是mysql的乐观锁和悲观锁。
实例中使用springboot整合mybatis,一并记录了。
添加依赖:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
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();
}
}
最终结果能得到正确的数据。