MySQL并发更新 [英] MySQL concurrent updates

问题描述

我有小型POS系统，服务器端使用PHP和MySql(InnoDB). 完成销售"后，将执行以下查询以更新库存数量:

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.

推荐答案

1. 如果使用innodb，则所有sql语句在事务中执行，您无需明确指定.

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

并发控制是通过锁而不是事务来完成的.事务只能决定锁的寿命.

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

更新语句放置排他锁在要修改的记录上，表示否其他语句可以修改(有时甚至无法读取)锁定的记录，直到释放互斥锁为止.因此，从并发的角度来看，您的陈述是安全的.

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.

这篇关于MySQL并发更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！