How to Test "Select for Update" in MySQL

MySQL & MariaDB Other MySQL® Questions
1

I am performing
SELECT … FOR UPDATE
or row level locking with InnoDB tables.

My intention is to only one request can read the same row. So if two users make request for the same data as the same time. Only one of them get data, who fires the query first.

But How can i test that locking is placed or not. as I am testing it by retrieving the same data at same time and both users getting the data.

Note: My tables are InnoDB, My query executes in transaction, my query as below:

SELECT * FROM table_name WHERE cond FOR UPDATE;

Any other thing I have to check for this to make work?

As far as I know, When we use FOR UPDATE in SELECT query then selected rows will get locked until transaction commits or Roll back and no other mysql thread can even select those rows. Is that correct?

2

You need the following to be true:

  1. it’s an InnoDB table
  2. you are in a transaction
  3. you do not have AUTOCOMMIT set.

Check all three of these very carefully, even if you think you know. (Your InnoDB table might be MyISAM, for example.)

3

Ok, have checked all things twice.

I am using PDO for my DB related work and for starting the transaction I am using the start transaction method which is provided by the PDO.

PDO::beginTransaction()

For the third point, You mean to say that I have to set AUTOCOMMIT to 0 in query even if I have started the transaction with above method?