By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,300 Members | 2,044 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,300 IT Pros & Developers. It's quick & easy.

Select .. for update question

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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/
Jul 20 '05 #2

P: n/a
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/
Jul 20 '05 #3

P: n/a
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/

Jul 20 '05 #4

P: n/a
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/

Jul 20 '05 #5

P: n/a
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/

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.