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
