MySQL使用版本号实现乐观锁

原创转载请注明出处：https://www.cnblogs.com/agilestyle/p/11608581.html

乐观锁适用于读多写少的应用场景

乐观锁Version图示

Project Directory

Maven Dependency

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <project xmlns="http://maven.apache.org/POM/4.0.0"
 3          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 5     <modelVersion>4.0.0</modelVersion>
 6 
 7     <groupId>HelloSpring</groupId>
 8     <artifactId>org.fool.spring</artifactId>
 9     <version>1.0-SNAPSHOT</version>
10 
11     <parent>
12         <groupId>org.springframework.boot</groupId>
13         <artifactId>spring-boot-starter-parent</artifactId>
14         <version>1.5.22.RELEASE</version>
15     </parent>
16 
17     <dependencies>
18         <dependency>
19             <groupId>org.springframework.boot</groupId>
20             <artifactId>spring-boot-starter-web</artifactId>
21             <exclusions>
22                 <exclusion>
23                     <groupId>org.springframework.boot</groupId>
24                     <artifactId>spring-boot-starter-tomcat</artifactId>
25                 </exclusion>
26             </exclusions>
27         </dependency>
28         <dependency>
29             <groupId>org.springframework.boot</groupId>
30             <artifactId>spring-boot-starter-jetty</artifactId>
31             <exclusions>
32                 <exclusion>
33                     <groupId>org.eclipse.jetty.websocket</groupId>
34                     <artifactId>websocket-server</artifactId>
35                 </exclusion>
36                 <exclusion>
37                     <groupId>org.eclipse.jetty.websocket</groupId>
38                     <artifactId>javax-websocket-server-impl</artifactId>
39                 </exclusion>
40             </exclusions>
41         </dependency>
42 
43         <dependency>
44             <groupId>org.mybatis.spring.boot</groupId>
45             <artifactId>mybatis-spring-boot-starter</artifactId>
46             <version>1.3.5</version>
47         </dependency>
48 
49         <dependency>
50             <groupId>com.alibaba</groupId>
51             <artifactId>druid-spring-boot-starter</artifactId>
52             <version>1.1.20</version>
53         </dependency>
54 
55         <dependency>
56             <groupId>mysql</groupId>
57             <artifactId>mysql-connector-java</artifactId>
58         </dependency>
59 
60         <dependency>
61             <groupId>org.springframework.boot</groupId>
62             <artifactId>spring-boot-starter-test</artifactId>
63             <scope>test</scope>
64         </dependency>
65 
66         <dependency>
67             <groupId>org.mybatis.generator</groupId>
68             <artifactId>mybatis-generator-core</artifactId>
69             <version>1.3.7</version>
70             <scope>test</scope>
71         </dependency>
72     </dependencies>
73 
74     <build>
75         <plugins>
76             <plugin>
77                 <groupId>org.springframework.boot</groupId>
78                 <artifactId>spring-boot-maven-plugin</artifactId>
79             </plugin>
80             <plugin>
81                 <groupId>org.mybatis.generator</groupId>
82                 <artifactId>mybatis-generator-maven-plugin</artifactId>
83                 <version>1.3.7</version>
84                 <configuration>
85                     <configurationFile>sql/generatorConfig.xml</configurationFile>
86                     <verbose>true</verbose>
87                     <overwrite>true</overwrite>
88                 </configuration>
89             </plugin>
90         </plugins>
91     </build>
92 </project>

View Code

application.properties

1 server.port=9999
2 
3 spring.datasource.url=jdbc:mysql://localhost:3306/test
4 spring.datasource.username=root
5 spring.datasource.password=123456
6 spring.datasource.driver-class-name=com.mysql.jdbc.Driver
7 
8 mybatis.type-aliases-package=org.fool.spring.model
9 mybatis.mapper-locations=classpath:mapper/**/*.xml

ddl.sql

 1 CREATE TABLE `goods` (
 2   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 3   `name` varchar(32) NOT NULL COMMENT '商品名称',
 4   `state` tinyint(4) unsigned NOT NULL COMMENT '1.正常；2.缺货',
 5   `version` bigint(20) unsigned NOT NULL,
 6   `create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
 7   `update_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
 8   PRIMARY KEY (`id`)
 9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
10 
11 insert into goods(name, state, version) values('iPhone', 1, 1);
12 insert into goods(name, state, version) values('iMac', 1, 1);
13 insert into goods(name, state, version) values('iPad', 1, 1);
14 insert into goods(name, state, version) values('iWatch', 1, 1);

generatorConfig.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE generatorConfiguration
 3         PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
 4         "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
 5 <generatorConfiguration>
 6     <classPathEntry
 7             location="/Users/${user.name}/.m2/repository/mysql/mysql-connector-java/5.1.48/mysql-connector-java-5.1.48.jar"/>
 8 
 9     <context id="test" targetRuntime="MyBatis3">
10 
11         <plugin type="org.mybatis.generator.plugins.ToStringPlugin"/>
12         <plugin type="org.mybatis.generator.plugins.UnmergeableXmlMappersPlugin"/>
13 
14         <commentGenerator>
15             <property name="suppressAllComments" value="true"/>
16         </commentGenerator>
17 
18         <jdbcConnection driverClass="com.mysql.jdbc.Driver"
19                         connectionURL="jdbc:mysql://localhost:3306/test"
20                         userId="root"
21                         password="123456">
22         </jdbcConnection>
23 
24         <javaModelGenerator targetPackage="org.fool.spring.model" targetProject="src/main/java">
25             <property name="enableSubPackages" value="true"/>
26             <property name="trimStrings" value="true"/>
27         </javaModelGenerator>
28 
29         <sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources">
30             <property name="enableSubPackages" value="true"/>
31         </sqlMapGenerator>
32 
33         <javaClientGenerator targetPackage="org.fool.spring.dao.mapper" targetProject="src/main/java"
34                              type="XMLMAPPER">
35             <property name="enableSubPackages" value="true"/>
36         </javaClientGenerator>
37 
38         <table tableName="goods" domainObjectName="Goods"
39                enableCountByExample="false" enableUpdateByExample="false"
40                enableDeleteByExample="false" enableSelectByExample="false"
41                selectByExampleQueryId="false">
42         </table>
43 
44     </context>
45 
46 </generatorConfiguration>

View Code

自动生成model和mapper

1 mybatis-generator:generate -e

Goods.java

 1 package org.fool.spring.model;
 2 
 3 import java.util.Date;
 4 
 5 public class Goods {
 6     private Long id;
 7 
 8     private String name;
 9 
10     private Byte state;
11 
12     private Long version;
13 
14     private Date createTime;
15 
16     private Date updateTime;
17 
18     public Long getId() {
19         return id;
20     }
21 
22     public void setId(Long id) {
23         this.id = id;
24     }
25 
26     public String getName() {
27         return name;
28     }
29 
30     public void setName(String name) {
31         this.name = name == null ? null : name.trim();
32     }
33 
34     public Byte getState() {
35         return state;
36     }
37 
38     public void setState(Byte state) {
39         this.state = state;
40     }
41 
42     public Long getVersion() {
43         return version;
44     }
45 
46     public void setVersion(Long version) {
47         this.version = version;
48     }
49 
50     public Date getCreateTime() {
51         return createTime;
52     }
53 
54     public void setCreateTime(Date createTime) {
55         this.createTime = createTime;
56     }
57 
58     public Date getUpdateTime() {
59         return updateTime;
60     }
61 
62     public void setUpdateTime(Date updateTime) {
63         this.updateTime = updateTime;
64     }
65 
66     @Override
67     public String toString() {
68         StringBuilder sb = new StringBuilder();
69         sb.append(getClass().getSimpleName());
70         sb.append(" [");
71         sb.append("Hash = ").append(hashCode());
72         sb.append(", id=").append(id);
73         sb.append(", name=").append(name);
74         sb.append(", state=").append(state);
75         sb.append(", version=").append(version);
76         sb.append(", createTime=").append(createTime);
77         sb.append(", updateTime=").append(updateTime);
78         sb.append("]");
79         return sb.toString();
80     }
81 }

View Code

GoodsMapper.java

 1 package org.fool.spring.dao.mapper;
 2 
 3 import org.fool.spring.model.Goods;
 4 
 5 public interface GoodsMapper {
 6     int deleteByPrimaryKey(Long id);
 7 
 8     int insert(Goods record);
 9 
10     int insertSelective(Goods record);
11 
12     Goods selectByPrimaryKey(Long id);
13 
14     int updateByPrimaryKeySelective(Goods record);
15 
16     int updateByPrimaryKey(Goods record);
17 }

View Code

GoodsMapper.xml

  1 <?xml version="1.0" encoding="UTF-8"?>
  2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  3 <mapper namespace="org.fool.spring.dao.mapper.GoodsMapper">
  4   <resultMap id="BaseResultMap" type="org.fool.spring.model.Goods">
  5     <id column="id" jdbcType="BIGINT" property="id" />
  6     <result column="name" jdbcType="VARCHAR" property="name" />
  7     <result column="state" jdbcType="TINYINT" property="state" />
  8     <result column="version" jdbcType="BIGINT" property="version" />
  9     <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
 10     <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
 11   </resultMap>
 12   <sql id="Base_Column_List">
 13     id, name, state, version, create_time, update_time
 14   </sql>
 15   <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
 16     select 
 17     <include refid="Base_Column_List" />
 18     from goods
 19     where id = #{id,jdbcType=BIGINT}
 20   </select>
 21   <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
 22     delete from goods
 23     where id = #{id,jdbcType=BIGINT}
 24   </delete>
 25   <insert id="insert" parameterType="org.fool.spring.model.Goods">
 26     insert into goods (id, name, state, 
 27       version, create_time, update_time
 28       )
 29     values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{state,jdbcType=TINYINT}, 
 30       #{version,jdbcType=BIGINT}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP}
 31       )
 32   </insert>
 33   <insert id="insertSelective" parameterType="org.fool.spring.model.Goods">
 34     insert into goods
 35     <trim prefix="(" suffix=")" suffixOverrides=",">
 36       <if test="id != null">
 37         id,
 38       </if>
 39       <if test="name != null">
 40         name,
 41       </if>
 42       <if test="state != null">
 43         state,
 44       </if>
 45       <if test="version != null">
 46         version,
 47       </if>
 48       <if test="createTime != null">
 49         create_time,
 50       </if>
 51       <if test="updateTime != null">
 52         update_time,
 53       </if>
 54     </trim>
 55     <trim prefix="values (" suffix=")" suffixOverrides=",">
 56       <if test="id != null">
 57         #{id,jdbcType=BIGINT},
 58       </if>
 59       <if test="name != null">
 60         #{name,jdbcType=VARCHAR},
 61       </if>
 62       <if test="state != null">
 63         #{state,jdbcType=TINYINT},
 64       </if>
 65       <if test="version != null">
 66         #{version,jdbcType=BIGINT},
 67       </if>
 68       <if test="createTime != null">
 69         #{createTime,jdbcType=TIMESTAMP},
 70       </if>
 71       <if test="updateTime != null">
 72         #{updateTime,jdbcType=TIMESTAMP},
 73       </if>
 74     </trim>
 75   </insert>
 76   <update id="updateByPrimaryKeySelective" parameterType="org.fool.spring.model.Goods">
 77     update goods
 78     <set>
 79       <if test="name != null">
 80         name = #{name,jdbcType=VARCHAR},
 81       </if>
 82       <if test="state != null">
 83         state = #{state,jdbcType=TINYINT},
 84       </if>
 85       <if test="version != null">
 86         version = #{version,jdbcType=BIGINT},
 87       </if>
 88       <if test="createTime != null">
 89         create_time = #{createTime,jdbcType=TIMESTAMP},
 90       </if>
 91       <if test="updateTime != null">
 92         update_time = #{updateTime,jdbcType=TIMESTAMP},
 93       </if>
 94     </set>
 95     where id = #{id,jdbcType=BIGINT}
 96   </update>
 97   <update id="updateByPrimaryKey" parameterType="org.fool.spring.model.Goods">
 98     update goods
 99     set name = #{name,jdbcType=VARCHAR},
