Error:

PDOException: SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected DETAIL: Process 30270 waits for AccessExclusiveLock on relation 2442958 of database 2434599; blocked by process 30269. Process 30269 waits for AccessExclusiveLock on relation 2442958 of database 2434599; blocked by process 30270. HINT: See server log for query details.: TRUNCATE {cache_entity_node} ; Array ( ) in cache_clear_all() (line 176 of /var/www/drupal/ec-internet/releases/20121212-1/includes/cache.inc).

I've seen this issue popup twice since enabling entitycache module, I have no clue what's causing it as it's seemingly random, but according to the logs it's caused by multiple truncates in short succession by different processes. Somehow this causes the node table to be updated with a non existent vid, which in turn causes all sorts of issues when using entity metadata wrappers etc.

example_db=> select * from node where nid = 631;
 nid | vid  |     type     | language |    title    | uid | status |  created   |  changed   | comment | promote | sticky | tnid | translate 
-----+------+--------------+----------+-------------+-----+--------+------------+------------+---------+---------+--------+------+-----------
 631 | 3767 | landing_page | und      | Past Events |  24 |      1 | 1351899901 | 1355358716 |       0 |       0 |      0 |    0 |         0
(1 row)

example_db=> select * from node_revision where vid = 3767;
 nid | vid | uid | title | log | timestamp | status | comment | promote | sticky 
-----+-----+-----+-------+-----+-----------+--------+---------+---------+--------
(0 rows)

Has anyone else had this problem?

Comments

acbramley’s picture

Priority: Normal » Major
acbramley’s picture

Worked out how to replicate, if you hit the save button over and over on updating a node

acbramley’s picture

I'm on postgres 9.1 btw

acbramley’s picture

Can confirm disabling entitycache and doing the same thing does not produce the error

acbramley’s picture

Have continued testing this and have found more exceptions from the same action:

PDOException: SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected DETAIL: Process 4244 waits for ExclusiveLock on tuple (0,5) of relation 1634099 of database 1633906; blocked by process 4238. Process 4238 waits for ShareLock on transaction 4406036; blocked by process 4237. Process 4237 waits for AccessExclusiveLock on relation 1634099 of database 1633906; blocked by process 4244. HINT: See server log for query details.: DELETE FROM {cache_entity_node} WHERE (cid IN (:db_condition_placeholder_0)) ; Array ( [:db_condition_placeholder_0] => 1424 ) in cache_clear_all() (line 176 of /var/www/drupal/ec-internet/includes/cache.inc).
PDOException: SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected DETAIL: Process 4238 waits for ShareLock on transaction 4406066; blocked by process 4237. Process 4237 waits for AccessExclusiveLock on relation 1634099 of database 1633906; blocked by process 4238. HINT: See server log for query details.: UPDATE node SET type=:db_update_placeholder_0, language=:db_update_placeholder_1, title=:db_update_placeholder_2, uid=:db_update_placeholder_3, status=:db_update_placeholder_4, created=:db_update_placeholder_5, changed=:db_update_placeholder_6, comment=:db_update_placeholder_7, promote=:db_update_placeholder_8, sticky=:db_update_placeholder_9, tnid=:db_update_placeholder_10, translate=:db_update_placeholder_11 WHERE (nid = :db_condition_placeholder_0) ; Array ( [target] => default [return] => 2 [already_prepared] => 1 ) in drupal_write_record() (line 7106 of /var/www/drupal/ec-internet/includes/common.inc).
acbramley’s picture

Priority: Major » Critical

Really wish this issue would get some attention, this is causing us major issues

acbramley’s picture

wiifm’s picture

Status: Closed (duplicate) » Needs work

Nope, I have a core patch applied from #1839998: TruncateQuery implemented as "DELETE FROM" in MySQL and SQLite, but not PostgreSQL, causing nefarious table locking, and the deadlock is still apparent, this time the deadlock is with a "DELETE FROM" SQL command:

2013-03-05 17:26:23 NZDT [drupal-site-mts] ERROR:  deadlock detected
2013-03-05 17:26:23 NZDT [drupal-site-mts] DETAIL:  Process 27040 waits for Shar
eLock on transaction 68931095; blocked by process 27041.
        Process 27041 waits for ShareLock on transaction 68931017; blocked by pr
ocess 27040.
        Process 27040: DELETE FROM cache_entity_node
        Process 27041: DELETE FROM cache_entity_node
deanflory’s picture

I too have been getting these deadlocks after enabling the module.

Dave Reid’s picture

Priority: Critical » Major
Status: Needs work » Active

There is no patch here that needs work nor review. Can we have some detailed steps to reproduce this bug using a fresh install of core + Entity cache + minimum amount of contrib modules? Since this seems to affect PgSQL only, bumping down to major.

catch’s picture

Issue summary: View changes
Status: Active » Closed (cannot reproduce)

You should use memcache or redis if you have a high concurrency site. This module makes pretty much zero sense with the database cache - it's purpose is to offload queries from the database for scalability, you don't get much benefit using it with the database cache.

Also Entity Cache only uses core's cache API - so this is either a problem with the postgres driver for core, or possibly your database configuration (MySQL at least has settings which can increase or decrease the likelihood for gap locks), but it's not a specific bug in entity cache - it's just exposing the bug elsewhere.