- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在调试使用 JPA/Hibernate 和 Postgres (9.6.2) 的 Java 应用程序的奇怪行为。
应用程序有 3 个实体:User、Country、UserEvent。
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有shared和exclusive;行、表或索引级别的锁。以错误的顺序在相同的对象上获取它们可能会导致死锁。
但对于这种特殊情况,为什么我会遇到乐观锁定的死锁?在这种情况下,我希望一笔交易成功,一笔交易返回 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 的流程是:
进程 2 的流程是:
是否因为顺序获取FK (userEvents.user_id) 的排他锁以进行插入,然后获取UPDATE 的排他锁(users.id = 10) 在第一个事务中,(users.id = 10) 和(userEvents.user_id) 在第二个更新中em>?
因为当我在“INSERT INTO userEvents”之前强制“UPDATE users”时,我在高负载下不再看到死锁。
最佳答案
如果两个事务试图同时更新具有相同版本
的同一行,那么其中一个将获取该行的锁,而另一个将阻塞。
只要第一个事务处于事件状态,第二个事务就无法决定它是否“获得”了乐观锁。如果第一个事务成功,第二个事务将发现 version
不再匹配,并且将不更新任何内容而返回。但是,如果第一个事务回滚,第二个事务将获取旧版本
上的锁并继续更新。
与此同时,它只需要等待,一旦您有事务等待,就很容易构造死锁场景。
关于postgresql - Postgres : strange behavior with optimistic locking,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43247757/
我想知道锁是如何在 Java 中实现的。在一些教程中,我发现它们应该使用 wait() 和 notify()/notifyAll() 以及一些 boolean 标志来实现(它可能比那复杂得多,但基本上
我正在开发一个多线程服务器,用于存储和读取来自数据库的信息。数据库是用 RocksDB 实现的。 我遇到的问题是,当我一次从多个线程访问数据库时,我得到了那个错误。 通常是说db在usign后没有被删
例如,这里有一些 linux 中的代码: void set_leds(int val) { int fd = open ("/dev/console", O_WRONLY); // argumen
关闭。这个问题需要更多focused .它目前不接受答案。 想改进这个问题吗? 更新问题,使其只关注一个问题 editing this post . 关闭 9 年前。 Improve this qu
考虑以下代码。为了防止IndexOutOfBoundsException打电话时 listIterator ,我们使用读取器锁来检索基于索引的 iteartor,并在对 stockCodes 进行写操
这个问题在这里已经有了答案: Java locking structure best pattern (2 个答案) 关闭 6 年前。 有什么区别: private Lock lock = new
是否可以在网页加载时检查 Scroll Lock、Num Lock 和 Caps Lock 的状态?我找到了使用 JavaScript 在按键后进行检查的方法,但这不是我要问的。 最佳答案 2019
是否可以在网页加载时检查 Scroll Lock、Num Lock 和 Caps Lock 的状态?我找到了使用 JavaScript 在按键后进行检查的方法,但这不是我要问的。 最佳答案 2019
在Ubuntu中安装某些东西时出现错误。我尝试了一些命令来杀死正在运行的进程,但仍然遇到相同的错误。 最佳答案 根据文章How to Fix “Waiting for cache lock: Coul
我们有一个gradle构建,它可以动态创建GradleBuild类型的多个任务,以与不同的客户端库版本一起运行以测试其兼容性。在Gradle4上可以正常工作,但是在移至Gradle5后,执行第一项任务
我在尝试运行在 vertica 数据库表上运行查询的客户端应用程序时反复遇到此错误。 有人可以帮助解决这个问题吗? 最佳答案 我想知道您的客户端应用程序向 Vertica 发送了哪种“查询”(以 RE
我有一个创建锁的方法。 ReadWriteLock lock = new ReentrantReadWriteLock(); 然后我使用 Lock Interface 将该对象传递到一个方法中。 m
正如问题所问,我知道这是可能的on Linux ,但我找不到任何适用于 Windows 的最新信息。有可能吗? 最佳答案 您可以使用 ctypes 加载 user32.dll 然后调用 GetKeyS
这是同一个问题 Change keyboard locks in Python或者 How to change caps lock status without key press . 但还是有区别的
与 this question 相同,不知道为什么Java团队没有在Lock中添加一些默认方法界面,类似这样: public default void withLock(Runnable r) {
我有一个带有package-lock.json文件的项目。 现在,我想基于yarn.lock文件或项目的现有package-lock.json生成node_modules文件。 我怎样才能做到这一点?
我正在查看 pthreadtypes.h 文件中的 pthread_mutex_t 结构。 “__lock”代表什么?它就像分配给互斥锁的锁号吗? typedef union { struct _
我正在研究避免死锁的措施,其中一种可能的方法是通过强制线程放弃它在访问另一个锁但无法访问该锁时已经持有的锁来打破循环等待。 以最简单的银行账户转账为例: class Account { priva
我被要求支持一些遗留代码,我看到了一些让我摸不着头脑的事情。在某些代码段中,我看到类实例使用 CMutex 实例来同步方法执行。例如 class CClassA : public CObject {
当我们调用 lock.lock() 或尝试进入一个 synchronized block 时,如果其他线程已经获取了该锁,我们的线程就会阻塞。现在我的问题是,当我们查看 lock.lock() 的实现
我是一名优秀的程序员,十分优秀!