What are the methods of MySQL update operation
This article introduces the relevant knowledge of "what are the methods of MySQL update operation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
Case analysis
Create the DDL of the database:
CREATE TABLE `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 amount
Direct update
Update after query in scenario 1
# data query select * from hw_account where id = 1bot # data update update hw_account set balance = 5 where id = 1
The problem is that there are two operations, if executed concurrently, it may cause the loss of updates.
Optimistic locking scheme
Use the version number operation, that is, to add optimistic locks to the database.
# data query select * from hw_account where id = 1bot # data update update hw_account set balance = 5, version = version + 1 where id = 1 and version = n; # determine whether if row is successful < 1 {rollback}
The problem is that if this piece of data is operated concurrently, it will cause other requests to fail. If the front link of this request is long, the rollback cost is high.
Unlocked scheme
Do not need to query, use the calculation of the database, do not need the operation of the version number, and judge the validity directly by the domain value. The specific SQL is as follows:
# data update update hw_account set balance = balance + @ change_num, version = version + 1 where id = 1 and version = n; # determine whether if row is successful < 1 {rollback}
This scheme is relatively simple to modify, but it depends on data calculation, so it doesn't feel particularly friendly.
Queuing operation
Data requests are queued through redis or zk distributed locks. And then update the data.
# pseudo code if (acquire distributed lock) {update hw_account set balance = @ balance where id = 1;} else {# enter waiting, or spin to acquire lock} FAQ
If there is a update_time field in the data, how many rows are affected?
The field of update_time is defined as follows. If the data is id = 1, status = 1. If the sql for updating the data is
Update hw_account set `status` = 1 where id = 1
The number of affected rows returned is 0
Will row locks be added if update updates are performed but the number of rows affected is 0?
Yes, any statement that executes an update will add a row lock (premise, within transaction)
This is the end of the content of "what are the methods of MySQL update operation". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
