Case analysis of several MySQL update operations

This article will analyze the pros and cons of several data update operations through a case study of updating a user account balance. I hope this helps you all 🐶.

Database version: mysql 5.7.23

Case Study

DDL to create a database:

CREATE TABLE `hw_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

Update account balance

Direct Update

Solution 1: Update after query

# Data query select * from hw_account where id = 1;

# Update data update hw_account set balance = 5 where id = 1;

The problem is that if the operation is divided into two parts and executed concurrently, the update may be lost.

Optimistic locking scheme

Use the version number operation, that is, add an optimistic lock to the database.

# Data query select * from hw_account where id = 1;

# Update data update hw_account set balance = 5 , version = version + 1 
  where id = 1 and version = n;
  
# Determine whether it is successful if row < 1 {
   rollback}

The existing problem is that if this piece of data is operated concurrently, other requests will fail. If the front-end link of this request is relatively long, the rollback cost will be relatively high.

Lock-free solution

No query is required, database calculation is used, and version number operation is not required. Validity is judged directly through domain values. The specific SQL is as follows:

# Update data update hw_account set balance = balance + @change_num , version = version + 1 
  where id = 1 and version = n;
  
# Determine whether it is successful if row < 1 {
   rollback}

This solution is relatively simple to modify, but it relies on data calculations and does not feel particularly user-friendly.

Queuing Operations

Data requests are queued through redis or zk's distributed locks. Then update the data.

# Pseudo code if (get distributed lock) {
  update hw_account set balance = @balance where id = 1;
} else {
  # Enter waiting, or spin to acquire the lock}

Frequently asked questions

If the update_time field exists in the data, how many rows are affected?

The update_time field is defined as follows. If the data is id = 1, status = 1, and the SQL statement for updating the data is

update hw_account set `status` = 1 where id = 1;

The number of affected rows returned is 0;

If an update is executed but the number of rows affected is 0, will a row lock be added?

Yes, all update statements will add row locks (prerequisite, within a transaction)

References

mysql.com

This is the end of this article about case analysis of several MySQL update operations. For more relevant MySQL update operation content, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!