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