CODEWITHSUNDEEP
mysql
Matt
Matt

Reputation: 21

Can two users work on the same row in mysql?

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?

Upvotes: 2

Views: 62

Answers (2)

Jaydee
Jaydee

Reputation: 4158

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.

Upvotes: 0

Andrew Carmichael
Andrew Carmichael

Reputation: 3113

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.

Upvotes: 3

Related Questions