sethbeckerman5 Staff asked 4 years ago
Overview
Assume that there is a table which controls stock amount information.
Now, user A and B try to take the last stock at the same time.
The questions are:
Could never be negative values? Do we need any explicit pessimistic row locking?
Which transaction level should I use: , , or (only for MySQL)?
Does it yield different conclusion with different RDBMS?
Related Information
(mysql innodb) Is single update statement with "where" transaction safe?
This question concludes that explicit pessimistic row locking is required for MySQL.
My Twitter friend RDBMS geek says that:
Oracle tries to achieve write consistency; if the target row has been changed, the consecutive UPDATE query is automatically rolled back and it retries with implicit pessimistic row locking. He says that it is described in this book: Amazon | Expert Oracle Database Architecture | Kyte, Thomas, Kuhn, Darl | Software Development
PostgreSQL has immutable rows and then the old rows are treated as dead tuple; so the latter updates will never be applied.