Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts (using

2002-10-01 Thread iod iod 


Tuuri,

Thank you for the follow-up. It's great that our efforts were not in vain and I could 
contribute towards resolving the issue. I will be looking forward to the next release 
of InnoDB/MySQL-Max.

-
regards,

Afroze H. Zubairi (IOD)
-


- Original Message -
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
Date: Tue, 1 Oct 2002 15:00:37 +0300
To: "\"iod iod\"" <[EMAIL PROTECTED]>
Subject: Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts (using


> IOD,
> 
> thank you for the bug report. I was able to repeat the problem with you
> scripts. The bug was that InnoDB failed to release at an SQL statement end
> such AUTO-INC locks which were only granted after a lock wait. They were
> only released at the transaction commit.
> 
> Since AUTO-INC locks normally are held for a short time, AUTO-INC lock waits
> are rare, and many tests fail to notice this bug. Only under a stress test
> like the one you wrote this bug becomes visible.
> 
> Below is a patch which fixes the bug.
> 
> Best regards,
> 
> Heikki
> Innobase Oy
> sql query
> 
> ChangeSet
>   1.1201 02/10/01 13:47:58 [EMAIL PROTECTED] +1 -0
>   lock0lock.c:
> Fix bug: the AUTO-INC lock was held to the end of trx if it was granted
> after a lock wait
> 
>   innobase/lock/lock0lock.c
> 1.16 02/10/01 13:47:39 [EMAIL PROTECTED] +13 -0
> Fix bug: the AUTO-INC lock was held to the end of trx if it was granted
> after a lock wait
> 
> # This is a BitKeeper patch.  What follows are the unified diffs for the
> # set of deltas contained in the patch.  The rest of the patch, the part
> # that BitKeeper cares about, is below these diffs.
> # User: heikki
> # Host: hundin.mysql.fi
> # Root: /home/heikki/mysql
> 
> --- 1.15/innobase/lock/lock0lock.c Sun Aug 11 18:34:57 2002
> +++ 1.16/innobase/lock/lock0lock.c Tue Oct  1 13:47:39 2002
> @@ -1863,6 +1863,19 @@
>   ut_ad(mutex_own(&kernel_mutex));
> 
>   lock_reset_lock_and_trx_wait(lock);
> +
> + if (lock_get_mode(lock) == LOCK_AUTO_INC) {
> +
> +  if (lock->trx->auto_inc_lock != NULL) {
> +  fprintf(stderr,
> + "InnoDB: Error: trx already had an AUTO-INC lock!\n");
> +  }
> +
> +  /* Store pointer to lock to trx so that we know to
> +  release it at the end of the SQL statement */
> +
> +  lock->trx->auto_inc_lock = lock;
> + }
> 
>   if (lock_print_waits) {
>   printf("Lock wait for trx %lu ends\n",
> 
> - Original Message -
> From: ""iod iod"" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Tuesday, October 01, 2002 11:10 AM
> Subject: Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts (using
> 
> 
> >
> > Thank you for your response.
> >
> > We tried your example here on shell and it looks like InnoDB locks the
> table only for the first transaction after db startup and this lock is held
> for the whole transaction (like you observed).
> >
> > But, contrary to this scenario we have experienced some strange behavior,
> such that table level AUTO-INC locks are being applied REPEATEDLY (duration
> of ENTIRE transaction), even though the database has undergone multiple,
> commited transactions previously. This behavior is very prominent when you
> have multiple processes with overlapping, concurrent transactions and you
> are executing DML statements in a loop (I tried a delay after each iteration
> to prevent a locks being held too long, but it did not seem to work). It so
> happens that the lock is not released by the process until the transaction
> is complete.
> >
> > I am attaching a simplified perl script (concur-test.pl) and a shell
> script (go.sh) used to invoke these processes (URL:
> http://www.geocities.com/yoda_pk/innodbchk.tar.gz ). Please create the table
> (SQL provided), run the shell script and observe the InnoDB monitor log for
> deadlocks. We have observed various AUTO-INC table level locks which are
> applied every time we run the tests (without restarting the mysql database
> engine) and these locks are held for the whole transaction and not just one
> particular statement, EVERY time.
> >
> > PLATFORM: RH Linux 7.3/MySQL-Max-3.23.52-1/Perl DBI
> >
> > Looking forward to your observations.
> >
> > 
> > regards,
> >
> > IOD
> > 
> >
> >
> >
> >
> >
> > Subject: Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts
> > (using
> > > Iod,
> > >
> > > - Original Message -
> >

Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts (using

2002-10-01 Thread Heikki Tuuri 

IOD,

thank you for the bug report. I was able to repeat the problem with you
scripts. The bug was that InnoDB failed to release at an SQL statement end
such AUTO-INC locks which were only granted after a lock wait. They were
only released at the transaction commit.

Since AUTO-INC locks normally are held for a short time, AUTO-INC lock waits
are rare, and many tests fail to notice this bug. Only under a stress test
like the one you wrote this bug becomes visible.

Below is a patch which fixes the bug.

Best regards,

Heikki
Innobase Oy
sql query

ChangeSet
  1.1201 02/10/01 13:47:58 [EMAIL PROTECTED] +1 -0
  lock0lock.c:
Fix bug: the AUTO-INC lock was held to the end of trx if it was granted
after a lock wait

  innobase/lock/lock0lock.c
1.16 02/10/01 13:47:39 [EMAIL PROTECTED] +13 -0
Fix bug: the AUTO-INC lock was held to the end of trx if it was granted
after a lock wait

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: heikki
# Host: hundin.mysql.fi
# Root: /home/heikki/mysql

--- 1.15/innobase/lock/lock0lock.c Sun Aug 11 18:34:57 2002
+++ 1.16/innobase/lock/lock0lock.c Tue Oct  1 13:47:39 2002
@@ -1863,6 +1863,19 @@
  ut_ad(mutex_own(&kernel_mutex));

  lock_reset_lock_and_trx_wait(lock);
+
+ if (lock_get_mode(lock) == LOCK_AUTO_INC) {
+
+  if (lock->trx->auto_inc_lock != NULL) {
+  fprintf(stderr,
+ "InnoDB: Error: trx already had an AUTO-INC lock!\n");
+  }
+
+  /* Store pointer to lock to trx so that we know to
+  release it at the end of the SQL statement */
+
+  lock->trx->auto_inc_lock = lock;
+ }

  if (lock_print_waits) {
  printf("Lock wait for trx %lu ends\n",

- Original Message -
From: ""iod iod"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Tuesday, October 01, 2002 11:10 AM
Subject: Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts (using


>
> Thank you for your response.
>
> We tried your example here on shell and it looks like InnoDB locks the
table only for the first transaction after db startup and this lock is held
for the whole transaction (like you observed).
>
> But, contrary to this scenario we have experienced some strange behavior,
such that table level AUTO-INC locks are being applied REPEATEDLY (duration
of ENTIRE transaction), even though the database has undergone multiple,
commited transactions previously. This behavior is very prominent when you
have multiple processes with overlapping, concurrent transactions and you
are executing DML statements in a loop (I tried a delay after each iteration
to prevent a locks being held too long, but it did not seem to work). It so
happens that the lock is not released by the process until the transaction
is complete.
>
> I am attaching a simplified perl script (concur-test.pl) and a shell
script (go.sh) used to invoke these processes (URL:
http://www.geocities.com/yoda_pk/innodbchk.tar.gz ). Please create the table
(SQL provided), run the shell script and observe the InnoDB monitor log for
deadlocks. We have observed various AUTO-INC table level locks which are
applied every time we run the tests (without restarting the mysql database
engine) and these locks are held for the whole transaction and not just one
particular statement, EVERY time.
>
> PLATFORM: RH Linux 7.3/MySQL-Max-3.23.52-1/Perl DBI
>
> Looking forward to your observations.
>
> ----
> regards,
>
> IOD
> 
>
>
>
>
>
> Subject: Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts
> (using
> > Iod,
> >
> > - Original Message -
> > From: ""iod iod"" <[EMAIL PROTECTED]>
>
> > Newsgroups: mailing.database.mysql
> > Sent: Thursday, September 26, 2002 8:14 AM
> > Subject: InnoDB AUTO-INC lock timeouts / deadlocks on inserts
> (using
> >
> >
> > >
> > > We have been using InnoDB with MySQL (MySQL-Max-3.23.52-1)
> for transaction
> > support in some complex situations in our application when
> maintaining
> > consistency is critical.
> > >
> > > Recently we did some stress testing by simulating 10
> concurrent users
> > (perl/DBD) doing large number of inserts on a few tables. Note
> that these
> > tables have indexed fields which are 'INT' & 'AUTO_INCREMENT'.
> > >
> > > We are getting lock timeouts on concurrent inserts (in
> concurrent
> > TRANSACTIONS) very frequently, although it works fine for a
> single
> > non-concurrent transaction. Removing transactions eliminates the
> deadlocks
> > in concurr

Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts (using

2002-10-01 Thread iod iod 


Thank you for your response. 

We tried your example here on shell and it looks like InnoDB locks the table only for 
the first transaction after db startup and this lock is held for the whole transaction 
(like you observed).

But, contrary to this scenario we have experienced some strange behavior, such that 
table level AUTO-INC locks are being applied REPEATEDLY (duration of ENTIRE 
transaction), even though the database has undergone multiple, commited transactions 
previously. This behavior is very prominent when you have multiple processes with 
overlapping, concurrent transactions and you are executing DML statements in a loop (I 
tried a delay after each iteration to prevent a locks being held too long, but it did 
not seem to work). It so happens that the lock is not released by the process until 
the transaction is complete.

I am attaching a simplified perl script (concur-test.pl) and a shell script (go.sh) 
used to invoke these processes (URL: http://www.geocities.com/yoda_pk/innodbchk.tar.gz 
). Please create the table (SQL provided), run the shell script and observe the InnoDB 
monitor log for deadlocks. We have observed various AUTO-INC table level locks which 
are applied every time we run the tests (without restarting the mysql database engine) 
and these locks are held for the whole transaction and not just one particular 
statement, EVERY time. 

PLATFORM: RH Linux 7.3/MySQL-Max-3.23.52-1/Perl DBI

Looking forward to your observations.


regards,

IOD






Subject: Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts
(using
> Iod,
> 
> - Original Message -
> From: ""iod iod"" <[EMAIL PROTECTED]>

> Newsgroups: mailing.database.mysql
> Sent: Thursday, September 26, 2002 8:14 AM
> Subject: InnoDB AUTO-INC lock timeouts / deadlocks on inserts
(using
> 
> 
> >
> > We have been using InnoDB with MySQL (MySQL-Max-3.23.52-1)
for transaction
> support in some complex situations in our application when
maintaining
> consistency is critical.
> >
> > Recently we did some stress testing by simulating 10
concurrent users
> (perl/DBD) doing large number of inserts on a few tables. Note
that these
> tables have indexed fields which are 'INT' & 'AUTO_INCREMENT'.
> >
> > We are getting lock timeouts on concurrent inserts (in
concurrent
> TRANSACTIONS) very frequently, although it works fine for a
single
> non-concurrent transaction. Removing transactions eliminates the
deadlocks
> in concurrent runs, but we cannot guarantee the consistency of
our data.
> >
> > RELEVANT 'my.cnf' SETTINGS:
> > innodb_lock_wait_timeout = 50
> > innodb_thread_concurrency = 2 (to reduce thread contention)
> >
> > When we enabled InnoDB Monitor we saw messages like the
following:
> >
> > 
> >
> > INSERT INTO BasketItems(BalQuantity, ...
> > --- TRX HAS BEEN WAITING 46 SEC FOR THIS LOCK TO BE
GRANTED:
> > TABLE LOCK table ca3/BasketItems trx id 0 26561327
lock_mode AUTO-INC
> waiting
> >
> > 
> >
> > At times there are around 4 to 5 such simultaneous waits in
the log and
> ultimately the lock times out and the process terminates.
> >
> > After reading the docs, it seems like InnoDB applies a
table level lock on
> the table and so other inserts get blocked. But, considering the
time a
> process has to wait ( > 50s) it looks like the locks are
being held for a
> very long time. According to the InnoDB documentation
> (http://www.innodb.com/ibman.html#InnoDB_auto_inc):

> >
> > "When accessing the auto-increment counter InnoDB uses
a special table
> level lock AUTO-INC lock which it keeps to the end of the
current SQL
> statement, not to the end of the transaction."
> >
> > Is it possible that the locks are being held for the WHOLE
transaction
> instead of just THAT SQL statement? Is it because InnoDB's
implementation of
> AUTO-INC locks needs to be more efficient?
> >
> 
> the auto-inc locks are necessary because MySQL binlogging
assumes that
> auto-inc values for a single SQL statement are determined by the
auto-inc
> value of the first insert in that statement.
> 
> If you suspect that there is a bug and the auto-inc lock is held
longer, you
> should look at InnoDB Lock Monitor output.
> 
> But also look at possible other bottlenecks. Log flushes, fsync,
etc.
> 
> I tested this now. Note that after a table creation or a mysqld
startup, the
> very FIRST insert to the table acquires a next-key lock which is
held for
> the duration of the whole transaction. But subsequent inserts
only acquire
> the AUTO-INC lock, which is held only for the duration of the
SQL statement.
> 
&

Re: InnoDB AUTO-INC lock timeouts / deadlocks on inserts (using

2002-09-26 Thread Heikki Tuuri 

Iod,

- Original Message -
From: ""iod iod"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, September 26, 2002 8:14 AM
Subject: InnoDB AUTO-INC lock timeouts / deadlocks on inserts (using


>
> We have been using InnoDB with MySQL (MySQL-Max-3.23.52-1) for transaction
support in some complex situations in our application when maintaining
consistency is critical.
>
> Recently we did some stress testing by simulating 10 concurrent users
(perl/DBD) doing large number of inserts on a few tables. Note that these
tables have indexed fields which are 'INT' & 'AUTO_INCREMENT'.
>
> We are getting lock timeouts on concurrent inserts (in concurrent
TRANSACTIONS) very frequently, although it works fine for a single
non-concurrent transaction. Removing transactions eliminates the deadlocks
in concurrent runs, but we cannot guarantee the consistency of our data.
>
> RELEVANT 'my.cnf' SETTINGS:
> innodb_lock_wait_timeout = 50
> innodb_thread_concurrency = 2 (to reduce thread contention)
>
> When we enabled InnoDB Monitor we saw messages like the following:
>
> 
>
> INSERT INTO BasketItems(BalQuantity, ...
> --- TRX HAS BEEN WAITING 46 SEC FOR THIS LOCK TO BE GRANTED:
> TABLE LOCK table ca3/BasketItems trx id 0 26561327 lock_mode AUTO-INC
waiting
>
> 
>
> At times there are around 4 to 5 such simultaneous waits in the log and
ultimately the lock times out and the process terminates.
>
> After reading the docs, it seems like InnoDB applies a table level lock on
the table and so other inserts get blocked. But, considering the time a
process has to wait ( > 50s) it looks like the locks are being held for a
very long time. According to the InnoDB documentation
(http://www.innodb.com/ibman.html#InnoDB_auto_inc):
>
> "When accessing the auto-increment counter InnoDB uses a special table
level lock AUTO-INC lock which it keeps to the end of the current SQL
statement, not to the end of the transaction."
>
> Is it possible that the locks are being held for the WHOLE transaction
instead of just THAT SQL statement? Is it because InnoDB's implementation of
AUTO-INC locks needs to be more efficient?
>

the auto-inc locks are necessary because MySQL binlogging assumes that
auto-inc values for a single SQL statement are determined by the auto-inc
value of the first insert in that statement.

If you suspect that there is a bug and the auto-inc lock is held longer, you
should look at InnoDB Lock Monitor output.

But also look at possible other bottlenecks. Log flushes, fsync, etc.

I tested this now. Note that after a table creation or a mysqld startup, the
very FIRST insert to the table acquires a next-key lock which is held for
the duration of the whole transaction. But subsequent inserts only acquire
the AUTO-INC lock, which is held only for the duration of the SQL statement.


User 1:


heikki@hundin:~/mysql-max-3.23.52-pc-linux-gnu-i686/bin> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.52-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into kkk(b) values (100);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into kkk(b) values (101);
Query OK, 1 row affected (0.00 sec)

mysql> insert into kkk(b) values (103);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from kkk;
+---+--+
| a | b|
+---+--+
| 1 |   34 |
| 2 |   34 |
| 3 |   12 |
| 4 |   15 |
| 5 |  100 |
| 6 |  101 |
| 7 |  102 |
| 8 |  103 |
| 9 |  104 |
+---+--+
9 rows in set (0.00 sec)

mysql>

User 2:
..

heikki@hundin:~/mysql-max-3.23.52-pc-linux-gnu-i686/bin> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.52-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into kkk(b) values (102);
Query OK, 1 row affected (0.00 sec)

mysql> insert into kkk(b) values (104);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>


> ---
> regards,
>
> IOD
> ---
> --

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, hot backup, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com

sql query




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

InnoDB AUTO-INC lock timeouts / deadlocks on inserts (using AUTO_INCREMENT column)

2002-09-25 Thread iod iod 


We have been using InnoDB with MySQL (MySQL-Max-3.23.52-1) for transaction support in 
some complex situations in our application when maintaining consistency is critical.

Recently we did some stress testing by simulating 10 concurrent users (perl/DBD) doing 
large number of inserts on a few tables. Note that these tables have indexed fields 
which are 'INT' & 'AUTO_INCREMENT'.

We are getting lock timeouts on concurrent inserts (in concurrent TRANSACTIONS) very 
frequently, although it works fine for a single non-concurrent transaction. Removing 
transactions eliminates the deadlocks in concurrent runs, but we cannot guarantee the 
consistency of our data.

RELEVANT 'my.cnf' SETTINGS: 
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 2 (to reduce thread contention)

When we enabled InnoDB Monitor we saw messages like the following:



INSERT INTO BasketItems(BalQuantity, ...
--- TRX HAS BEEN WAITING 46 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table ca3/BasketItems trx id 0 26561327 lock_mode AUTO-INC waiting



At times there are around 4 to 5 such simultaneous waits in the log and ultimately the 
lock times out and the process terminates. 

After reading the docs, it seems like InnoDB applies a table level lock on the table 
and so other inserts get blocked. But, considering the time a process has to wait ( > 
50s) it looks like the locks are being held for a very long time. According to the 
InnoDB documentation (http://www.innodb.com/ibman.html#InnoDB_auto_inc):

"When accessing the auto-increment counter InnoDB uses a special table level lock 
AUTO-INC lock which it keeps to the end of the current SQL statement, not to the end 
of the transaction."

Is it possible that the locks are being held for the WHOLE transaction instead of just 
THAT SQL statement? Is it because InnoDB's implementation of AUTO-INC locks needs to 
be more efficient?


---
regards,

IOD
---
-- 
__
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php