Hi,
I have created a query to read and update a stock item by using the FOR UPDATE
statement. According to the MySql documention only the rows that are selected
using the FOR UPDATE should be locked for other sessions, but somehow whole the
table is locked. This post gives some general information and then shows 3
scenarios. The first 2 scenarios work as expected, but the last one fails.
Two questions:
a.. Does anyone has an idea why scenario 3 does not work as expected and what
is the solution to make it work?
b.. Is this the preferred way to update a stock table? If not, could you
provide me an example how it should be done?
Here some clarification about the problem:
Version MySQL
5.0.45-community-nt
Stock table
DROP TABLE IF EXISTS `mydatabase`.`stock`;
CREATE TABLE `mydatabase`.`stock` (
`ID` bigint(20) unsigned NOT NULL auto_increment,
`ProductID` bigint(20) unsigned NOT NULL,
`SizeID` bigint(20) unsigned NOT NULL,
`Quantity` int(11) NOT NULL,
PRIMARY KEY USING BTREE (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
MySQL example
This query updates the stock of one product of a particular size by
decrementing it's quantity by 1.
START TRANSACTION;
SET AUTOCOMMIT=0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2
FOR UPDATE;
UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1
AND Stock.SizeID = 2;
COMMIT;
Now let me show you three scenarios wherein you can see that using the FOR
UPDATE statement is not working as it should.
Scenario 1 (works as expected)
I opened two sessions of MySql Query Browser and each executing the same MySql
query. So, both looking at the same row (same selection criteria).
START TRANSACTION;
SET AUTOCOMMIT=0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2
FOR UPDATE;
UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1
AND Stock.SizeID = 2;
COMMIT;
1) Session 1: START TRANSACTION;
2) Session 1: SET AUTOCOMMIT=0;
3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND
Stock.SizeID = 2 FOR UPDATE;
(returns Stock.Quantity as expected)
4) Session 2: START TRANSACTION;
5) Session 2: SET AUTOCOMMIT=0;
6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND
Stock.SizeID = 2 FOR UPDATE;
(blocks as exepected)
7) Session 1: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE
Stock.ProductID = 1 AND Stock.SizeID = 2;
8) Session 1: COMMIT;
(the blocking step 6 is now executed and returns the
updated Stock.Quantity as exepected)
9) Session 2: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE
Stock.ProductID = 1 AND Stock.SizeID = 2;
10) Session 2: COMMIT;
Scenario 2 (works as expected)
I opened two sessions of MySql Query Browser and each executing the same MySql
query. Only the selection criteria are different;
both are using another value for the primary key "ID". When I use different
values for the primary key "ID" I have the same results
as in scenario 1.
Session 1 query
START TRANSACTION;
SET AUTOCOMMIT=0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 1 FOR UPDATE;
UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 1;
COMMIT;
Session 2 query
START TRANSACTION;
SET AUTOCOMMIT=0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 2 FOR UPDATE;
UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 2;
COMMIT;
1) Session 1: START TRANSACTION;
2) Session 1: SET AUTOCOMMIT=0;
3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 1 FOR UPDATE;
(returns Stock.Quantity as expected)
4) Session 2: START TRANSACTION;
5) Session 2: SET AUTOCOMMIT=0;
6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 2 FOR UPDATE;
(no blocking as exepected, because it's another row;
returns Stock.Quantity as expected)
7) Session 1: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE
Stock.ID = 1;
8) Session 1: COMMIT;
9) Session 2: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE
Stock.ID = 2;
10) Session 2: COMMIT;
Scenario 3 (does not work as expected)
I opened two sessions of MySql Query Browser and each executing the same MySql
query. Only the selection criteria are different;
both are using another value for the non-primary key "SizeID". Making SizeID a
primary key does not influence the results of this
scenario. So, the sessions work in different rows, but session 2 is blocked! It
looks like the whole table is locked instead of only one row?
Session 1 query
START TRANSACTION;
SET AUTOCOMMIT=0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 1
FOR UPDATE;
UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1
AND Stock.SizeID = 1;
COMMIT;
Session 2 query
START TRANSACTION;
SET AUTOCOMMIT=0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2
FOR UPDATE;
UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1
AND Stock.SizeID = 2;
COMMIT;
1) Session 1: START TRANSACTION;
2) Session 1: SET AUTOCOMMIT=0;
3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND
Stock.SizeID = 1 FOR UPDATE;
(returns Stock.Quantity as expected)
4) Session 2: START TRANSACTION;
5) Session 2: SET AUTOCOMMIT=0;
6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND
Stock.SizeID = 2 FOR UPDATE;
(blocks, but this is not exepected and is unwanted
behaviour, because session 2 is using another row)
7) Session 1: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE
Stock.ProductID = 1 AND Stock.SizeID = 1;
8) Session 1: COMMIT;
(the blocking step 6 is now executed and returns the
Stock.Quantity as exepected)
9) Session 2: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE
Stock.ProductID = 1 AND Stock.SizeID = 2;
10) Session 2: COMMIT;
Regards,
Johan Machielse
Machielse Software
http://www.machielsesoftware.nl
