How to Ensure database read/write transactions

Document directory

• Solution 1: transactions
• Solution 2: Determine whether the UPDATE results are valid
• Solution 3: Use the auto-increment ID attribute of the table
• Solution 4: Introduce the requestid Field

Scenario

First, let's talk about one of the application scenarios I met:

A table containing 50 thousand pieces of data is expected to obtain an unused piece of data each time, and then mark that the data has been used.

For the sake of convenience, the syntax for converting MySQL into SQL is:

One table:

Value	Status
V1	1
V2	1
...	1

 

 

 

 

We need to read a row of data at a time, and then change the status field of the row to 0. It must involve one select and one update. How can we ensure the transaction?

Note: The table structure is not required. You can design it yourself.

 

Solution

If you think it is simple: directly select the data and then update it. That's really a tragedy.

For example, read and write SQL statements

SELECT value FROM table WHERE status = 1 LIMIT 1UPDATE table SET status = 0 WHERE value = 'v1'

If each request is the same, it is executed in sequence.

However, if the concurrency of the application is very high, this will cause problems. For example, if user 2 finishes executing select but does not complete update, user 2 executes select. Then user 1 and user 2 will get the same data, which is obviously not the result we want.

So what are the good solutions? I believe that the solution that many people immediately think of must be a transaction. Yes, it is indeed a good solution to use transactions.

Solution 1: transactions

Start transaction; select value from Table where status = 1 limit 1 update table set status = 0 where value = 'v1 'commit; or rollback;

But after all, transactions are a method that affects performance. Is there any way to avoid transactions?

Solution 2: Determine whether the UPDATE results are valid

The execution result is returned when the update statement is executed in MySQL, as follows:

mysql> UPDATE table SET status = 0 WHERE value = 'v1' AND status = 1;Query OK, 0 rows affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0

Here, rows matched indicates the number of matched rows, and changed indicates the number of modified rows.

Back to the problem we just encountered, we can use this information to determine whether the problem was successful.

SELECT value FROM table WHERE status = 1 LIMIT 1UPDATE table SET status = 0 WHERE value = 'v1' AND status = 1

If the returned result is:

Rows matched: 1  Changed: 1  Warnings: 0

Indicates that this data update is valid.

If the returned result is:

Rows matched: 0  Changed: 0  Warnings: 0

Indicates that the data update failed because the data in this row is occupied by another user and needs to be retried.

But the problem with this method is that when the concurrency is very high, many requests may conflict and need to be retried.

Solution 3: Use the auto-increment ID attribute of the table

ID	Value	Status
1	V1	1
2	V2	1

 

 

 

ID is the auto-increment primary key (auto_increment) of the table)

Another table Table2 is required.

ID	...
1	...
2	...
...	...

 

 

 

 

ID is also the auto-increment primary key (auto_increment) of the table)

 

Insert into Table2 (...) values (...); obtain the most recent idupdate table set status = 0 where id = idselect value from Table where id = ID

Because the auto-increment ID is unique, the final data can be ensured to be unique, but the disadvantage is also very obvious: One more table is required, in addition, the IDs of the two tables must be mapped.

Solution 4: Introduce the requestid Field

Table settings:

Value	Status	Request_id
V1	1
V2	1

 

 

 

Use SQL:

UPDATE table SET status = 0, request_id = 'xxxxx'  WHERE status = 1 LIMIT 1SELECT value FROM table WHERE request_id = 'xxxxx'

As long as the request_id is unique (the simplest method is hash (SERVER + time + random number), the structure we get is certainly valid.

 

Hope to help you. ^ V ^