List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:January 15 2010 2:14am
Subject:Re: When using "FOR UPDATE" whole the table seems to lock instead of
selected row
View as plain text  
Johan,

I don't see a valid need for using FOR UPDATE here.  In fact, FOR
UPDATE is the cause of many grievances, and I would advise you to
avoid it by any means possible.  Among other things, it will cause
serious performance problems when your server gets busy.  And as you
can see, it's hard to figure out why it behaves as it does.  Simply
issue the UPDATE statement without a SELECT first.

- Baron

On Thu, Jan 14, 2010 at 4:08 AM, Johan Machielse
<johan.machielse@stripped> wrote:
> 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



-- 
Baron Schwartz
Percona Inc: Services and Support for MySQL
http://www.percona.com/
Thread
When using "FOR UPDATE" whole the table seems to lock instead of selected rowJohan Machielse14 Jan
  • Re: When using "FOR UPDATE" whole the table seems to lock instead of selected rowBaron Schwartz15 Jan
  • Re: When using "FOR UPDATE" whole the table seems to lock instead of selected rowJohan Machielse15 Jan
    • Re: When using "FOR UPDATE" whole the table seems to lock instead of selected rowPerrin Harkins15 Jan
      • RE: When using "FOR UPDATE" whole the table seems to lock insteadof selected rowGavin Towey18 Jan