When using "FOR UPDATE" whole the table seems to lock instead of selected row

------=_NextPart_000_001F_01CA9501.77182F20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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.=20

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=3DInnoDB AUTO_INCREMENT=3D3 DEFAULT CHARSET=3Dlatin1;

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=3D0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D 1 AND =
Stock.SizeID =3D 2 FOR UPDATE;
UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE =
Stock.ProductID =3D 1 AND Stock.SizeID =3D 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=3D0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D 1 AND =
Stock.SizeID =3D 2 FOR UPDATE;
UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE =
Stock.ProductID =3D 1 AND Stock.SizeID =3D 2;
COMMIT;

1) Session 1: START TRANSACTION;
2) Session 1: SET AUTOCOMMIT=3D0;
3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D =
1 AND Stock.SizeID =3D 2 FOR UPDATE;
(returns Stock.Quantity as expected)
4) Session 2: START TRANSACTION;
5) Session 2: SET AUTOCOMMIT=3D0;
6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D =
1 AND Stock.SizeID =3D 2 FOR UPDATE;
(blocks as exepected)
7) Session 1: UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 =
WHERE Stock.ProductID =3D 1 AND Stock.SizeID =3D 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 =3D Stock.Quantity - 1 =
WHERE Stock.ProductID =3D 1 AND Stock.SizeID =3D 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=3D0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ID =3D 1 FOR UPDATE;
UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE Stock.ID =
=3D 1;
COMMIT;

Session 2 query
START TRANSACTION;
SET AUTOCOMMIT=3D0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ID =3D 2 FOR UPDATE;
UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE Stock.ID =
=3D 2;
COMMIT;

1) Session 1: START TRANSACTION;
2) Session 1: SET AUTOCOMMIT=3D0;
3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ID =3D 1 FOR =
UPDATE;
(returns Stock.Quantity as expected)
4) Session 2: START TRANSACTION;
5) Session 2: SET AUTOCOMMIT=3D0;
6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ID =3D 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 =3D Stock.Quantity - 1 =
WHERE Stock.ID =3D 1;
8) Session 1: COMMIT;
9) Session 2: UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 =
WHERE Stock.ID =3D 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?=20

Session 1 query
START TRANSACTION;
SET AUTOCOMMIT=3D0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D 1 AND =
Stock.SizeID =3D 1 FOR UPDATE;
UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE =
Stock.ProductID =3D 1 AND Stock.SizeID =3D 1;
COMMIT;

Session 2 query
START TRANSACTION;
SET AUTOCOMMIT=3D0;
SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D 1 AND =
Stock.SizeID =3D 2 FOR UPDATE;
UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE =
Stock.ProductID =3D 1 AND Stock.SizeID =3D 2;
COMMIT;

1) Session 1: START TRANSACTION;
2) Session 1: SET AUTOCOMMIT=3D0;
3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D =
1 AND Stock.SizeID =3D 1 FOR UPDATE;
(returns Stock.Quantity as expected)
4) Session 2: START TRANSACTION;
5) Session 2: SET AUTOCOMMIT=3D0;
6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D =
1 AND Stock.SizeID =3D 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 =3D Stock.Quantity - 1 =
WHERE Stock.ProductID =3D 1 AND Stock.SizeID =3D 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 =3D Stock.Quantity - 1 =
WHERE Stock.ProductID =3D 1 AND Stock.SizeID =3D 2;
10) Session 2: COMMIT;

Regards,

Johan Machielse
Machielse Software
http://www.machielsesoftware.nl
------=_NextPart_000_001F_01CA9501.77182F20--

