Select .. for update question

[jayson_13]

Hi,

I need to implement a counter and i face problem of locking so hope
that u guys can help me.

I try to do test like this :

1st connection
SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
(when i execute this statement and i guess that this will lock the
record and prevent other connection to update that record right?)
2nd connection
Update nextkey SET inextkey = inextkey + 1 WHERE tblname = 'PLCN';
(I execute this in another connection after i have executed the
select for update statement in the first connection. However, this
statement does update the record!!!)

My major concern is that how can i prevent 2 ppl getting the same
counter. TQ!!!!!!!!
I am using InnoDb table + Mysql 4.0.17

[Chris Hope]

jayson_13 wrote:

Hi,

I need to implement a counter and i face problem of locking so hope
that u guys can help me.

I try to do test like this :

1st connection
SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
(when i execute this statement and i guess that this will lock the
record and prevent other connection to update that record right?)
2nd connection
Update nextkey SET inextkey = inextkey + 1 WHERE tblname = 'PLCN';
(I execute this in another connection after i have executed the
select for update statement in the first connection. However, this
statement does update the record!!!)

My major concern is that how can i prevent 2 ppl getting the same
counter. TQ!!!!!!!!
I am using InnoDb table + Mysql 4.0.17

Google is your friend:
http://www.google.com/search?q=mysql+lock+table

The first entry is the page you want from the MySQL manual.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Heikki Tuuri]

Jayson,

----- Original Message -----
From: "Chris Hope" <bl*******@electrictoolbox.com>
Newsgroups: mailing.database.mysql
Sent: Friday, October 22, 2004 4:02 AM
Subject: Re: Select .. for update question

jayson_13 wrote:

Hi,

I need to implement a counter and i face problem of locking so hope
that u guys can help me.

I try to do test like this :

1st connection
SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
(when i execute this statement and i guess that this will lock the
record and prevent other connection to update that record right?)
2nd connection
Update nextkey SET inextkey = inextkey + 1 WHERE tblname = 'PLCN';
(I execute this in another connection after i have executed the
select for update statement in the first connection. However, this
statement does update the record!!!)

My major concern is that how can i prevent 2 ppl getting the same
counter. TQ!!!!!!!!
you have probably not set

SET AUTOCOMMIT=0

and the lock by the 1st connection is released in the autocommit immediately
after you execute the SELECT ... FOR UPDATE.
I am using InnoDb table + Mysql 4.0.17
Google is your friend:
http://www.google.com/search?q=mysql+lock+table

The first entry is the page you want from the MySQL manual.

Using LOCK TABLES is not a very good method for InnoDB tables, because
MySQL's table locking mechanism does not have deadlock detection in
combination with row locks inside InnoDB. We will soon implement LOCK TABLES
TRANSACTIONAL that will work like in DB2, SQL Server, Oracle, and will be
the recommended method for locking InnoDB type tables.
--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/

[jayson_13]

Hi Turri,

I am testing it using mysql front 2.2. The following is how i
test... please tell me what the hell i have done wrong... TQ !

1st Connection
SET Autocommit=0;
SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
(if i am not mistaken, at this point, the transaction is not yet
completed. so the record supposed to be locked right?)

2nd Connection
Update nextkey SET inextkey = inextkey + 1 WHERE tblname =
'PLCN';

The 2nd connection still manage to update the record!

Please help... i don't know what i did wrong ...

TQ!!!!!!!


"Heikki Tuuri" <He**********@innodb.com> wrote in message news:<jj************@read3.inet.fi>...

Jayson,

----- Original Message -----
From: "Chris Hope" <bl*******@electrictoolbox.com>
Newsgroups: mailing.database.mysql
Sent: Friday, October 22, 2004 4:02 AM
Subject: Re: Select .. for update question

jayson_13 wrote:

Hi,

I need to implement a counter and i face problem of locking so hope
that u guys can help me.

I try to do test like this :

1st connection
SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
(when i execute this statement and i guess that this will lock the
record and prevent other connection to update that record right?)
2nd connection
Update nextkey SET inextkey = inextkey + 1 WHERE tblname = 'PLCN';
(I execute this in another connection after i have executed the
select for update statement in the first connection. However, this
statement does update the record!!!)

My major concern is that how can i prevent 2 ppl getting the same
counter. TQ!!!!!!!!
you have probably not set

SET AUTOCOMMIT=0

and the lock by the 1st connection is released in the autocommit immediately
after you execute the SELECT ... FOR UPDATE.
I am using InnoDb table + Mysql 4.0.17

Google is your friend:
http://www.google.com/search?q=mysql+lock+table

The first entry is the page you want from the MySQL manual.

Using LOCK TABLES is not a very good method for InnoDB tables, because
MySQL's table locking mechanism does not have deadlock detection in
combination with row locks inside InnoDB. We will soon implement LOCK TABLES
TRANSACTIONAL that will work like in DB2, SQL Server, Oracle, and will be
the recommended method for locking InnoDB type tables.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/

[Heikki Tuuri]

Jayson,

maybe MySQL-Front terminates the connection and commits the transaction
after EVERY SQL statement that you run?

Please test with the mysql command-line SQL client.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/

