how to use SELECT FOR UPDATE

Question

I'm using a MySQL 5.0

My application is used by multiple user. All data is served with one shared table products (for update )and i don't want to loose any user update,i can't lock entire table bcz it slow down my application.for same reason i can not apply lock in my program.

Now I want to use:

SELECT * FROM products limit 1;

...so that other database connections unable to read this row until update is finished.

I tried:

query = "select  min(nextag_product_id) from products where is_bottomline = 0 and timestamp <= date_sub(now(),  interval 30 minute) for update"

...but this is not able to lock other connection to to read this row.At a time same products is opening on more than one user.

Which is the correct hint to do a SELECT FOR UPDATE as known for MySql?

possible duplicate of [SELECT FOR UPDATE with SQL Server](http://stackoverflow.com/questions/1483725/select-for-update-with-sql-server) — sampathsris, Jul 10 '14 at 05:29
What do you mean by _but this is not able to lock other connection to to read this row_? Should it lock other connections? — sampathsris, Jul 10 '14 at 05:32

Igor Borisenko · Accepted Answer · 2014-07-10T06:17:24.230

You can lock the rows using hint with(xlock) that will set exclusive lock. In this case other transactions will not be able even to read locked data until the first transaction either committed or rolled back.

If you want other transactions to be able to read data that is not selected by 1st transaction you need to achieve reading data using Index Seek in both transactions.
Say we have to transactions:

/* 1st transaction */
SELECT *
FROM tbl with(xlock)
WHERE field1=1

/* 2nd concurrent transaction */
SELECT *
FROM tbl with(xlock)
WHERE field1=2
/* 3d concurrent transaction */
SELECT *
FROM tbl
WHERE field1=1

If you don't have index on 1field11 than the 2nd transaction will have to wait for completion of the 1st transaction.
But if you create index on field1 than 2nd transaction will be executed in parallel while 3d transaction still will be waiting for conclusion of the 1st transaction.

CREATE INDEX IX_tbl_field1 ON tbl

how to use SELECT FOR UPDATE

1 Answers1