问题：

Postgres：乐观锁定的奇怪行为

靳举

2023-03-14

我正在调试使用JPA / Hibernate和Postgres（9.6.2）的Java应用程序的奇怪行为。

应用程序有 3 个实体：用户、国家/地区、用户事件。

Hibernate将其映射到4个表：users、countries、userevent、Hibernate_sequences。

用户实体具有版本列（@Version）和用于乐观锁定的主题。@GeneratedValue（strategy = GenerationType.TABLE）为其主键“id”。用户表将接收最多的读取和写入次数（最受欢迎的表）。

我已经创建了几个压力测试来模拟高负载，并在运行它们时注意到 Postgres 日志中的以下错误（请注意导致它的更新查询）：

ERROR:  deadlock detected
DETAIL:  Process 46758 waits for ShareLock on transaction 1580660; blocked by process 46759.
  Process 46759 waits for AccessExclusiveLock on tuple (97,26) of relation 17353 of database 16385; blocked by process 46758.

  Process 46758: update users set confirmationState=$1, country_iso=$2, dateOfBirth=$3, email=$4, firstName=$5, gender=$6, lastName=$7, lastUsedLocale=$8, middleName=$9, passwordHash=$10, passwordSalt=$11, phone=$12, role=$13, signInState=$14, updated=$15, username=$16, version=$17 where id=$18 and version=$19
  Process 46759: update users set confirmationState=$1, country_iso=$2, dateOfBirth=$3, email=$4, firstName=$5, gender=$6, lastName=$7, lastUsedLocale=$8, middleName=$9, passwordHash=$10, passwordSalt=$11, phone=$12, role=$13, signInState=$14, updated=$15, username=$16, version=$17 where id=$18 and version=$19

  HINT:  See server log for query details.
  CONTEXT:  while updating tuple (97,26) in relation "users"

我知道 Postgres 有共享和独家;行、表或索引级锁。以错误的顺序在相同的对象上获取它们可能会导致死锁。

但是对于这种特殊情况，为什么我得到一个带有乐观锁定的死锁？在这种情况下，我希望一个事务成功，一个事务返回0个UPDATE。

表格、FK及其索引如下所述：

##################################################################################################

                                        Table "public.users"
      Column       |            Type             | Modifiers | Storage  | Stats target | Description 
-------------------+-----------------------------+-----------+----------+--------------+-------------
 id                | bigint                      | not null  | plain    |              | 
 confirmationstate | character varying(20)       | not null  | extended |              | 
 created           | timestamp without time zone | not null  | plain    |              | 
 dateofbirth       | date                        | not null  | plain    |              | 
 email             | character varying(255)      | not null  | extended |              | 
 firstname         | character varying(100)      | not null  | extended |              | 
 gender            | character varying(10)       | not null  | extended |              | 
 lastname          | character varying(100)      | not null  | extended |              | 
 lastusedlocale    | character varying(5)        |           | extended |              | 
 middlename        | character varying(100)      |           | extended |              | 
 passwordhash      | bytea                       | not null  | extended |              | 
 passwordsalt      | bytea                       | not null  | extended |              | 
 phone             | character varying(50)       |           | extended |              | 
 role              | character varying(50)       | not null  | extended |              | 
 signinstate       | character varying(20)       | not null  | extended |              | 
 updated           | timestamp without time zone | not null  | plain    |              | 
 username          | character varying(255)      |           | extended |              | 
 version           | bigint                      |           | plain    |              | 
 country_iso       | character varying(2)        |           | extended |              | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "email_idx" UNIQUE CONSTRAINT, btree (email)
    "username_idx" UNIQUE CONSTRAINT, btree (username)
    "firstname_idx" btree (firstname)
    "lastname_idx" btree (lastname)
    "phone_idx" btree (phone)
Foreign-key constraints:
    "fk_ghgfskd1hgobolp0fh20i6h14" FOREIGN KEY (country_iso) REFERENCES countries(iso)
Referenced by:
    TABLE "userevents" CONSTRAINT "fk_69tm2wh9hkakap0uyjw41c61h" FOREIGN KEY (targetuser_id) REFERENCES users(id)
    TABLE "userevents" CONSTRAINT "fk_nov1tso5k93h4m88unjy4f2kl" FOREIGN KEY (user_id) REFERENCES users(id)

##################################################################################################

                                Table "public.countries"
   Column   |          Type          | Modifiers | Storage  | Stats target | Description 
------------+------------------------+-----------+----------+--------------+-------------
 iso        | character varying(2)   | not null  | extended |              | 
 iso3       | character varying(3)   |           | extended |              | 
 name       | character varying(80)  |           | extended |              | 
 nativename | character varying(150) |           | extended |              | 
 numcode    | integer                |           | plain    |              | 
 phonecode  | integer                | not null  | plain    |              | 
