PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock - semaphore

Same here, the {semaphore} table is innodb, but on Drupal 7.23 (and the function lock_may_be_available() looks exactly the same in 7.24):

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {semaphore} WHERE (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) AND (expire <= :db_condition_placeholder_2) ; Array ( [:db_condition_placeholder_0] => variable_init [:db_condition_placeholder_1] => 77390603752cc42e32413d9.37784754 [:db_condition_placeholder_2] => 1389118180.1417 ) in lock_may_be_available() (line 181 of /var/www/vhosts/mysite/public/includes/lock.inc).

Changed version from 7.22 to 7.23 since it was seen there as well.

Try updating to the latest version of Drupal 7, there was a fix for the deadlock issue.

Same issue at 7.31 (boost cache enabled):
PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {cache_rules}; Array ( ) in cache_clear_all() (Zeile 165 von /var/www/web/html/mysite/includes/cache.inc).

update from 7.33 to 7.34
PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {semaphore} WHERE (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) AND (expire <= :db_condition_placeholder_2) ; Array ( [:db_condition_placeholder_0] => menu_rebuild [:db_condition_placeholder_1] => 1840166209547282168f3ba1.95102164 [:db_condition_placeholder_2] => 1416790581.11 ) in lock_may_be_available() (line 181 of /home/jicny/public_html/drupal/includes/lock.inc).

I update Drupal core and modules, I have 7.34 version and the error persists. I need a solution please, my client is going to kill me :D

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {semaphore} WHERE (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) AND (expire <= :db_condition_placeholder_2) ; Array ( [:db_condition_placeholder_0] => variable_init [:db_condition_placeholder_1] => 380163740547f23e7d2f2a2.68681472 [:db_condition_placeholder_2] => 1417618408.86 ) in lock_may_be_available() (line 181 of ...includes/lock.inc).

Hi, Same issue here...what could be the problem please?
How to clean the semaphore table?
greetings, Martijn

I tried this by the moment that work for me

ALTER TABLE semaphore ENGINE = MEMORY;
ALTER TABLE semaphore DROP PRIMARY KEY;
ALTER TABLE semaphore ADD PRIMARY KEY (name, value) USING BTREE;
ALTER TABLE semaphore ADD UNIQUE name (name) USING BTREE;
ALTER TABLE semaphore DROP INDEX value;
ALTER TABLE semaphore ADD INDEX value (value) USING BTREE;
ALTER TABLE semaphore DROP INDEX expire;
ALTER TABLE semaphore ADD INDEX expire (expire) USING BTREE;

good luck

I tried this ALTER TABLE lines. It worked fine on my mysql 5.5.39 database
but on another mysql 5.6.19 database I got the following error:

[SQL] ALTER TABLE semaphore ADD PRIMARY KEY (name, value) USING BTREE;
[Err] 1071 - Specified key was too long; max key length is 1000 bytes

Does anybody has an idea about that?
Thank you very much!

Currently I have a Drupal 7.35 and show me the same error

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {semaphore} WHERE (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) AND (expire <= :db_condition_placeholder_2) ; Array ( [:db_condition_placeholder_0] => variable_init [:db_condition_placeholder_1] => 198423353555fbcb6011264.18295914 [:db_condition_placeholder_2] => 1432337591.0042 ) in lock_may_be_available() (line 181 of /path/to/drupal/includes/lock.inc).

Currently I have a Drupal 7.35 and show me the same error

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {semaphore} WHERE (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) AND (expire <= :db_condition_placeholder_2) ; Array ( [:db_condition_placeholder_0] => variable_init [:db_condition_placeholder_1] => 198423353555fbcb6011264.18295914 [:db_condition_placeholder_2] => 1432337591.0042 ) in lock_may_be_available() (line 181 of /path/to/drupal/includes/lock.inc).

I update Drupal core and modules, I have 7.35 version and the error persists. I don't know what do to, you guys save me pls.this error is changing shapes and i found some times the following:

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: DELETE FROM {semaphore} WHERE (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) AND (expire <= :db_condition_placeholder_2) ; Array ( [:db_condition_placeholder_0] => menu_rebuild [:db_condition_placeholder_1] => 3704930655582d3af30bcf1.98491632 [:db_condition_placeholder_2] => 1434637262.6315 ) in lock_may_be_available() (line 181 of C:\Users\user\Documents\mysite\includes\lock.inc).

Are you using Profile2 module on the websites with this deadlock appearing? We've found that the reason of the deadlock is menu_rebuild() called by Profile2 module from inside transaction started by EntityAPI module. Semaphore "menu_rebuild" doesn't get saved to database until transaction is committed, but it creates row-level lock on the table allowing to read-only and there is a time gap for another request to run into menu_rebuild() and create deadlock situation on the semaphore table.

This problem seems to be fixed in Profile2 7.x-1.x-dev, but the latest stable release 7.x-1.3 is still affected.

