![]() ![]() INSERT INTO `package_files` (`pkg_id`, `is_directory`, `directory`, `filename`) VALUES (70298, 1, 'usr/', NULL) MySQL thread id 393562, OS thread handle 0x7f61d18ce700, query id 5884411 localhost 127.0.0.1 archweb-update update TRANSACTION 32116C74, ACTIVE 1 sec insertingġ1 lock struct(s), heap size 1248, 6 row lock(s), undo log entries 2 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1 compact format info bits 0Ġ: len 7072656d756d asc supremum RECORD LOCKS space id 0 page no 139734 n bits 224 index `package_files_pkg_id` of table `archweb`.`package_files` trx id 32116C73 lock_mode X insert intention waiting *** (1) WAITING FOR THIS LOCK TO BE GRANTED: INSERT INTO `package_files` (`pkg_id`, `is_directory`, `directory`, `filename`) VALUES (70297, 1, 'usr/share/', NULL) MySQL thread id 393561, OS thread handle 0x7f61cc503700, query id 5884410 localhost 127.0.0.1 archweb-update update LOCK WAIT 12 lock struct(s), heap size 3112, 8 row lock(s), undo log entries 4 TRANSACTION 32116C73, ACTIVE 1 sec inserting Here is the output from SHOW ENGINE INNODB STATUS. The first step to debugging deadlocks is to ask the database what the heck happened. ![]() OperationalError: (1213, 'Deadlock found when trying to get lock try restarting transaction') Populate_files(dbpkg, repopkg, force=force)įile "/srv/http/archweb/devel/management/commands/reporead.py", line 283, in populate_filesįile "/usr/lib/python2.7/site-packages/MySQLdb/cursors.py", line 174, in executeįile "/usr/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler Self._target(*self._args, **self._kwargs)įile "/srv/http/archweb/devel/management/commands/reporead.py", line 223, in populate_pkg manage.py reporead_inotify -v0įile "/usr/lib/python2.7/multiprocessing/process.py", line 258, in _bootstrapįile "/usr/lib/python2.7/multiprocessing/process.py", line 114, in run The inotify-based script will thus kick off two simultaneous updates however, these updates should always be non-conflicting and operating on different rows in the various touched tables.Īfter a few trials, I started noticing updates not running to completion, and tracebacks such as the following (slightly trimmed for brevity). However, it now often occurs that the databases for the same repository for both architectures are updated at nearly the same time (within seconds of each other). ![]() Previously this ran on a fixed schedule via cron, so updates to the database were serialized, and things worked as expected. You can get deadlocks even in the case of transactions that just insert or delete a single row.Ī relatively recent update to archweb, the Arch Linux website code, added an inotify-based update script that watches the package repository database files for updates, and then kicks off the script for updating the website database. The InnoDB documentation does have a section dealing with deadlocks and possible ways to avoid them. However, it is pretty bad that a relational database in the year 2012 cannot handle simultaneous non-conflicting inserts on a table when transactions are involved.įirst, some background. (Say, 3 retries on this particular error before giving up).Poor MySQL, always getting picked on. you can add this logic to your client code. WHERE datetime <= now() - INTERVAL 900 SECONDĪnother thing to keep in mind is that MySQL documentation suggest that in case of a deadlock the client should retry automatically. if you do (and I suspect you do), order their WHERE in (k1,k2.kn) in ascending order.įix your delete statement to work in ascending order: Make sure you have no other queries that lock access more than one key at a time except for the delete statement. connection 2: locks key( 1), locks key( 2).Now, if you changed your queries such that the connections would lock the keys at the same order, ie: If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock. connection 2: locks key(2), locks key(1). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |