Consider such a scenario, which is perhaps very common: we have to update multiple fields of one or multiple rows of data in the relational database, and need to get the results of any filed updated.
Also, consider such a scenario: we have to update multiple fields of one or multiple row(s) of data in the relational database, and need to get the primary key of this batch of records updated in order to manage other related tables.
If there are other requests sent in this time window, the data will surely be contaminated, so we should block the other requests, and never execute the update statement before finishing the select statement. Adding a transaction may be the solution, and the transaction isolation level must be Read committed or higher.
The transaction will implement line blocking of that row using the update statement at the beginning, so that the other requests can only update the same row after the transaction completion report is returned. However, it may be refused by the architect during code review if we open a transaction so casually just for such a small requirement.
Besides Percona which provides source code, there is also Ali SQL maintained by Alibaba, and the database-as-a-service Alibaba Cloud RDS.
If you are using Alibaba Cloud RDS, you may try the writing to combine the update and select statements into an SQL statement, and further reduce the network overhead and database overhead to improve the performance.
A transaction should only be opened in the cases where it is required to guarantee atomicity, such as updating multiple tables.select count from update likes set gmt_modified = now(), count = count 1 where object_id = ？; It is important to note that the enhanced syntax is not expressly stated in the cloud database document.Therefore, we'd better consult relevant experts before applying it to the production environment.When the problem was posted, there was no way to have it done via a single query in My SQL.Even until now, there is still no way to have it done via a single query in the Oracle-maintained official version of My SQL. Besides the Maria DB which has been thoroughly split out, there is also Percona, the so-called enhanced version which is fully compatible with My SQL.Why is business logic involved so gratuitously by increasing the number by one?