Re: When using "FOR UPDATE" whole the table seems to lock instead of

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
wrote:
> Hi,
>
> I have created a query to read and update a stock item by using the FOR U=
PDATE statement. According to the MySql documention only the rows that are =
selected using the FOR UPDATE should be locked for other sessions, but some=
how 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 la=
st one fails.
>
> Two questions:
> =A0a.. Does anyone has an idea why scenario 3 does not work as expected a=
nd what is the solution to make it work?
> =A0b.. Is this the preferred way to update a stock table? If not, could y=
ou 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 =A0`mydatabase`.`stock` (
> =A0`ID` bigint(20) unsigned NOT NULL auto_increment,
> =A0`ProductID` bigint(20) unsigned NOT NULL,
> =A0`SizeID` bigint(20) unsigned NOT NULL,
> =A0`Quantity` int(11) NOT NULL,
> =A0PRIMARY KEY =A0USING BTREE (`ID`)
> ) ENGINE=3DInnoDB AUTO_INCREMENT=3D3 DEFAULT CHARSET=3Dlatin1;
>
> MySQL example
> This query updates the stock of one product of a particular size by decre=
menting it's quantity by 1.
>
> START TRANSACTION;
> SET AUTOCOMMIT=3D0;
> SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D 1 AND Stock.Si=
zeID =3D 2 FOR UPDATE;
> UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE Stock.Produc=
tID =3D 1 AND Stock.SizeID =3D 2;
> COMMIT;
>
> Now let me show you three scenarios wherein you can see that using the FO=
R 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=3D0;
> SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D 1 AND Stock.Si=
zeID =3D 2 FOR UPDATE;
> UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE Stock.Produc=
tID =3D 1 AND Stock.SizeID =3D 2;
> COMMIT;
>
> 1) Session 1: START TRANSACTION;
> 2) Session 1: SET AUTOCOMMIT=3D0;
> 3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D =
1 AND Stock.SizeID =3D 2 FOR UPDATE;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(returns Stock.Quantity as expecte=
d)
> 4) Session 2: START TRANSACTION;
> 5) Session 2: SET AUTOCOMMIT=3D0;
> 6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D =
1 AND Stock.SizeID =3D 2 FOR UPDATE;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(blocks as exepected)
> 7) Session 1: UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHER=
E Stock.ProductID =3D 1 AND Stock.SizeID =3D 2;
> 8) Session 1: COMMIT;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(the blocking step 6 is now execut=
ed and returns the updated Stock.Quantity as exepected)
> 9) Session 2: UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHER=
E Stock.ProductID =3D 1 AND Stock.SizeID =3D 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 differe=
nt =A0values for the primary key "ID" I have the same results
> as in scenario 1.
>
> Session 1 query
> START TRANSACTION;
> SET AUTOCOMMIT=3D0;
> SELECT Stock.Quantity FROM Stock WHERE Stock.ID =3D 1 FOR UPDATE;
> UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE Stock.ID =3D=
1;
> COMMIT;
>
> Session 2 query
> START TRANSACTION;
> SET AUTOCOMMIT=3D0;
> SELECT Stock.Quantity FROM Stock WHERE Stock.ID =3D 2 FOR UPDATE;
> UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE Stock.ID =3D=
2;
> COMMIT;
>
> 1) Session 1: START TRANSACTION;
> 2) Session 1: SET AUTOCOMMIT=3D0;
> 3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ID =3D 1 FOR U=
PDATE;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(returns Stock.Quantity as expecte=
d)
> 4) Session 2: START TRANSACTION;
> 5) Session 2: SET AUTOCOMMIT=3D0;
> 6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ID =3D 2 FOR U=
PDATE;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(no blocking as exepected, because=
it's another row; returns Stock.Quantity as expected)
> 7) Session 1: UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHER=
E Stock.ID =3D 1;
> 8) Session 1: COMMIT;
> 9) Session 2: UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHER=
E Stock.ID =3D 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 Siz=
eID a primary key does not influence the results of this
> scenario. So, the sessions work in different rows, but session 2 is block=
ed! It looks like the whole table is locked instead of only one row?
>
> Session 1 query
> START TRANSACTION;
> SET AUTOCOMMIT=3D0;
> SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D 1 AND Stock.Si=
zeID =3D 1 FOR UPDATE;
> UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE Stock.Produc=
tID =3D 1 AND Stock.SizeID =3D 1;
> COMMIT;
>
> Session 2 query
> START TRANSACTION;
> SET AUTOCOMMIT=3D0;
> SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D 1 AND Stock.Si=
zeID =3D 2 FOR UPDATE;
> UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHERE Stock.Produc=
tID =3D 1 AND Stock.SizeID =3D 2;
> COMMIT;
>
> 1) Session 1: START TRANSACTION;
> 2) Session 1: SET AUTOCOMMIT=3D0;
> 3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D =
1 AND Stock.SizeID =3D 1 FOR UPDATE;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(returns Stock.Quantity as expecte=
d)
> 4) Session 2: START TRANSACTION;
> 5) Session 2: SET AUTOCOMMIT=3D0;
> 6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID =3D =
1 AND Stock.SizeID =3D 2 FOR UPDATE;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(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 =3D Stock.Quantity - 1 WHER=
E Stock.ProductID =3D 1 AND Stock.SizeID =3D 1;
> 8) Session 1: COMMIT;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(the blocking step 6 is now execut=
ed and returns the Stock.Quantity as exepected)
> 9) Session 2: UPDATE Stock SET Stock.Quantity =3D Stock.Quantity - 1 WHER=
E Stock.ProductID =3D 1 AND Stock.SizeID =3D 2;
> 10) Session 2: COMMIT;
>
> Regards,
>
> Johan Machielse
> Machielse Software
> http://www.machielsesoftware.nl