100       state = #{state,jdbcType=TINYINT},
101       version = #{version,jdbcType=BIGINT},
102       create_time = #{createTime,jdbcType=TIMESTAMP},
103       update_time = #{updateTime,jdbcType=TIMESTAMP}
104     where id = #{id,jdbcType=BIGINT}
105   </update>
106 </mapper>

View Code

GoodsMapperExt.java

1 package org.fool.spring.dao.mapper.extension;
2 
3 import org.fool.spring.model.Goods;
4 
5 public interface GoodsMapperExt {
6     int casUpdateByPrimaryKey(Goods goods);
7 }

GoodsMapperExt.xml

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <mapper namespace="org.fool.spring.dao.mapper.extension.GoodsMapperExt">
 4     <resultMap id="BaseResultMap" type="org.fool.spring.model.Goods">
 5         <id column="id" jdbcType="BIGINT" property="id"/>
 6         <result column="name" jdbcType="VARCHAR" property="name"/>
 7         <result column="state" jdbcType="TINYINT" property="state"/>
 8         <result column="version" jdbcType="BIGINT" property="version"/>
 9         <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
10         <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
11     </resultMap>
12     <sql id="Base_Column_List">
13         id, name, state, version, create_time, update_time
14     </sql>
15     <update id="casUpdateByPrimaryKey" parameterType="org.fool.spring.model.Goods">
16         update goods
17         set name = #{name,jdbcType=VARCHAR},
18         state = #{state,jdbcType=TINYINT},
19         version = version + 1
20         where id = #{id,jdbcType=BIGINT} and version = #{version,jdbcType=BIGINT}
21     </update>
22 </mapper>