"jayson_13" <ja*******@yahoo.com> kirjoitti
viestissä:85**************************@posting.goo gle.com...

Hi Turri,

I am testing it using mysql front 2.2. The following is how i
test... please tell me what the hell i have done wrong... TQ !

1st Connection
SET Autocommit=0;
SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
(if i am not mistaken, at this point, the transaction is not yet
completed. so the record supposed to be locked right?)

2nd Connection
Update nextkey SET inextkey = inextkey + 1 WHERE tblname =
'PLCN';

The 2nd connection still manage to update the record!

Please help... i don't know what i did wrong ...

TQ!!!!!!!


"Heikki Tuuri" <He**********@innodb.com> wrote in message
news:<jj************@read3.inet.fi>...

Jayson,

----- Original Message -----
From: "Chris Hope" <bl*******@electrictoolbox.com>
Newsgroups: mailing.database.mysql
Sent: Friday, October 22, 2004 4:02 AM
Subject: Re: Select .. for update question

> jayson_13 wrote:
>
>> Hi,
>>
>> I need to implement a counter and i face problem of locking so hope
>> that u guys can help me.
>>
>> I try to do test like this :
>>
>> 1st connection
>> SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
>> (when i execute this statement and i guess that this will lock the
>> record and prevent other connection to update that record right?)
>>
>>
>> 2nd connection
>> Update nextkey SET inextkey = inextkey + 1 WHERE tblname = 'PLCN';
>> (I execute this in another connection after i have executed the
>> select for update statement in the first connection. However, this
>> statement does update the record!!!)
>>
>> My major concern is that how can i prevent 2 ppl getting the same
>> counter. TQ!!!!!!!!

you have probably not set

SET AUTOCOMMIT=0

and the lock by the 1st connection is released in the autocommit
immediately
after you execute the SELECT ... FOR UPDATE.

>> I am using InnoDb table + Mysql 4.0.17
>
> Google is your friend:
> http://www.google.com/search?q=mysql+lock+table
>
> The first entry is the page you want from the MySQL manual.

Using LOCK TABLES is not a very good method for InnoDB tables, because
MySQL's table locking mechanism does not have deadlock detection in
combination with row locks inside InnoDB. We will soon implement LOCK
TABLES
TRANSACTIONAL that will work like in DB2, SQL Server, Oracle, and will be
the recommended method for locking InnoDB type tables.

> --
> Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/

[jayson_13]

Hi Turri,

TQ! i got it work now !!! so stupid i am... :)



"Heikki Tuuri" <He**********@innodb.com> wrote in message news:<9Q****************@read3.inet.fi>...

Jayson,

maybe MySQL-Front terminates the connection and commits the transaction
after EVERY SQL statement that you run?

Please test with the mysql command-line SQL client.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/

"jayson_13" <ja*******@yahoo.com> kirjoitti
viestissä:85**************************@posting.goo gle.com...

Hi Turri,

I am testing it using mysql front 2.2. The following is how i
test... please tell me what the hell i have done wrong... TQ !

1st Connection
SET Autocommit=0;
SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
(if i am not mistaken, at this point, the transaction is not yet
completed. so the record supposed to be locked right?)

2nd Connection
Update nextkey SET inextkey = inextkey + 1 WHERE tblname =
'PLCN';

The 2nd connection still manage to update the record!

Please help... i don't know what i did wrong ...

TQ!!!!!!!


"Heikki Tuuri" <He**********@innodb.com> wrote in message
news:<jj************@read3.inet.fi>...

Jayson,

----- Original Message -----
From: "Chris Hope" <bl*******@electrictoolbox.com>
Newsgroups: mailing.database.mysql
Sent: Friday, October 22, 2004 4:02 AM
Subject: Re: Select .. for update question

> jayson_13 wrote:
>
>> Hi,
>>
>> I need to implement a counter and i face problem of locking so hope
>> that u guys can help me.
>>
>> I try to do test like this :
>>
>> 1st connection
>> SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
>> (when i execute this statement and i guess that this will lock the
>> record and prevent other connection to update that record right?)
>>
>>
>> 2nd connection
>> Update nextkey SET inextkey = inextkey + 1 WHERE tblname = 'PLCN';
>> (I execute this in another connection after i have executed the
>> select for update statement in the first connection. However, this
>> statement does update the record!!!)
>>
>> My major concern is that how can i prevent 2 ppl getting the same
>> counter. TQ!!!!!!!!

you have probably not set

SET AUTOCOMMIT=0

and the lock by the 1st connection is released in the autocommit
immediately
after you execute the SELECT ... FOR UPDATE.

>> I am using InnoDb table + Mysql 4.0.17
>
> Google is your friend:
> http://www.google.com/search?q=mysql+lock+table
>
> The first entry is the page you want from the MySQL manual.

Using LOCK TABLES is not a very good method for InnoDB tables, because
MySQL's table locking mechanism does not have deadlock detection in
combination with row locks inside InnoDB. We will soon implement LOCK
TABLES
TRANSACTIONAL that will work like in DB2, SQL Server, Oracle, and will be
the recommended method for locking InnoDB type tables.

> --
> Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/