Can two users work on the same row in mysql?

Question

Hi,

In my application I search for products available in my inventory table, select them and then insert the inventoryID into my items table.

The SELECT & INSERT are two different operations. Is it possible that another user at the same time could snatch the same rows retrieved and use them to allocate the same inventory products to different items?

Answer 1

The answer is yes.

Assuming you don't want this to happen, you should look into wrapping the two operations into a transaction. I think transactions have been available in MySQL since version 4.

Answer 2

To answer your question directly, Yes, though it may depend on how you designed the database. If there is a danger of this occuring, you might consider record locks to prevent access to the items you are in the process of assigning.

You may find it worthwhile looking up "transaction isolation" and also "record locking".

http://www.expresscomputeronline.com/20040426/techspace01.shtml

Hopefully you should get an understanding of how locks and transactions differ.