MySQL concurrent updates

Question

I have small POS system with server side uses PHP and MySql (InnoDB). When "Sale" is being completed the following query is executed for update stock quantity:

"UPDATE products SET qty=qty-:qty, ustatus=1 WHERE pid=:pid";

Does this statement is "Concurrency Update Secure", meaning do i need any transactions or locking tables.

I just want to be sure, that multiple queries like this, executed in same time does not make mess with my stock.

Answer 1

1. If you use innodb, then all sql statements are executed in a transaction, you do not need to explicitly specify that.

2. Concurrency control is done via locks, not transactions. Transactions may only determine the lifespan of locks.

3. The update statement places an exclusive lock on the records that it wants to modify, meaning no other statements can modify (sometimes cannot even read) the locked record until the exclusive lock is released. So, your statement is safe from concurrency point of view.