MySql error> Exceeded wait wait wait wait; try restarting transaction SQLState: 41000 VendorError: 1205

Question

I had many years, and I could not figure it out. I have no idea what might cause these locks.

Error: Lock wait timeout exceeded; try restarting transaction SQLState: 41000 VendorError: 1205 Lock wait timeout exceeded; try restarting transaction SQLState: 41000 VendorError: 1205

An SQL statement is a single insert statement that runs inside a transaction. All inserts have this shape, so there are no built-in inserts or inserts in blend mode, etc.

INSERT INTO attachment( id, entityid, entitytype , addeduserid , deleteduserid , fullpath , filename, status, creationdate, lastupdated, deletiondate, hasfile,notes,history,type,mimeinfo,archivedby,archivedon, referencedate,changedby,changedon ) values (0,0,2,360,null,NULL,NULL,1,'2013-02-20 08:45:31','2013-02-20 08:45:31',NULL,0,NULL,'20/02/2013 08:45:UserA:File uploaded internally. <br>',0,NULL,null,NULL,NULL,null,NULL);

System Configuration: Mysql Version: "Server Version: 5.1.61 Source Distribution" (on Redhat)

Storage: INNODB

INNODB related configuration (partially edited from my.cnf):

 innodb_file_per_table=1 innodb_buffer_pool_size=3G innodb_additional_mem_pool_size=20M innodb_log_file_size=512M innodb_log_files_in_group=2 innodb_log_buffer_size=16M innodb_support_xa=1 innodb_doublewrite=1 innodb_thread_concurrency=0 innodb_flush_log_at_trx_commit=2 innodb_autoinc_lock_mode=2** innodb_rollback_on_timeout=1 innodb_locks_unsafe_for_binlog=1** thread_cache_size=8 query_cache_size=256M query_cache_limit=4M table_cache=2048 table_definition_cache=1024 tmp_table_size=512M max_heap_table_size=512M transaction-isolation=READ-COMMITTED** innodb_table_locks=0** innodb_lock_wait_timeout=50** 

** They have been specially added in connection with this problem.

Usually:

The system (that is, it has 6 instances of applications, each of which has the same database structure that runs in the same mysql instance) can work normally for several days, and then it can start when the "Block expectations" function starts occur and will usually appear in groups throughout the day. Each individual error will occur repeatedly, because as soon as it fails, I will try again, and usually a second attempt will fail. I set up a retry 4 times. Often locks will only occur on a few different tables.

Today's specific instance of the problem:

This morning on the attachment table from last night there was no insert on the table. There were no updates since the previous night either. If the locks are not associated with other users doing updates and inserts, can certain select statements cause locks? I tried to ensure that all select statements use attachment_general_index ?

Due to the fact that I basically get this for a couple of different tables - here is the structure of this table.

 CREATE TABLE `attachment` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `entityid` int(10) unsigned DEFAULT NULL, `entitytype` tinyint(3) unsigned NOT NULL DEFAULT '0', `addeduserid` int(10) unsigned NOT NULL, `deleteduserid` int(10) unsigned DEFAULT NULL, `fullpath` varchar(255) DEFAULT NULL, `filename` varchar(255) DEFAULT NULL, `status` tinyint(3) unsigned NOT NULL DEFAULT '0', `creationdate` varchar(40) DEFAULT NULL, `lastupdated` varchar(40) DEFAULT NULL, `deletiondate` varchar(40) DEFAULT NULL, `hasfile` tinyint(3) unsigned NOT NULL DEFAULT '0', `notes` text, `history` text, `type` tinyint(3) unsigned DEFAULT '0', `lastupdatedby` int(10) DEFAULT '0', `lastupdatedinfo` varchar(255) DEFAULT NULL, `mimeinfo` varchar(255) DEFAULT NULL, `archivedby` int(10) unsigned DEFAULT NULL, `archivedon` varchar(40) DEFAULT NULL, `referencedate` varchar(40) DEFAULT NULL, `changedby` int(10) unsigned DEFAULT NULL, `changedon` varchar(40) DEFAULT NULL, PRIMARY KEY (`id`), KEY `attachment_addeduserid_fkey` (`addeduserid`), KEY `attachment_deleteduserid_fkey` (`deleteduserid`), KEY `attachment_archivedby_fkey` (`archivedby`), KEY `attachment_changedby_fkey` (`changedby`), KEY `attachment_general_index` (`entitytype`,`entityid`,`status`,`type`), CONSTRAINT `attachment_ibfk_1` FOREIGN KEY (`addeduserid`) REFERENCES `user` (`id`), CONSTRAINT `attachment_ibfk_2` FOREIGN KEY (`deleteduserid`) REFERENCES `user` (`id`), CONSTRAINT `attachment_ibfk_3` FOREIGN KEY (`archivedby`) REFERENCES `user` (`id`), CONSTRAINT `attachment_ibfk_4` FOREIGN KEY (`changedby`) REFERENCES `user` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3619 DEFAULT CHARSET=latin1$$ 