Indexes:
    "countries_pkey" PRIMARY KEY, btree (iso)
    "iso3_idx" UNIQUE CONSTRAINT, btree (iso3)
    "nativename_idx" btree (nativename)
Referenced by:
    TABLE "users" CONSTRAINT "fk_ghgfskd1hgobolp0fh20i6h14" FOREIGN KEY (country_iso) REFERENCES countries(iso)    

##################################################################################################

                                    Table "public.userevents"
    Column     |            Type             | Modifiers | Storage  | Stats target | Description 
---------------+-----------------------------+-----------+----------+--------------+-------------
 id            | bigint                      | not null  | plain    |              | 
 data          | bytea                       |           | extended |              | 
 ip            | character varying(255)      | not null  | extended |              | 
 time          | timestamp without time zone | not null  | plain    |              | 
 type          | character varying(15)       | not null  | extended |              | 
 url           | character varying(2000)     |           | extended |              | 
 targetuser_id | bigint                      |           | plain    |              | 
 user_id       | bigint                      | not null  | plain    |              | 
Indexes:
    "userevents_pkey" PRIMARY KEY, btree (id)
    "usertypetime_idx" btree (user_id, type, "time")
Foreign-key constraints:
    "fk_69tm2wh9hkakap0uyjw41c61h" FOREIGN KEY (targetuser_id) REFERENCES users(id)
    "fk_nov1tso5k93h4m88unjy4f2kl" FOREIGN KEY (user_id) REFERENCES users(id)

##################################################################################################    

Table "public.hibernate_sequences"
         Column         |          Type          | Modifiers | Storage  | Stats target | Description 
------------------------+------------------------+-----------+----------+--------------+-------------
 sequence_name          | character varying(255) |           | extended |              | 
 sequence_next_hi_value | integer                |           | plain    |              | 

##################################################################################################

编辑1:

pozs先生建议使用循环FK查看现有问题，并提供有关事务中发生的事情的更多详细信息。我在这个模式中没有循环FK，所以我在Postgres中设置了日志记录并尝试隔离问题。

下面是2个进程中导致死锁的所有语句的完整日志。

进程1日志：