--=20
Baron Schwartz
Percona Inc: Services and Support for MySQL
http://www.percona.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: When using "FOR UPDATE" whole the table seems to lock instead of selected row

Hi Baron,

Thank you for your answer.

The problem is that multiple users can read and update the same field
simultaneously (worse case) which could lead to unpredictable problems.
According to the MySql online documentation
(http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads .html) this is
the way to solve this problem.

What I really want is the following:
When person A is reading and updating a field value, person B should not be
able to do this simultaneously. Person B has to wait till the Person A has
finished his work.

Are there other more robust solutions to solve such a problem?

Thank you in advance.

Regards,

Johan Machielse

----- Original Message -----
From: "Baron Schwartz"
To: "Johan Machielse"
Cc:
Sent: Friday, January 15, 2010 3:14 AM
Subject: Re: When using "FOR UPDATE" whole the table seems to lock instead
of selected row


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
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/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: When using "FOR UPDATE" whole the table seems to lock instead of

On Fri, Jan 15, 2010 at 2:54 AM, Johan Machielse
wrote:
> The problem is that multiple users can read and update the same field
> simultaneously (worse case) which could lead to unpredictable problems.

There are other ways to do handle most cases. For example:
UPDATE table SET value = value + 1 WHERE key = 7;

If you need to grab the value after the insert, you can get it from
last_insert_id:
UPDATE table SET value = last_insert_id(value + 1) WHERE key = 7;

However, if your situation is more complex than that, FOR UPDATE is
usually a good solution.

> What I really want is the following:
> When person A is reading and updating a field value, person B should not be
> able to do this simultaneously. Person B has to wait till the Person A has
> finished his work.

FOR UPDATE is the right solution for that. Your only issue seems to
be that you feel too many rows are being locked. That's an internal
implementation issue, but you may be able to change it by adjusting
which columns have indexes and keeping your statistics up to date. Or
there may not be enough cardinality on the column you're using in the
query to lock specific rows. Using EXPLAIN on the SELECT query might
tell you more about what's happening.

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: When using "FOR UPDATE" whole the table seems to lock insteadof selected row

I think Baron was referring to a technique like this:

you sell a t-shirt, UPDATE table SET t=3Dt-X WHERE t >=3D X, if you get row=
s affected, it's sold and ok. if not, the stock ran out before the operatio=
n. but it's safe. see http://dev.mysql.com/tech-resources/articles/storage-=
engine/part_3.html


-----Original Message-----
From: pharkins@gmail.com [mailto:pharkins@gmail.com] On Behalf Of Perrin Ha=
rkins
Sent: Friday, January 15, 2010 6:08 AM
To: Johan Machielse
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: When using "FOR UPDATE" whole the table seems to lock instead =
of selected row

On Fri, Jan 15, 2010 at 2:54 AM, Johan Machielse
wrote:
> The problem is that multiple users can read and update the same field
> simultaneously (worse case) which could lead to unpredictable problems.

There are other ways to do handle most cases. For example:
UPDATE table SET value =3D value + 1 WHERE key =3D 7;

If you need to grab the value after the insert, you can get it from
last_insert_id:
UPDATE table SET value =3D last_insert_id(value + 1) WHERE key =3D 7;

However, if your situation is more complex than that, FOR UPDATE is
usually a good solution.

> What I really want is the following:
> When person A is reading and updating a field value, person B should not =
be
> able to do this simultaneously. Person B has to wait till the Person A ha=
s
> finished his work.

FOR UPDATE is the right solution for that. Your only issue seems to
be that you feel too many rows are being locked. That's an internal
implementation issue, but you may be able to change it by adjusting
which columns have indexes and keeping your statistics up to date. Or
there may not be enough cardinality on the column you're using in the
query to lock specific rows. Using EXPLAIN on the SELECT query might
tell you more about what's happening.

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg