How is optimistic locking handled in MyBatis for the database?

There are typically two methods for handling optimistic locking in MyBatis when dealing with databases.

1. Utilize a version number field: Add a version number field to the database table, incrementing the number each time data is updated. Include a check for the version number in the SQL statement to ensure that the update operation only affects records with matching versions. Example:

<update id="updateUser" parameterType="User">
    UPDATE user
    SET username = #{username},
        version = version + 1
    WHERE id = #{id} AND version = #{version}
</update>

1. Using a timestamp field: Add a timestamp field in the database table to record the time of each data update. Include a timestamp check in the SQL statement to ensure that the update operation only affects records that match the timestamp. An example is shown below:

<update id="updateUser" parameterType="User">
    UPDATE user
    SET username = #{username},
        last_update_time = #{lastUpdateTime}
    WHERE id = #{id} AND last_update_time = #{lastUpdateTime}
</update>

The above are the two common methods for handling optimistic locking in databases. The specific choice of method depends on the specific business needs and database table structure.