Application.java

 1 package org.fool.spring;
 2 
 3 import org.mybatis.spring.annotation.MapperScan;
 4 import org.springframework.boot.SpringApplication;
 5 import org.springframework.boot.autoconfigure.SpringBootApplication;
 6 
 7 @SpringBootApplication
 8 @MapperScan("org.fool.spring.dao.mapper")
 9 public class Application {
10     public static void main(String[] args) {
11         SpringApplication.run(Application.class, args);
12     }
13 }

ApplicationTest.java

 1 package org.fool.spring.test;
 2 
 3 import org.fool.spring.Application;
 4 import org.fool.spring.dao.mapper.GoodsMapper;
 5 import org.fool.spring.dao.mapper.extension.GoodsMapperExt;
 6 import org.fool.spring.model.Goods;
 7 import org.junit.Test;
 8 import org.junit.runner.RunWith;
 9 import org.springframework.beans.factory.annotation.Autowired;
10 import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
11 import org.springframework.boot.test.context.SpringBootTest;
12 import org.springframework.test.context.junit4.SpringRunner;
13 
14 @RunWith(SpringRunner.class)
15 @SpringBootTest(classes = Application.class)
16 @EnableAutoConfiguration
17 public class ApplicationTest {
18     @Autowired
19     private GoodsMapper goodsMapper;
20 
21     @Autowired
22     private GoodsMapperExt goodsMapperExt;
23 
24     @Test
25     public void testSelect() {
26         Goods result = goodsMapper.selectByPrimaryKey(1L);
27         assert 1 == result.getId();
28     }
29 
30     @Test
31     public void testUpdate() {
32         Goods goods1 = goodsMapper.selectByPrimaryKey(1L);
33         Goods goods2 = goodsMapper.selectByPrimaryKey(1L);
34 
35         goods1.setName("iPhone");
36         goods2.setName("iPhoneXR");
37 
38         int result1 = goodsMapperExt.casUpdateByPrimaryKey(goods1);
39         assert 1 == result1;
40         int result2 = goodsMapperExt.casUpdateByPrimaryKey(goods2);
41         assert 0 == result2;
42     }
43 }