SATURN  RING  STATION

At a distant space view post, we chat about codes.

MyBatis optimistic locking implementation tutorial with MySQL

Thoughts and background (unnecessary):

I was doing CRUDs for a ‘not-ORM-friendly’ MySQL database that has a table with 400,000 rows and relationships. The database has no ‘auto_increment’ on IDs, no version columns, composite keys, varchar(4) PK with numbers, and linked tables with columns for data but no IDs. It gonna be a nightmare for Hibernate, so I picked MyBatis because of its outstanding flexibility and speed. It does service me well, but I found out one feature MyBatis is lacking behind: built-in optimistic locking. After some searching, I found out that I either have to implement it myself or use a plugin. Since only two objects in this project really need optimistic locking, so a plugin will be overkill.


 

Here we go.

This is the table we’re gonna deal with in this tutorial. It didn’t have the ‘version’ column before, so the first thing is showing the SQL to add this new column.

ALTER TABLE employees ADD version INT UNSIGNED NOT NULL DEFAULT 0;

It can be using ‘smallint’ for smaller size though. Now we got the schema out of the way, let’s focus on the main topic.

Domain POJO with java.sql.date :

public class Employee {
    private Integer id;
    private String firstName;
    private String lastName;
    private Character gender;
    private Date birthDate;
    private Date HireDate;
    private Integer version;

    //Setters, getters and simple toString...
}

EmployeeSeriver and Mybatis mapper interface:

@Repository
public class EmployeeService {

    @Autowired
    EmployeeMapper employeeMapper;

    public Employee findEmployeeById(Integer id) {
        return employeeMapper.findEmployeeById(id);
    }

    public Boolean updateEmployee(Employee employee){
        employeeMapper.updateEmployee(employee);
    }

}
public interface EmployeeMapper {

    Employee findEmployeeById(Integer id);

    Boolean updateEmployee(Employee employee);

}

MyBatis XML mapper:

<mapper namespace="com.tom.mappers.EmployeeMapper">

    <resultMap id="employeeResultMap" type="Employee">
        <id property="id" column="emp_no"/>
        <result property="firstName" column="first_name"/>
        <result property="lastName" column="last_name"/>
        <result property="gender" column="gender"/>
        <result property="birthDate" column="birth_date"/>
        <result property="hireDate" column="hire_date"/>
        <result property="version" column="version"/>
    </resultMap>

    <select id="findEmployeeById" parameterType="Integer" resultMap="employeeResultMap">
        SELECT * FROM employees WHERE emp_no = #{id}
    </select>

    <update id="updateEmployee" parameterType="Employee">
        UPDATE employees SET
          first_name = #{firstName},
          last_name = #{lastName},
          gender = #{gender},
          birth_date = #{birthDate},
          hire_date = #{hireDate},
          version = version + 1
          WHERE emp_no = #{id} AND version = #{version}
    </update>

</mapper>

Take a look at the last two rows of update’s SQL:

          version = version + 1
          WHERE emp_no = #{id} AND version = #{version}

Yep, that’s all we need. Remind that we sent the version out to the view layer by the select method, then we compare the update’s #{version} back from the view layer with the version in the database. If it’s the same, updates the row. If it’s not the same, this row is locked and operated by other admin concurrently; This SQL gonna update zero row since two versions don’t match. MyBatis will pick up the zero row change and send back a zero, which is a false in Boolean.

Let’s look at a test:

	@Test
	public void optimisticLockingTest() throws Exception{
		Integer id = 499999;
		String name1 = "BananaEmp1";
		String name2 = "AppleEmp2";

		Employee employee1 = employeeService.findEmployeeById(id);
		Employee employee2 = employeeService.findEmployeeById(id);

		System.out.println(employee1);
		System.out.println(employee2);

		employee1.setFirstName(name1);
		if (employeeService.updateEmployee(employee1)){
			System.out.println("Banana update succeeded.");
		} else {
			System.out.println("Banana update failed");
		}

		employee2.setFirstName(name2);
		if (employeeService.updateEmployee(employee2)){    // 	Will be updated if without optimistic locking,
			System.out.println("Apple update succeeded."); // 	since it's put after employee1.
		} else {
			System.out.println("Apple update failed.");
		}

		Employee employee3 = employeeService.findEmployeeById(id);
		System.out.println(employee3);
		assertEquals(employee3.getFirstName(), name1);
	}

This is the result:

Shokugeki Tsukuda ID : 499999, version: 5
Shokugeki Tsukuda ID : 499999, version: 5
Banana update succeeded.
Apple update failed
BananaEmp1 Tsukuda ID : 499999, version: 6

AssertEquals test past and no exception thrown.

You must setup a catch when update method returns false from MyBatis mapper interface. Otherwise, optimistic lock does work and block the changes, but the app will run like update succeeded, no error massages will be shown for the failed update.

 

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

MyBatis optimistic locking implementation tutorial with MySQL