mysql write lock

Question

I dont add any manual locking or any transaction anything, just manual running queries from php script, and execute below two update queries at the same time

update table_x set status = 'PROCESSING' where id = 123 and status = 'PENDING'

update table_x set status = 'PROCESSING' where id = 123 and status = 'PENDING'

And If I rely on the updated rows count then is it possible that both return 1?

The requirement here is that from my web-server if two processes at the same time try to process same row then I have to avoid both processing it.

In a solution where I first run a select query to check if the status for the given id is PENDING and then do the processing, there is a chance of both processing it.

But I'm not sure what will happen if I do just one update query and rely on the rows updated count.

Answer 1

No problem. Use InnoDB.

One of those queries will take out an "exclusive" lock on the row in question. The other query will try to take out such a lock, but be blocked until the first finishes (and COMMITs). Then it will see that there is no longer any row matching the WHERE clause, so it will say "0 rows matched, 0 rows updated".

Answer 2

The Rick James answer is good (I vote it up). You can try the following test. Create table and insert some values:

create table table_x (
  id int unsigned not NULL,
  status varchar(100) not NULL,
  index(id)
) engine=INNODB;

insert into table_x values 
(123, 'PENDING'), (321, 'PENDING');

Then start two mysql sessions into two separated terminal. I cannot try two updates at the same time, but I can simulate it with a transaction:

session1 > start transaction;
Query OK, 0 rows affected (0.00 sec)

session1 > update table_x set status = 'PROCESSING' where id = 123 and status = 'PENDING';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session1> select row_count();
+-------------+
| row_count() |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

Second session:

session2> update table_x set status = 'PROCESSING' where id = 123 and status = 'PENDING';

The previous statement blocks until the first session commit:

session1> commit;
Query OK, 0 rows affected (0.01 sec)

Into the second session we will see:

Query OK, 0 rows affected (8.48 sec)
Rows matched: 0  Changed: 0  Warnings: 0

session2> select row_count();
+-------------+
| row_count() |
+-------------+
|           0 |
+-------------+
1 row in set (0 

You can use MyIsam storage engine (withou transaction), if you want, and obtain similar results. But I cannot see any advantage to use it.