May be it helps a little.

but i still need some root work solutions here.

Got that error yesterday with Drupal 7.37 running on Apache 2.4.10, PHP 5.6.4 and MySQL 5.6.24.

I was editing some content on the site exactly on the time when cron launched. (I could see that the time was about 5 seconds after the cron started)
Here's what I got on the web:

Uncaught exception thrown in shutdown function.
PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {semaphore} WHERE (value = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => 1891152368558823025786a6.46510670 ) in lock_release_all() (line 269 of /var/www/MYPROJECT/includes/lock.inc).

I'm afraid to let the user use it because this could happen every time a cron is launched.
Is there a known solution to it?

There was a change here: #800432: No index on {semaphore}.value That added an index for value. Do you have that schema change?

FYI, the underlying issue for the OP and many others in this queue may be a precision/rounding error -- based on the fact that the error messages are showing times rounded to two significant digits (e.g., [:db_condition_placeholder_2] => 1372752964.32) instead of four (e.g., 1372752964.3200. This problem is being discussed at #2077827: Lock system is broken in many regards (was: needs a precision of at least 14 for timestamp comparison).

I have a same problem on drupal 7.43 nginx and mariadb 10.1

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {semaphore} WHERE (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) ; Array ( [:db_condition_placeholder_0] => field_info:bundle:node:headphone_headset [:db_condition_placeholder_1] => 1314223567570237160c5b05.08794376 ) in lock_release() (line 254 of /var/www/bestnik/includes/lock.inc). Backtrace:

PDOStatement->execute(Array) database.inc:2171
DatabaseStatementBase->execute(Array, Array) database.inc:683
DatabaseConnection->query('DELETE FROM {semaphore} 
WHERE  (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) ', Array, Array) query.inc:858
DeleteQuery->execute() lock.inc:254
lock_release('field_info:bundle:node:headphone_headset') field.info.class.inc:434
FieldInfo->getBundleInstances('node', 'headphone_headset') field.info.inc:606
field_info_instances('node', 'headphone_headset') field.multilingual.inc:277
field_language('node', Object, 'field_media_image', 'und') field.module:873
field_view_field('node', Object, 'field_media_image', Array, 'und') views_handler_field_field.inc:852
views_handler_field_field->set_items(Object, 0) views_handler_field_field.inc:683
views_handler_field_field->post_execute(Array) view.inc:744
view->_post_execute() view.inc:1164
view->execute(NULL) view.inc:1192
view->render() views_plugin_display_block.inc:54
views_plugin_display_block->execute() view.inc:1345
view->execute_display('block_3') views.module:738
views_block_view('product_tab-block_3') 
call_user_func_array('views_block_view', Array) module.inc:922
module_invoke('views', 'block_view', 'product_tab-block_3') block.module:915
_block_render_blocks(Array) block.module:694
block_list('tab6') block.module:320
block_get_blocks_by_region('tab6') block.module:270
block_page_build(Array) common.inc:5867
drupal_render_page('

I have the same issue with drupal 7.43, during updating a view with menu
restarting mySql - didn't help .
cleaning 'semaphore' table - didn't help
changing the innodb_lock_wait_timeout=5000 - result with a white page

any new ideas?

As per @Nephele in comment #17, the patch here patch for d7 lock issue solved the problem for me.

And I think this is a duplicate.

@hkovacs @Nephele
Not sure if this is really a duplicate and the root for this is the precision php.ini setting like in the mentioned issue: https://www.drupal.org/node/2077827

Because I have set precision to 14 in php.ini and have the same problem with the semaphore table. Especially for users with large sites (many sessions) and errors on {semaphore} table it maybe a InnoDB issue, see https://groups.drupal.org/node/415883 and https://www.drupal.org/node/1898204

As we already use Redis for caching I will now try to move the semaphore table to Redis, found hint at Pantheon Redis (Troubleshooting section):

// Use Redis for Drupal locks (semaphore).
$conf['lock_inc'] = 'sites/all/modules/redis/redis.lock.inc';

I checked my php.ini file and my "precision" setting is already "14" but the issues persist.
Moving semaphore to memory or basically away from db seems to be the solution.

Can also confirm, after moving semaphore to Redis yesterday we have 0 serialization errors in log. We had a few hundred of them each day before.

Edit: So this seems not to be a duplicate of the precision setting in php.ini, therefore setting it back to active.

Same. Pantheon live environment.

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM {semaphore} WHERE (name = :db_condition_placeholder_0) AND (value = :db_condition_placeholder_1) ; Array ( [:db_condition_placeholder_0] => field_info:bundle:commerce_discount_offer:per_quantity_fixed [:db_condition_placeholder_1] => 354540880618efa63958b93.40900433 ) in lock_release() (line 254 of /code/includes/lock.inc).

I didn't change this. 🙄