MySQL update request. Will the "where" condition be met in relation to race conditions and line blocking? (php, PDO, MySQL, InnoDB)

Question

I am trying to create a first-come-first-get sale page. We have n items of the same type. We want to assign these n elements to the first n users who made the request. Corresponds to each item, there is a database row. When the user clicks the buy button, the system tries to find a record that has not yet been sold ( reservationCompleted = FALSE ), updates the user ID and sets reservationCompleted to true.

Since the core of the database that I use is InnoDB, I understand that there is an internal locking mechanism that prevents two processes from updating simultaneously on the same line.

My question is

if I use the following statement, will it lead to the fact that different users will be assigned to the same line if two requests arrive at the same time?

 $query = "UPDATE available_items SET assignedPhone=".$user->phone.", reservationCompleted = TRUE, assignmentCreatedTimestamp =".time()." WHERE id=".$itemListing['id']." AND reservationCompleted=FALSE"; $stmt = $pdo->prepare($query); $stmt->execute(); 

Consider the following case.

Two different processes receive the same row (say id = 5) and try to update the record in the database. But one of them gets a castle. It updates the item and releases the lock, and the next process gets the lock. So, will it check the where clause again before doing the update?

Answer 1

When the condition is met during the race, but you must be careful how you check who won the race.

Consider the following demonstration of how this works and why you should be careful.

First set up some minimal tables.

 CREATE TABLE table1 ( `id` TINYINT UNSIGNED NOT NULL PRIMARY KEY, `locked` TINYINT UNSIGNED NOT NULL, `updated_by_connection_id` TINYINT UNSIGNED DEFAULT NULL ) ENGINE = InnoDB; CREATE TABLE table2 ( `id` TINYINT UNSIGNED NOT NULL PRIMARY KEY ) ENGINE = InnoDB; INSERT INTO table1 (`id`,`locked`) VALUES (1,0); 

id plays the role of id in your table, updated_by_connection_id acts as assignedPhone and locked as reservationCompleted .

Now let's start the race test. You should open 2 command line / terminal windows, connect to mysql and use the database in which you created these tables.

Compound 1

 start transaction; 

Compound 2

 start transaction; 

Compound 1

 UPDATE table1 SET locked = 1, updated_by_connection_id = 1 WHERE id = 1 AND locked = 0; 

Query OK, 1 line affected (0.00 sec.) Matching lines: 1 Changed: 1 Warnings: 0

Compound 2

 UPDATE table1 SET locked = 1, updated_by_connection_id = 2 WHERE id = 1 AND locked = 0; 

Compound 2 is now waiting

Compound 1

 SELECT * FROM table1 WHERE id = 1; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 

 commit; 

At this point, connection 2 is freed up to continue and displays the following:

Compound 2

Request OK, 0 rows affected (23.25 seconds) Matching rows: 0 Changed: 0 Warnings: 0

 SELECT * FROM table1 WHERE id = 1; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 

 commit; 

Everything looks great. We see that yes, the WHERE clause has been respected by race.

The reason I said that you need to be careful is because things are not so simple in a real application. You may have other actions happening inside the transaction, and this can actually change the results.

Let reset the database with the following:

 delete from table1; INSERT INTO table1 (`id`,`locked`) VALUES (1,0); 

And now consider this situation when SELECT is executed before UPDATE.

Compound 1

 start transaction; SELECT * FROM table2; 

Empty set (0.00 s)

Compound 2

 start transaction; SELECT * FROM table2; 

Empty set (0.00 s)

Compound 1

 UPDATE table1 SET locked = 1, updated_by_connection_id = 1 WHERE id = 1 AND locked = 0; 

Query OK, 1 line affected (0.00 sec.) Matching lines: 1 Changed: 1 Warnings: 0

Compound 2

 UPDATE table1 SET locked = 1, updated_by_connection_id = 2 WHERE id = 1 AND locked = 0; 

Compound 2 is now waiting

Compound 1

 SELECT * FROM table1 WHERE id = 1; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 1 row in set (0.00 sec) 

 SELECT * FROM table1 WHERE id = 1 FOR UPDATE; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 1 row in set (0.00 sec) 

 commit; 

At this point, connection 2 is freed up to continue and displays the following:

Request OK, 0 rows affected (20.47 sec.) Matching rows: 0 Changed: 0 Warnings: 0

Ok, let's see who won:

Compound 2

 SELECT * FROM table1 WHERE id = 1; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 0 | NULL | +----+--------+--------------------------+ 

Wait what? Why is locked 0 and updated_by_connection_id NULL ??

This is a cautious mention. The culprit is actually related to the fact that we made a choice at the beginning. To get the correct result, we can run the following:

 SELECT * FROM table1 WHERE id = 1 FOR UPDATE; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 

 commit; 

Using SELECT ... FOR UPDATE, we get the correct result. This can be very confusing (as it was for me, initially), since SELECT and SELECT ... FOR UPDATE give two different results.

The reason for this is due to the default isolation level of READ-REPEATABLE . When the first SELECT is executed, immediately after the start transaction; snapshot is taken. All future inactive readings will be made from this snapshot.

Therefore, if you are just naively SELECT after performing the update, it will pull the information from this initial snapshot, which before , this row has been updated. By doing SELECT ... FOR UPDATE, you force it to receive the correct information.

However, again, in a real application this can be a problem. Say, for example, your request is wrapped in a transaction, and after the update, you want to display some information. The collection and output of this information can be done using a separate reusable code that you DO NOT want to put with FOR UPDATE clauses just in case. This would lead to great disappointment due to unnecessary blocking.