2017-04-11 12:41:31 PDT      73842 LOG:  00000: execute S_2: BEGIN    
2017-04-11 12:41:31 PDT      73842 LOG:  00000: execute S_3: select user0_.id as id1_2_0_, user0_.confirmationState as confirma2_2_0_, user0_.country_iso as country19_2_0_, user0_.created as created3_2_0_, user0_.dateOfBirth as dateOfBi4_2_0_, user0_.email as email5_2_0_, user0_.firstName as firstNam6_2_0_, user0_.gender as gender7_2_0_, user0_.lastName as lastName8_2_0_, user0_.lastUsedLocale as lastUsed9_2_0_, user0_.middleName as middleN10_2_0_, user0_.passwordHash as passwor11_2_0_, user0_.passwordSalt as passwor12_2_0_, user0_.phone as phone13_2_0_, user0_.role as role14_2_0_, user0_.signInState as signInS15_2_0_, user0_.updated as updated16_2_0_, user0_.username as usernam17_2_0_, user0_.version as version18_2_0_, country1_.iso as iso1_0_1_, country1_.iso3 as iso2_0_1_, country1_.name as name3_0_1_, country1_.nativeName as nativeNa4_0_1_, country1_.numCode as numCode5_0_1_, country1_.phoneCode as phoneCod6_0_1_ from users user0_ left outer join countries country1_ on user0_.country_iso=country1_.iso where user0_.id=$1
2017-04-11 12:41:31 PDT      73842 DETAIL:  parameters: $1 = '10'
2017-04-11 12:41:31 PDT      73842 LOG:  00000: execute S_10: select country0_.iso as iso1_0_, country0_.iso3 as iso2_0_, country0_.name as name3_0_, country0_.nativeName as nativeNa4_0_, country0_.numCode as numCode5_0_, country0_.phoneCode as phoneCod6_0_ from countries country0_ order by country0_.nativeName, country0_.name
2017-04-11 12:41:31 PDT      73842 LOG:  00000: execute S_4: insert into userEvents (data, ip, targetUser_id, time, type, url, user_id, id) values ($1, $2, $3, $4, $5, $6, $7, $8)
2017-04-11 12:41:31 PDT      73842 DETAIL:  parameters: $1 = '\x04545...LONG_BINARY_DATA...d', $2 = '192.168.1.2', $3 = '10', $4 = '2017-04-11 19:41:31.268', $5 = 'UPDATE', $6 = 'http://example.org', $7 = '10', $8 = '201243'
2017-04-11 12:41:31 PDT      73842 LOG:  00000: execute S_7: update users set confirmationState=$1, country_iso=$2, dateOfBirth=$3, email=$4, firstName=$5, gender=$6, lastName=$7, lastUsedLocale=$8, middleName=$9, passwordHash=$10, passwordSalt=$11, phone=$12, role=$13, signInState=$14, updated=$15, username=$16, version=$17 where id=$18 and version=$19
2017-04-11 12:41:31 PDT      73842 DETAIL:  parameters: $1 = 'CONFIRMED', $2 = 'IS', $3 = '1984-10-10', $4 = 'email-527785869@example.org', $5 = 's7w7j5cdm1bg', $6 = 'FEMALE', $7 = 'Terrier9', $8 = 'en', $9 = 'Russel', $10 = '\x64..6439', $11 = '\x33...3836', $12 = '+16509876945', $13 = 'USER', $14 = 'ENABLED', $15 = '2017-04-11 19:41:31.268', $16 = 'demouser9', $17 = '547', $18 = '10', $19 = '546'
2017-04-11 12:41:32 PDT      73842 LOG:  00000: process 73842 detected deadlock while waiting for ExclusiveLock on tuple (206,43) of relation 17992 of database 16385 after 1000.114 ms
2017-04-11 12:41:32 PDT      73842 DETAIL:  Process holding the lock: 73817. Wait queue: .
2017-04-11 12:41:32 PDT      73842 LOCATION:  ProcSleep, proc.c:1415
2017-04-11 12:41:32 PDT      73842 STATEMENT:  update users set confirmationState=$1, country_iso=$2, dateOfBirth=$3, email=$4, firstName=$5, gender=$6, lastName=$7, lastUsedLocale=$8, middleName=$9, passwordHash=$10, passwordSalt=$11, phone=$12, role=$13, signInState=$14, updated=$15, username=$16, version=$17 where id=$18 and version=$19
2017-04-11 12:41:32 PDT      73842 ERROR:  40P01: deadlock detected
2017-04-11 12:41:32 PDT      73842 DETAIL:  Process 73842 waits for ExclusiveLock on tuple (206,43) of relation 17992 of database 16385; blocked by process 73817.
        Process 73817 waits for ShareLock on transaction 2716972; blocked by process 73842.
        Process 73842: update users set confirmationState=$1, country_iso=$2, dateOfBirth=$3, email=$4, firstName=$5, gender=$6, lastName=$7, lastUsedLocale=$8, middleName=$9, passwordHash=$10, passwordSalt=$11, phone=$12, role=$13, signInState=$14, updated=$15, username=$16, version=$17 where id=$18 and version=$19