I attached the recent SHOW INNODB STATUS, this is from today, and since yesterday there has been no blockage. I don’t understand this whole exit, but the main thing is that castles never appear here. I believe that they are not classified as deadlocks?

https://docs.google.com/document/d/1Hslf2B594n8ofAUYxN54Gh8FrSCIFNGGMtthVI_Lv4k/pub

Is this interesting only for the area of ​​dead castles? If there are other areas, I will try to collect when this happens and can provide.

Any help would be appreciated.

Nick

Answer 1

I would like to share the Eureka moment with those of you who scratch your heads at transaction timeouts and find that none of the proposed server configuration changes helps.

I floundered to such an extent that I seriously considered rewriting some of my applications so that I could take into account transaction timeouts (there is a collective moan throughout the world).

I'm paranoid about losing any of my business transactions, so I run a cron job that runs a full mysqldump every 10 minutes (that's twice as much duplication) for a day.

What I discovered is mysqldump hogs on the server, it locks tables and pretty much prohibits anything else from using the database when it does. My moment at Eureka came when I discovered that the transaction failure coincided with the mysqldump runtime.

In short, there are three command line options that will prevent the killing of your mysqldump server. it

• - one transaction
• - fast
• - table lock = false

Thanks a lot CA3LE @ How can I slow down the MySQL dump so as not to affect the current server load? to enlighten me.

Answer 2

(This is probably a comment, but I have too much text and formatting is required).

I think this is a very similar problem to that described in , where:

• One transaction has a lock at the end of the table.
• The second transaction locks most of the table.
• The first transaction tries to update / insert into the lock held by the second transaction. This fails, so one of the transactions is chosen in order to die.

Thanks for posting show status . You are right that the deadlock shown does not seem to be related to the table you asked for, but it seems to be the same as the one from Xaprb.

Is this interesting only for the area of ​​dead castles?

Yes, the exact parts are:

 Transaction 1 UPDATE operative SET lastupdated='2013-02-19 17:12:44'=N<EDITED> RECORD LOCKS space id 1789 page no 3622 n bits 112 index `PRIMARY` of table `<EDITED> `.`operative` trx id 0 233901602 lock_mode X locks rec but not gap waiting *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1789 page no 3622 n bits 112 index `PRIMARY` of table `<EDITED> `.`operative` trx id 0 233901602 lock_mode X locks rec but not gap waiting Transaction 2 INSERT INTO opdate(operativeId,opdate,updatingUser,dategroup,type,notes,lastupdated) values (....) RECORD LOCKS space id 1789 page no 3622 n bits 112 index `PRIMARY` of table `<EDITED> `.`operative` trx id 0 233901603 lock mode S locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 830 page no 112 n bits 808 index `opdate_unique` of table `<EDITED> `.`opdate` trx id 0 233901603 lock mode S waiting Record lock, heap no 739 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 

This is very similar to the problem pointed to by xaprb. those.

• Transaction 2 inserted into the table and now contains the primary key lock.
• Transaction 1 scans the table to complete the update and expects this primary key to lock.
• Transaction 2 tries to do another insertion and needs to be locked, but it is forbidden to it, because Transaction 1 already has it (I actually guess there when you messed up the table name).

I would suggest to fix this deadlock first, and also try to solve the problem you asked for.

In fact, I think your problem may not appear in INNODB status. You get error code 1205 - this is ER_LOCK_WAIT_TIMEOUT, not error 1213 ER_LOCK_DEADLOCK. Therefore, although you do have a dead end, it is not classified as such.

I think that if you can do SHOW ENGINE INNODB STATUS while the problem arises, you should be able to see locks on inhibited transactions there, even if they do not appear as the last deadlock.