Instead, you will want to select a different track. You have many options here.

First, make sure you complete the transaction after completing UPDATE. In most cases, this is probably the best, easiest choice.

Another option is not to try to use SELECT to determine the result. Instead, you can read the affected lines and use this (1-line update updated to 0 lines) to determine if UPDATE was successful.

Another option, and one that I often use, since I want a single request (for example, an HTTP request) to be completely completed in one transaction, is to make sure that the first statement executed in the transaction is either UPDATE or SELECT. .. FOR UPDATE . This will result in the picture being NOT accepted until the connection is continued.

Repeat reset our test database and see how it works.

 delete from table1; INSERT INTO table1 (`id`,`locked`) VALUES (1,0); 

Compound 1

 start transaction; SELECT * FROM table1 WHERE id = 1 FOR UPDATE; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 0 | NULL | +----+--------+--------------------------+ 

Compound 2

 start transaction; SELECT * FROM table1 WHERE id = 1 FOR UPDATE; 

Compound 2 is now pending.

Compound 1

 UPDATE table1 SET locked = 1, updated_by_connection_id = 1 WHERE id = 1 AND locked = 0; 

Query OK, 1 row affected (0.01 sec.) Matching rows: 1 Changed: 1 Warnings: 0

 SELECT * FROM table1 WHERE id = 1; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 

 SELECT * FROM table1 WHERE id = 1 FOR UPDATE; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 

 commit; 

Compound 2 is now released.

Compound 2

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 

Here you can really have server-side code to check the results of this SELECT and know what it is for sure, and not even continue the next steps. But, for completeness, I will finish as before.

 UPDATE table1 SET locked = 1, updated_by_connection_id = 2 WHERE id = 1 AND locked = 0; 

Query OK, 0 rows affected (0.00 sec.) Matching rows: 0 Changed: 0 Warnings: 0

 SELECT * FROM table1 WHERE id = 1; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 

 SELECT * FROM table1 WHERE id = 1 FOR UPDATE; 

 +----+--------+--------------------------+ | id | locked | updated_by_connection_id | +----+--------+--------------------------+ | 1 | 1 | 1 | +----+--------+--------------------------+ 

 commit; 

Now you can see that in Connection 2 SELECT and SELECT ... FOR UPDATE give the same result. This is because the snapshot that SELECT reads was not created until connection 1 was committed.

So, back to the original question: Yes, the WHERE clause is checked by the UPDATE statement in all cases. However, you should be careful with any SELECT you can do to avoid incorrectly defining the result of this UPDATE.

(Yes, another option is to change the transaction isolation level. However, I have no experience with this and any of them that may exist, so I won’t go into it.)

Answer 2

Answer: it will check the WHERE condition before updating the data .

---

Well, I have to say that this is a very interesting question. I have never thought of such a question before, and it makes me better understand how it works in MySQL. Thanks!

How do I get the answer:

First I did a test for this situation. I know this should work the same way, even before my test, but I just didn't understand why.

Why:

Finally, I found something useful in the Index Condition Downdown section .

Here's how it works in MySQL:

 MySQL Server ↑ ↑ ↓ ↓ Storage Engine(InnoDB here) 

• MySQL Server parses SQL queries from external applications.
• The MySQL server tells InnoDB to retrieve rows for it.
• InnoDB finds the rows (Index, Locking) and then returns them to the MySQL server.
• MySQL Server evaluates WHERE clauses for rows.
• Some other things ...

As you can see, the lock occurs inside InnoDB, and MySQL Server evaluates the WHERE after receiving the rows. For your situation, the row (id = 5) is blocked by the first UPDATE , and the second UPDATE gets stuck when retrieving the same row. And the evaluation for the second UPDATE WHERE occurs after receiving a lock for the row.

What else, if you created an index on id , the index will be dragged in your query.

Answer 3

No, because reservationCompleted set to true true . Remember to COMMIT every successful transaction. the next process, of course, will get a lock, but will not satisfy the WHERE and release LOCK. If you want the next process to look for another available item, you can bind your Update statement using the Sub Routine procedure to check reservationCompleted FALSE .

Answer 4

You should consider using the parameters in your application, since you are using PDO. Also, your timestamp in the database must be stored as DATETIME so that you can use the MySQL search capabilities in the search.

 <?php $sql = "UPDATE available_items SET assignedPhone=:userPhone, reservationCompleted = TRUE, assignmentCreatedTimestamp =:time WHERE id=:id AND reservationCompleted=FALSE"; $stmt = $db->prepare($sql); $stmt->bindValue(':userPhone', $user->phone, PDO::PARAM_STR); $stmt->bindValue(':time', time(), PDO::PARAM_INT); $stmt->bindValue(':id', $itemListing['id'], PDO::PARAM_INT); $stmt->execute(); $affected_rows = $stmt->rowCount(); 

In any case, in order to complete the actions you are talking about, you will need to make a request before each transaction. And if you want it to be close to each other, otherwise you run the risk of pushing away your target audience. You also need to use an asynchronous connection (think AJAX ) to constantly check the status of an item during a potential transaction and potentially deliver a messaging service so that someone knows when a transaction is not possible. Database locks are not long enough for this condition unless you have a slow connection between your database and your web server.

You might want to explore something like SOAP . Then you must write an XML file or a JSON file to the system that represents the status for each element, and your client code will simply check it for updates, and will not execute asynchronous MySQL queries continuously while the page is open. When you successfully complete the transaction, you also update this file and database. Thus, you can only use the web server instead of the web server and the database server.

You can also consider microtime() to jump to the microsecond if you want.