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