Database locks are used to deal with concurrency problems and ensure data consistency under concurrency.
Although we did not use the lock in the process of development, our program can also be normal execution, that is because the database help us implicitly add the lock, according to the way of use can be divided into: pessimistic lock and optimistic lock; According to the granularity can be divided into: global locks, table locks and row locks, and in the event of deadlock how to resolve.
1, optimistic lock and pessimistic lock applicable scenarios:
Pessimistic lock: It is suitable for scenarios with frequent write operations. If a large number of read operations occur, locks will be added each time the read operations occur, which increases the overhead of locks and reduces the throughput of the system.
Optimistic lock: Applies to scenarios where read operations are frequent. If a large number of write operations occur, data conflicts are more likely. To ensure data consistency, the application layer needs to continuously obtain data, which increases a large number of query operations and reduces the throughput of the system.
Conclusion: Both methods have their advantages and disadvantages. Optimistic locks are used for frequent reads and pessimistic locks are used for frequent writes.
2, pessimistic lock and its principle
Pessimistic locks:
Pessimistic lock thinks that the data will be modified by others. In order to prevent modification, when I get the data, I lock it, so that others can not get my data, only after I complete the operation to release the lock, the next person can get my data. Pessimistic locking implementations simply add for update to the statement.
It’s an exclusive lock, locked regardless of reading or writing. Traditional relational database inside used a lot of this locking mechanism, such as row lock, table lock, read lock, write lock, etc., are in the operation before the first lock. Exclusive locks such as synchronized and ReentrantLock in Java are implementations of the pessimistic locking idea.
3. Optimistic locking and its principle
Optimistic locking:
Each time the data is acquired, the data will not be modified, so the data will not be locked. Since the data is not locked, the data can be read and written by other threads.
How optimistic locking works:
Optimistic locking is mostly implemented based on the data Version recording mechanism. What is data version? Adding a version identifier to the data is typically done in a database table-based version solution by adding a “Version” field to the database table (adding a field version to the table). This version number is read with the data when it is read, and is incremented with the version number when it is updated later. In this case, the version of the submitted data is compared with the current version of the database table. If the version number of the submitted data is greater than the current version number of the database table, the submitted data is updated. Otherwise, the submitted data is considered to be expired.
Implementation of optimistic locking:
Add a version field of type int to the database and entity classes to generate getter and setter methods.
4. Pessimistic lock implementation
Step 1: mapper. XML file
Select * from A Where id=1 for update;
Add for update to SQL query statement in mapper. XML
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.singerw.dao.ArticleMapper">
<sql id="Base_Column_List">
<! [email protected]>
a_id, article_title, article_content, head_image, article_author, type_number, pageviews,
create_time, is_state, version
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
<! [email protected]>
select
<include refid="Base_Column_List"/>
from article
where a_id = #{aId,jdbcType=INTEGER} for update
</select>
</mapper>
Copy the codeStep 2: Service interface
public interface ArticleService {
void testUptateLock1(Integer aId);
void testUptateLock2(Integer aId);
}
Copy the codeStep 3: Service implementation class
@Service
public class ArticleServiceImpl implements ArticleService {
@Resource
private ArticleMapper articleMapper;
@Override
@Transactional
public void testUptateLock1(Integer aId) {
// 1 do the query first
Article article = articleMapper.selectByPrimaryKey(aId);
// 2 Modifies the property value of the object
article.setArticleTitle("Title_1 for pessimistic lock _lock test");
// Simulate other services
try {
Thread.sleep(10000);
} catch (InterruptedException e) {
e.printStackTrace();
}
// 3 Call the modified method
int result = articleMapper.updateByPrimaryKeySelective(article);
// Custom null pointer exception
String str = null;
System.out.println(str.length());
}
@Override
@Transactional
public void testUptateLock2(Integer aId) {
// 1 do the query first
Article article = articleMapper.selectByPrimaryKey(aId);
// 2 Modifies the property value of the object
article.setArticleTitle("Title_2 for pessimistic lock _lock test");
// 3 Call the modified method
intresult = articleMapper.updateByPrimaryKeySelective(article); }}Copy the codeHere, a thread sleep is simulated in the testUptateLock1 method to replace the execution time of the business. Within 10 seconds, we try to call testUptateLock2 in the controller again, and wait 10 seconds for the convenience of finding the test result in the subsequent test.
Step 4: Controller
@RestController
@RequestMapping("/api")
public class ArticleController {
@Autowired
private ArticleService articleService;
@GetMapping("/updateLock01/{aId}")
public String updateLock01(@PathVariable("aId") Integer aId){
articleService.testUptateLock1(aId);
return "updateLock01 OK!";
}
@GetMapping("/updateLock02/{aId}")
public String updateLock02(@PathVariable("aId") Integer aId){
articleService.testUptateLock2(aId);
return "updateLock02 OK!"; }}Copy the codeStep 5: Visit to test
UpdateLock01 /{aId} = updateLock01/{aId}
UpdateLock01 = /updateLock02/{aId}
However, updateLock02 needs to be executed after updateLock01 is executed.
conclusion
Write a query in SQL:… For update, updateLock02 is executed only after updateLock01 is executed. This is a concrete implementation of a typically pessimistic lock.
5. Optimistic lock implementation
Step 1: Add columns and refine the entity class
Add a private int version (version =>int) to the database table
Model: Article. Java
@ApiModel(value = "article")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Article implements Serializable {
/** * article ID */
@apiModelProperty (value = "article ID")
privateInteger aId; ./** * optimistic lock */
@apiModelProperty (value = "optimistic lock ")
private Integer version;
private static final long serialVersionUID = 1L;
}
Copy the codeStep 2: mapper.xml
At the SQL statement level, we need to update the version number every time we submit data (update); +1 on the original version; Modify statement conditions also add… . And version =#{version}
<update id="updateByPrimaryKey" parameterType="com.singerw.pojo.Article">
<! [email protected]>
update article
set article_title = #{articleTitle,jdbcType=LONGVARCHAR},
article_content = #{articleContent,jdbcType=LONGVARCHAR},
head_image = #{headImage,jdbcType=VARCHAR},
article_author = #{articleAuthor,jdbcType=VARCHAR},
type_number = #{typeNumber,jdbcType=INTEGER},
pageviews = #{pageviews,jdbcType=INTEGER},
create_time = #{createTime,jdbcType=TIMESTAMP},
is_state = #{isState,jdbcType=INTEGER},
version = #{version,jdbcType=INTEGER}
where a_id = #{aId,jdbcType=INTEGER}
and version = #{version,jdbcType=INTEGER}
</update>
Copy the codeStep 3: Service interface
public interface ArticleService {
void testUptateLock3(Integer aId);
void testUptateLock4(Integer aId);
}
Copy the codeStep 4: ServiceImpl implements the class
@Service
public class ArticleServiceImpl implements ArticleService {
@Resource
private ArticleMapper articleMapper;
@Override
@Transactional
public void testUptateLock3(Integer aId) {
// 1 do the query first
Article article = articleMapper.selectByPrimaryKey(aId);
// 2 Modifies the property value of the object
article.setArticleTitle("Title_3 for optimistic lock _lock test");
// Simulate other services
try {
Thread.sleep(10000);
} catch (InterruptedException e) {
e.printStackTrace();
}
// 3 Call the modified method
int result = articleMapper.updateByPrimaryKeySelective(article);
}
@Override
@Transactional
public void testUptateLock4(Integer aId) {
// 1 do the query first
Article article = articleMapper.selectByPrimaryKey(aId);
// 2 Modifies the property value of the object
article.setArticleTitle("Title_4 for optimistic lock _lock test");
// 3 Call the modified method
intresult = articleMapper.updateByPrimaryKeySelective(article); }}Copy the codeStep 5: Controller
@RestController
@RequestMapping("/api")
public class ArticleController {
@Autowired
private ArticleService articleService;
@GetMapping("/updateLock03/{aId}")
public String updateLock03(@PathVariable("aId") Integer aId){
articleService.testUptateLock3(aId);
return "updateLock03 OK!";
}
@GetMapping("/updateLock04/{aId}")
public String updateLock04(@PathVariable("aId") Integer aId){
articleService.testUptateLock4(aId);
return "updateLock04 OK!"; }}Copy the codeStep 6: Visit to test
UpdateLock03 ===>/ {aId}
UpdateLock04 ===> /{aId}
conclusion
The second request to updateLock04 did not wait until the first request to updateLock03 was completed. Respond directly and update successfully;
Then, after the second request was submitted successfully, the first request was submitted 10 seconds later, but the modification failed, because the version number of updateLock03 has been changed by updateLock04, and all updateLock03 cannot be submitted again.
For the realization of optimistic lock in Mybatis, is the implementation of SQL statements, if there is no successful update, no exception will be reported, directly is the affected behavior 0;
