我最近实现了一个功能,其中创建了多个后台作业,这些作业遍历数百个对象并创建与其相关的其他对象.创建每个对象时,触摸相同的用户.
因此,这两个工作并行运行：
>作业A：每次触摸用户123时,连续创建数百个对象
>作业B：每次触摸用户123时,连续创建数百个对象
在部署此功能后,我发现有时postgres会检测到死锁并取消两个正在等待的查询.错误中显示的查询始终是触摸查询(使用当前时间更新用户123的updated_at).
pID: 33541 tID: 0 vtID: LOG: database system was shut down at 2014-03-27 19:57:11 EDTpID: 33545 tID: 0 vtID: LOG: autovacuum launcher startedpID: 33539 tID: 0 vtID: LOG: database system is ready to accept connectionspID: 33626 tID: 0 vtID: 2/5 LOG: statement: set clIEnt_enCoding to 'UTF8'pID: 33626 tID: 0 vtID: 2/6 LOG: statement: set clIEnt_enCoding to 'unicode'pID: 33626 tID: 0 vtID: 2/7 LOG: statement: SET clIEnt_min_messages TO 'warning'pID: 33626 tID: 0 vtID: 2/8 LOG: statement: SHOW clIEnt_min_messagespID: 33626 tID: 0 vtID: 2/9 LOG: statement: SET clIEnt_min_messages TO 'panic'pID: 33626 tID: 0 vtID: 2/10 LOG: statement: SET standard_conforming_strings = onpID: 33626 tID: 0 vtID: 2/11 LOG: statement: SET clIEnt_min_messages TO 'warning'pID: 33626 tID: 0 vtID: 2/12 LOG: statement: SET time zone 'UTC'pID: 33626 tID: 0 vtID: 2/13 LOG: statement: SELECT oID,typname,typelem,typdelim,typinput FROM pg_typepID: 33626 tID: 0 vtID: 2/14 LOG: statement: SHOW TIME ZONEpID: 33626 tID: 0 vtID: 2/15 LOG: statement: SELECT 1pID: 33626 tID: 0 vtID: 2/16 LOG: statement: SELECT COUNT(*) FROM pg_class c left JOIN pg_namespace n ON n.oID = c.relnamespace WHERE c.relkind in ('v','r') AND c.relname = 'users' AND n.nspname = ANY (current_schemas(false))pID: 33626 tID: 0 vtID: 2/17 LOG: statement: SELECT attr.attname FROM pg_attribute attr INNER JOIN pg_constraint cons ON attr.attrelID = cons.conrelID AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND cons.conrelID = '"users"'::regclasspID: 33626 tID: 0 vtID: 2/18 LOG: statement: SELECT "users".* FROM "users" ORDER BY "users"."ID" ASC liMIT 1pID: 33626 tID: 0 vtID: 2/19 LOG: statement: SELECT a.attname,format_type(a.atttypID,a.atttypmod),pg_get_expr(d.adbin,d.adrelID),a.attnotnull,a.atttypID,a.atttypmod FROM pg_attribute a left JOIN pg_attrdef d ON a.attrelID = d.adrelID AND a.attnum = d.adnum WHERE a.attrelID = '"users"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnumpID: 33627 tID: 0 vtID: 3/2 LOG: statement: set clIEnt_enCoding to 'UTF8'pID: 33627 tID: 0 vtID: 3/3 LOG: statement: set clIEnt_enCoding to 'unicode'pID: 33627 tID: 0 vtID: 3/4 LOG: statement: SET clIEnt_min_messages TO 'warning'pID: 33627 tID: 0 vtID: 3/5 LOG: statement: SHOW clIEnt_min_messagespID: 33627 tID: 0 vtID: 3/6 LOG: statement: SET clIEnt_min_messages TO 'panic'pID: 33627 tID: 0 vtID: 3/7 LOG: statement: SET standard_conforming_strings = onpID: 33627 tID: 0 vtID: 3/8 LOG: statement: SET clIEnt_min_messages TO 'warning'pID: 33627 tID: 0 vtID: 3/9 LOG: statement: SET time zone 'UTC'pID: 33627 tID: 0 vtID: 3/10 LOG: statement: SELECT oID,typinput FROM pg_typepID: 33627 tID: 0 vtID: 3/11 LOG: statement: SHOW TIME ZONEpID: 33627 tID: 0 vtID: 3/12 LOG: statement: SELECT 1pID: 33627 tID: 0 vtID: 3/13 LOG: statement: SELECT a.attname,a.atttypmod FROM pg_attribute a left JOIN pg_attrdef d ON a.attrelID = d.adrelID AND a.attnum = d.adnum WHERE a.attrelID = '"users"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnumpID: 33627 tID: 0 vtID: 3/14 LOG: statement: SELECT COUNT(*) FROM pg_class c left JOIN pg_namespace n ON n.oID = c.relnamespace WHERE c.relkind in ('v','r') AND c.relname = 'users' AND n.nspname = ANY (current_schemas(false))pID: 33627 tID: 0 vtID: 3/15 LOG: statement: SELECT attr.attname FROM pg_attribute attr INNER JOIN pg_constraint cons ON attr.attrelID = cons.conrelID AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND cons.conrelID = '"users"'::regclasspID: 33627 tID: 0 vtID: 3/16 LOG: statement: SELECT ID FROM "users" WHERE (bared_at IS NOT NulL) ORDER BY "users"."ID" ASC liMIT 1000pID: 33628 tID: 0 vtID: 4/2 LOG: statement: set clIEnt_enCoding to 'UTF8'pID: 33628 tID: 0 vtID: 4/3 LOG: statement: set clIEnt_enCoding to 'unicode'pID: 33628 tID: 0 vtID: 4/4 LOG: statement: SET clIEnt_min_messages TO 'warning'pID: 33628 tID: 0 vtID: 4/5 LOG: statement: SHOW clIEnt_min_messagespID: 33628 tID: 0 vtID: 4/6 LOG: statement: SET clIEnt_min_messages TO 'panic'pID: 33628 tID: 0 vtID: 4/7 LOG: statement: SET standard_conforming_strings = onpID: 33628 tID: 0 vtID: 4/8 LOG: statement: SET clIEnt_min_messages TO 'warning'pID: 33628 tID: 0 vtID: 4/9 LOG: statement: SET time zone 'UTC'pID: 33628 tID: 0 vtID: 4/10 LOG: statement: SELECT oID,typinput FROM pg_typepID: 33628 tID: 0 vtID: 4/11 LOG: statement: SHOW TIME ZONEpID: 33628 tID: 0 vtID: 4/12 LOG: statement: SELECT 1pID: 33628 tID: 0 vtID: 4/13 LOG: statement: SELECT ID FROM "users" WHERE (bared_at IS NOT NulL) ORDER BY "users"."ID" ASC liMIT 1000pID: 33627 tID: 0 vtID: 3/17 LOG: statement: SELECT COUNT(*) FROM pg_class c left JOIN pg_namespace n ON n.oID = c.relnamespace WHERE c.relkind in ('v','r') AND c.relname = 'monkeys' AND n.nspname = ANY (current_schemas(false))pID: 33628 tID: 0 vtID: 4/14 LOG: statement: SELECT COUNT(*) FROM pg_class c left JOIN pg_namespace n ON n.oID = c.relnamespace WHERE c.relkind in ('v','r') AND c.relname = 'monkeys' AND n.nspname = ANY (current_schemas(false))pID: 33627 tID: 0 vtID: 3/18 LOG: statement: SELECT attr.attname FROM pg_attribute attr INNER JOIN pg_constraint cons ON attr.attrelID = cons.conrelID AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND cons.conrelID = '"monkeys"'::regclasspID: 33628 tID: 0 vtID: 4/15 LOG: statement: SELECT attr.attname FROM pg_attribute attr INNER JOIN pg_constraint cons ON attr.attrelID = cons.conrelID AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND cons.conrelID = '"monkeys"'::regclasspID: 33627 tID: 0 vtID: 3/19 LOG: statement: SELECT a.attname,a.atttypmod FROM pg_attribute a left JOIN pg_attrdef d ON a.attrelID = d.adrelID AND a.attnum = d.adnum WHERE a.attrelID = '"users_monkeys"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnumpID: 33628 tID: 0 vtID: 4/16 LOG: statement: SELECT a.attname,a.atttypmod FROM pg_attribute a left JOIN pg_attrdef d ON a.attrelID = d.adrelID AND a.attnum = d.adnum WHERE a.attrelID = '"users_monkeys"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnumpID: 33627 tID: 0 vtID: 3/20 LOG: statement: SELECT a.attname,a.atttypmod FROM pg_attribute a left JOIN pg_attrdef d ON a.attrelID = d.adrelID AND a.attnum = d.adnum WHERE a.attrelID = '"monkeys"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnumpID: 33628 tID: 0 vtID: 4/17 LOG: statement: SELECT a.attname,a.atttypmod FROM pg_attribute a left JOIN pg_attrdef d ON a.attrelID = d.adrelID AND a.attnum = d.adnum WHERE a.attrelID = '"monkeys"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnumpID: 33627 tID: 0 vtID: 3/21 LOG: statement: SHOW search_pathpID: 33628 tID: 0 vtID: 4/18 LOG: statement: SHOW search_pathpID: 33627 tID: 0 vtID: 3/23 LOG: execute a1: SELECT COUNT(*) FROM "monkeys" INNER JOIN "users_monkeys" ON "monkeys"."ID" = "users_monkeys"."monkey_ID" WHERE "users_monkeys"."user_ID" = AND (((monkeys.name = 'recruiter') AND (monkeys.resource_type IS NulL) AND (monkeys.resource_ID IS NulL)))pID: 33627 tID: 0 vtID: 3/23 DETAIL: parameters: = '2'pID: 33628 tID: 0 vtID: 4/20 LOG: execute a1: SELECT COUNT(*) FROM "monkeys" INNER JOIN "users_monkeys" ON "monkeys"."ID" = "users_monkeys"."monkey_ID" WHERE "users_monkeys"."user_ID" = AND (((monkeys.name = 'recruiter') AND (monkeys.resource_type IS NulL) AND (monkeys.resource_ID IS NulL)))pID: 33628 tID: 0 vtID: 4/20 DETAIL: parameters: = '2'pID: 33627 tID: 0 vtID: 3/24 LOG: statement: SELECT "foos".* FROM "foos" WHERE "foos"."user_ID" = 2 AND "foos"."fooer_ID" = 1 liMIT 1pID: 33628 tID: 0 vtID: 4/21 LOG: statement: SELECT "foos".* FROM "foos" WHERE "foos"."user_ID" = 1 AND "foos"."fooer_ID" = 2 liMIT 1pID: 33628 tID: 0 vtID: 4/22 LOG: statement: SELECT a.attname,a.atttypmod FROM pg_attribute a left JOIN pg_attrdef d ON a.attrelID = d.adrelID AND a.attnum = d.adnum WHERE a.attrelID = '"foos"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnumpID: 33627 tID: 0 vtID: 3/25 LOG: statement: SELECT a.attname,a.atttypmod FROM pg_attribute a left JOIN pg_attrdef d ON a.attrelID = d.adrelID AND a.attnum = d.adnum WHERE a.attrelID = '"foos"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnumpID: 33628 tID: 0 vtID: 4/23 LOG: statement: SELECT COUNT(*) FROM pg_class c left JOIN pg_namespace n ON n.oID = c.relnamespace WHERE c.relkind in ('v','r') AND c.relname = 'foos' AND n.nspname = ANY (current_schemas(false))pID: 33627 tID: 0 vtID: 3/26 LOG: statement: SELECT COUNT(*) FROM pg_class c left JOIN pg_namespace n ON n.oID = c.relnamespace WHERE c.relkind in ('v','r') AND c.relname = 'foos' AND n.nspname = ANY (current_schemas(false))pID: 33628 tID: 0 vtID: 4/24 LOG: statement: SELECT attr.attname FROM pg_attribute attr INNER JOIN pg_constraint cons ON attr.attrelID = cons.conrelID AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND cons.conrelID = '"foos"'::regclasspID: 33627 tID: 0 vtID: 3/27 LOG: statement: SELECT attr.attname FROM pg_attribute attr INNER JOIN pg_constraint cons ON attr.attrelID = cons.conrelID AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND cons.conrelID = '"foos"'::regclasspID: 33628 tID: 0 vtID: 4/25 LOG: statement: BEGINpID: 33627 tID: 0 vtID: 3/28 LOG: statement: BEGINpID: 33628 tID: 0 vtID: 4/25 LOG: statement: SELECT 1 AS one FROM "foos" WHERE ("foos"."user_ID" = 1 AND "foos"."ID" != 4 AND "foos"."fooer_ID" = 2) liMIT 1pID: 33627 tID: 0 vtID: 3/28 LOG: statement: SELECT 1 AS one FROM "foos" WHERE ("foos"."user_ID" = 2 AND "foos"."ID" != 3 AND "foos"."fooer_ID" = 1) liMIT 1pID: 33628 tID: 0 vtID: 4/25 LOG: statement: SELECT a.attname,a.atttypmod FROM pg_attribute a left JOIN pg_attrdef d ON a.attrelID = d.adrelID AND a.attnum = d.adnum WHERE a.attrelID = '"users"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnumpID: 33627 tID: 0 vtID: 3/28 LOG: execute a2: SELECT "users".* FROM "users" WHERE "users"."ID" = liMIT 1pID: 33627 tID: 0 vtID: 3/28 DETAIL: parameters: = '2'pID: 33627 tID: 0 vtID: 3/28 LOG: statement: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.619282' WHERE "users"."ID" = 2pID: 33628 tID: 0 vtID: 4/25 LOG: execute a2: SELECT "users".* FROM "users" WHERE "users"."ID" = liMIT 1pID: 33628 tID: 0 vtID: 4/25 DETAIL: parameters: = '1'pID: 33627 tID: 6723 vtID: 3/28 LOG: execute a2: SELECT "users".* FROM "users" WHERE "users"."ID" = liMIT 1pID: 33627 tID: 6723 vtID: 3/28 DETAIL: parameters: = '1'pID: 33628 tID: 0 vtID: 4/25 LOG: statement: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.627175' WHERE "users"."ID" = 1pID: 33627 tID: 6723 vtID: 3/28 LOG: statement: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.628983' WHERE "users"."ID" = 1pID: 33628 tID: 6724 vtID: 4/25 LOG: execute a2: SELECT "users".* FROM "users" WHERE "users"."ID" = liMIT 1pID: 33628 tID: 6724 vtID: 4/25 DETAIL: parameters: = '2'pID: 33628 tID: 6724 vtID: 4/25 LOG: statement: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.632111' WHERE "users"."ID" = 2pID: 33627 tID: 6723 vtID: 3/28 ERROR: deadlock detectedpID: 33627 tID: 6723 vtID: 3/28 DETAIL: Process 33627 waits for ShareLock on transaction 6724; blocked by process 33628. Process 33628 waits for ShareLock on transaction 6723; blocked by process 33627. Process 33627: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.628983' WHERE "users"."ID" = 1 Process 33628: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.632111' WHERE "users"."ID" = 2 我很惊讶发生了这种死锁,因为行和表都没有被锁定.不应该第二次交易只是等待第一次完成？
更新：这里最小的sql-only BUG：https://gist.github.com/jjb/9823023
解决方法 您的原始日志不完整,因为第一个UPDATE没有事务ID,因此它们不包含在原始日志中.既然存在具有虚拟txIDs的日志,您可以看到Postgresql的行为完全符合设计.
相关的路线是：
pID: 33627 tID: 0 vtID: 3/28 LOG: statement: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.619282' WHERE "users"."ID" = 2pID: 33628 tID: 0 vtID: 4/25 LOG: statement: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.627175' WHERE "users"."ID" = 1pID: 33627 tID: 6723 vtID: 3/28 LOG: statement: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.628983' WHERE "users"."ID" = 1pID: 33628 tID: 6724 vtID: 4/25 LOG: statement: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.632111' WHERE "users"."ID" = 2pID: 33627 tID: 6723 vtID: 3/28 ERROR: deadlock detectedpID: 33627 tID: 6723 vtID: 3/28 DETAIL: Process 33627 waits for ShareLock on transaction 6724; blocked by process 33628. Process 33628 waits for ShareLock on transaction 6723; blocked by process 33627. Process 33627: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.628983' WHERE "users"."ID" = 1 Process 33628: UPDATE "users" SET "updated_at" = '2014-03-27 23:58:02.632111' WHERE "users"."ID" = 2
这里：
> 3/28锁定ID = 2进行更新
> 4/25锁定ID = 1进行更新
> 3/28尝试锁定ID = 1进行更新,阻止4/25持有的锁定
> 4/25尝试锁定ID = 2进行更新,阻塞3/28所持有的锁定
在这一点上,两个事务都无法取得进展,因此Postgresql会中止其中一个事务.
为了防止这种情况发生,您的应用程序必须确保它始终以相同的顺序获取锁.如果不可能,它必须尝试在任何给定的事务中仅使用一个依赖对象,因此不会出现排序问题.或者它必须准备通过捕获异常并重新发出事务来处理死锁.
要了解更多信息,请参阅手册中的Explicit locking.
总结以上是内存溢出为你收集整理的ruby-on-rails – 当没有设置行或表锁时,如何发生死锁？ (由rails ActiveRecord#touch引起)全部内容，希望文章能够帮你解决ruby-on-rails – 当没有设置行或表锁时,如何发生死锁？ (由rails ActiveRecord#touch引起)所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）