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 -----
> > > 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
> > >
> > >
> > >
> > --
> > __________________________________________________________
> > 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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
>
--
__________________________________________________________
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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