2017-04-11 12:41:32 PDT      73842 HINT:  See server log for query details.
2017-04-11 12:41:32 PDT      73842 LOCATION:  DeadLockReport, deadlock.c:1140
2017-04-11 12:41:32 PDT      73842 STATEMENT:  update users set confirmationState=$1, country_iso=$2, dateOfBirth=$3, email=$4, firstName=$5, gender=$6, lastName=$7, lastUsedLocale=$8, middleName=$9, passwordHash=$10, passwordSalt=$11, phone=$12, role=$13, signInState=$14, updated=$15, username=$16, version=$17 where id=$18 and version=$19
2017-04-11 12:41:32 PDT      73842 ERROR:  25P02: current transaction is aborted, commands ignored until end of transaction block
2017-04-11 12:41:32 PDT      73842 LOCATION:  exec_parse_message, postgres.c:1303
2017-04-11 12:41:32 PDT      73842 STATEMENT:  SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   WHEN 'S' THEN 'TEMPORARY SEQUENCE'   WHEN 'v' THEN 'TEMPORARY VIEW'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  WHEN 'f' THEN 'FOREIGN TABLE'  WHEN 'm' THEN 'MATERIALIZED VIEW'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname LIKE 'PROBABLYNOT' AND (false  OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME 
2017-04-11 12:41:32 PDT      73842 LOG:  00000: execute S_11: ROLLBACK

进程2日志：

2017-04-11 12:41:31 PDT      73817 LOG:  00000: execute S_2: BEGIN
2017-04-11 12:41:31 PDT      73817 LOG:  00000: execute S_3: select user0_.id as id1_2_0_, user0_.confirmationState as confirma2_2_0_, user0_.country_iso as country19_2_0_, user0_.created as created3_2_0_, user0_.dateOfBirth as dateOfBi4_2_0_, user0_.email as email5_2_0_, user0_.firstName as firstNam6_2_0_, user0_.gender as gender7_2_0_, user0_.lastName as lastName8_2_0_, user0_.lastUsedLocale as lastUsed9_2_0_, user0_.middleName as middleN10_2_0_, user0_.passwordHash as passwor11_2_0_, user0_.passwordSalt as passwor12_2_0_, user0_.phone as phone13_2_0_, user0_.role as role14_2_0_, user0_.signInState as signInS15_2_0_, user0_.updated as updated16_2_0_, user0_.username as usernam17_2_0_, user0_.version as version18_2_0_, country1_.iso as iso1_0_1_, country1_.iso3 as iso2_0_1_, country1_.name as name3_0_1_, country1_.nativeName as nativeNa4_0_1_, country1_.numCode as numCode5_0_1_, country1_.phoneCode as phoneCod6_0_1_ from users user0_ left outer join countries country1_ on user0_.country_iso=country1_.iso where user0_.id=$1
2017-04-11 12:41:31 PDT      73817 DETAIL:  parameters: $1 = '10'
2017-04-11 12:41:31 PDT      73817 LOG:  00000: execute S_6: select country0_.iso as iso1_0_0_, country0_.iso3 as iso2_0_0_, country0_.name as name3_0_0_, country0_.nativeName as nativeNa4_0_0_, country0_.numCode as numCode5_0_0_, country0_.phoneCode as phoneCod6_0_0_ from countries country0_ where country0_.iso=$1
2017-04-11 12:41:31 PDT      73817 DETAIL:  parameters: $1 = 'JP'
2017-04-11 12:41:31 PDT      73817      
2017-04-11 12:41:31 PDT      73817 LOG:  00000: execute S_5: update users set confirmationState=$1, country_iso=$2, dateOfBirth=$3, email=$4, firstName=$5, gender=$6, lastName=$7, lastUsedLocale=$8, middleName=$9, passwordHash=$10, passwordSalt=$11, phone=$12, role=$13, signInState=$14, updated=$15, username=$16, version=$17 where id=$18 and version=$19
2017-04-11 12:41:31 PDT      73817 DETAIL:  parameters: $1 = 'CONFIRMED', $2 = 'JP', $3 = '1984-10-10', $4 = 'email465994020@example.org', $5 = '-1kcg4facio48g', $6 = 'FEMALE', $7 = 'Terrier9', $8 = 'en', $9 = 'Russel', $10 = '\x64656d6f7573657270617373776f726439', $11 = '\x33...3836', $12 = '+16509876905', $13 = 'USER', $14 = 'ENABLED', $15 = '2017-04-11 19:41:31.188', $16 = 'demouser9', $17 = '547', $18 = '10', $19 = '546'
2017-04-11 12:41:32 PDT      73842 DETAIL:  Process holding the lock: 73817. Wait queue: .
2017-04-11 12:41:32 PDT      73842 DETAIL:  Process 73842 waits for ExclusiveLock on tuple (206,43) of relation 17992 of database 16385; blocked by process 73817.
        Process 73817 waits for ShareLock on transaction 2716972; blocked by process 73842.
        Process 73817: update users set confirmationState=$1, country_iso=$2, dateOfBirth=$3, email=$4, firstName=$5, gender=$6, lastName=$7, lastUsedLocale=$8, middleName=$9, passwordHash=$10, passwordSalt=$11, phone=$12, role=$13, signInState=$14, updated=$15, username=$16, version=$17 where id=$18 and version=$19
2017-04-11 12:41:32 PDT      73817 LOG:  00000: execute S_10: ROLLBACK

流程 1 的流程为：

开始;
从用户中选择 *，其中 ID = 10;
从国家/地区选择 *;// 不影响问题
插入到用户事件（...， user_id， ...）值（...， 10， ...）;
更新用户设置 [..要更新的字段..]其中 id = 10 且版本 = 546;
犯;

流程 2 的流程为：

< li >开始； < li >从id = 10的用户中选择*； < li >更新用户集[..要更新的字段..]其中id = 10，版本= 546； < li >提交；

是因为在第一个事务中为INSERT获取FK (userEvents.user_id)上的排它锁，然后在第二个事务中为UPDATE (users.id = 10)和(userEvents.user_id)获取排它锁的顺序吗？

因为当我在“INSERT INTO userEvents”之前强制“UPDATE users”时，在高负载下我再也看不到死锁了。

齐弘业

2023-03-14

如果两个事务试图同时更新同一行，使用相同的版本，那么其中一个将获取该行的锁，另一个将阻塞。

只要第一个事务处于活动状态，第二个事务就不能决定它是否“获得”了乐观锁。如果第一个事务成功，第二个事务将发现版本不再匹配，并且将返回而不更新任何内容。但是，如果第一个事务回滚，第二个事务将获取旧版本上的锁并继续其更新。

同时，它只需要等待，一旦有事务等待，死锁场景就很容易构建。

Postgres：乐观锁定的奇怪行为

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档