postgresql锁定整个表！ [英] postgresql locks the whole table!
问题描述
帮助！
我有一个表，多个进程必须能够同时写入
写入。但是，它出于某种原因
被锁定在独占模式。我把它缩小到了一个SQL语句+一些奇怪的外键。
为了调试这个，我打开了两个psql会话并输入了
手动sql语句。情况如下：
CREATE TABLE take2
（
id serial not null，
timestamp timestamp NOT NULL DEFAULT now（），
description text，
iteration smallint，
asset_id integer，
- FOREIGN KEY（asset_id）REFERENCES public.asset
（id）， - ON DELDATE CASCADE ON DELETE CASCADE，
主键（id）<
）;
（注意外键声明被注释
out）。为了确保我没有造成过多的无意识锁定，我做了设置事务
隔离级别读取已提交在两个psql shell中
（默认是可序列化的）。
现在我输入以下命令：
shell 1：
1.开始
2.插入take2值（默认，''now（）''，''t1''，1，
1）;
shell 2：
1.开始
2.插入take2值（默认值，''now（）''，'t2''，1，
1）;
这是有效的。
但是，如果我取消注释外键语句并且
重新创建表，那么第二个shell就会在插入语句中阻止
。一旦第一笔交易
被提交或回滚，插入
语句就会通过。
我的问题是为什么？？？这两个插入操作并没有相互冲突（至少不是在实际情况下的b $ b b）。另外，为什么外键
有所作为呢？
看着pg_locks，我看到以下内容：
关系|数据库|交易| pid |
模式|授予
---------- + ---------- + ------------- + ----- - + ------------------ + ---------
39356 | 34862 | NULL | 18671 |
AccessShareLock | t
39356 | 34862 | NULL | 18671 |
RowExclusiveLock | t
NULL | NULL | 9914 | 18671 |
ExclusiveLock | t
39354 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
RowShareLock | t
16759 | 34862 | NULL | 18671 |
AccessShareLock | t $ / $
（7行）
ExclusiveLock来自哪里？什么是
锁定？
我们同时运行多笔交易
至关重要 - 实际上这是一个选择PostgreSQL而不是MySQL的原因
有很多
文件系统操作和其他处理需要
与DB事务一起发生。这些东西
需要很长时间，因此BEGIN和COMMIT之间通常最多有5分钟的间隔。当用户试图运行交易时，我们不能
阻止生产层5分钟，所以作为临时修复，我们
得到了摆脱开始/提交。但很明显我们会把b $ b而不是失去原子性。
所以，总结一下：
为什么PostgreSQL会锁定整个表格？
我们可以做些什么呢？
这是在PostgreSQL 7.4.0和7.3.2上测试的。
提前谢谢，
Eugene
__________________________________
你是Yahoo！？
免费弹出窗口阻止程序 - 立即获取
http：// companion。 yahoo.com/
---------------------------（...结束广播）---------------------------
提示9：计划者将忽略您选择索引的愿望扫描你的
加入列的数据类型是否匹配
Help!
I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manually. Here is the situation:
CREATE TABLE take2
(
id serial not null,
timestamp timestamp NOT NULL DEFAULT now(),
description text,
iteration smallint,
asset_id integer,
-- FOREIGN KEY (asset_id) REFERENCES public.asset
(id), -- ON UPDATE CASCADE ON DELETE CASCADE,
primary key(id)
);
(notice that the foreign key statement is commented
out). Just to make sure I am not causing excessive
locking unintentionally, I did "set transaction
isolation level read committed" in both psql shells
(default was serializable).
Now I type the following commands:
shell 1:
1. BEGIN
2. insert into take2 values(default, ''now()'', ''t1'', 1,
1);
shell 2:
1. BEGIN
2. insert into take2 values(default, ''now()'', ''t2'', 1,
1);
this works.
However, if I uncomment the foreign key statement and
recreate the table, then the second shell blocks on
the insert statement. As soon as the first transaction
is either committed or rolled back, the insert
statement goes through.
My question is why??? The two insert operations do not
conflict with each other (at least not in the
real-world situation). Also, why does the foreign key
make a difference?
looking at pg_locks, I see the following:
relation | database | transaction | pid |
mode | granted
----------+----------+-------------+-------+------------------+---------
39356 | 34862 | NULL | 18671 |
AccessShareLock | t
39356 | 34862 | NULL | 18671 |
RowExclusiveLock | t
NULL | NULL | 9914 | 18671 |
ExclusiveLock | t
39354 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
RowShareLock | t
16759 | 34862 | NULL | 18671 |
AccessShareLock | t
(7 rows)
Where does the ExclusiveLock come from? What is being
locked?
It is critical for us to run multiple transactions
concurrently -- in fact that was one of the reasons
for choosing PostgreSQL over MySQL. There are a lot of
file system operations and other processing that need
to happen along side the DB transaction. Those things
take a long time, so there is typically up to a
5-minute span between BEGIN and COMMIT. We cannot
block the production floor for 5 minutes when a user
tries to run a transaction, so as a temporary fix, we
got rid of the begin/commit. But obviously we would
rather not lose the atomicity.
So, in summary:
why does PostgreSQL lock the entire table?
what can we do about it?
This was tested on PostgreSQL 7.4.0 and 7.3.2.
thanks in advance,
Eugene
__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
推荐答案
Dr NoName写道：
Dr NoName wrote:
帮助！
我有一个表，多个进程必须能够同时写入。但是，它由于某种原因被锁定在独占模式。我把它缩小到一个SQL语句+外键的一些奇怪。
为了调试这个，我打开了两个psql会话并手动输入了sql语句。以下是这种情况：
CREATE TABLE take2
（id = serial not null，
时间戳时间戳NOT NULL DEFAULT now（），
描述文字，
迭代smallint，
asset_id整数，
- FOREIGN KEY（asset_id）REFERENCES public.asset
（id）， - ON UPDATE CASCADE ON DELETE CASCADE，
主键（id）
）;
....
1.开始
2.插入take2值（默认，''now（）''，'' t1''，1，
1）;
....
所以，总结一下：
为什么PostgreSQL会锁定整个表格？
它没有锁定整个表，它锁定了资产行
其中asset_id为1 FOR UPDATE。当两个同时插入在同一个父行的同一个子表中时，它相当于
两个并发的SELECT ... FOR UPDATE查询正在执行
父行。
我们可以做些什么呢？
Help!
I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manually. Here is the situation:
CREATE TABLE take2
(
id serial not null,
timestamp timestamp NOT NULL DEFAULT now(),
description text,
iteration smallint,
asset_id integer,
-- FOREIGN KEY (asset_id) REFERENCES public.asset
(id), -- ON UPDATE CASCADE ON DELETE CASCADE,
primary key(id)
);
....
1. BEGIN
2. insert into take2 values(default, ''now()'', ''t1'', 1,
1);
....
So, in summary:
why does PostgreSQL lock the entire table?
It isn''t locking the entire table, it is locking the row of asset
where asset_id is 1 FOR UPDATE. When two simultaneous inserts occur in
the same child table for the same parent row, it is the equivalent of
two concurrent SELECT ... FOR UPDATE queries being executed against
the parent row.
what can we do about it?
不多，我很害怕。 PostgreSQL非常需要一个锁定级别，其中一个
行仅锁定UPDATE和DELETE并且不是伪SELECT ...
FOR RI_CHECK ....
Mike Mascari
ma*****@mascari.com
---------------------------（广播结束）------------- --------------
提示9：如果您的
加入专栏，计划员将无视您选择索引扫描的愿望s数据类型不匹配
Not much, I''m afraid. PostgreSQL badly needs a lock level whereby a
row is only locked for UPDATEs and DELETEs and not a pseudo SELECT ...
FOR RI_CHECK....
Mike Mascari
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
Dr NoName写道：
Dr NoName wrote:
帮助！
我有一个表，多个进程必须能够同时写入。但是，它由于某种原因被锁定在独占模式。我把它缩小到一个SQL语句+外键的一些奇怪。
为了调试这个，我打开了两个psql会话并手动输入了sql语句。以下是这种情况：
CREATE TABLE take2
（id = serial not null，
时间戳时间戳NOT NULL DEFAULT now（），
描述文字，
迭代smallint，
asset_id整数，
- FOREIGN KEY（asset_id）REFERENCES public.asset
（id）， - ON UPDATE CASCADE ON DELETE CASCADE，
主键（id）
）
（注意外键语句被注释
）。为了确保我没有无意中造成过多的锁定，我做了设置事务
隔离级别读取已提交。在两个psql shell中
（默认是可序列化的）。
现在我输入以下命令：
shell 1：
1。 BEGIN
2.插入take2值（默认，''now（）''，'t1''，1，
1）;
shell 2：
1. BEGIN
2.插入take2值（默认，''now（）''，''t2''，1，
1）;
<这是有效的。
但是，如果我取消注释外键语句并重新创建表，那么第二个shell就会阻塞插入语句。一旦第一笔交易
被提交或回滚，插入
语句就会通过。
我的问题是为什么???这两个插入操作不会相互冲突（至少不是在真实情况下）。另外，为什么外键有所作为呢？
因为PostgreSQL没有在行级实现共享读锁
因此最轻的锁定外键约束可以采取的是
a写锁定。
如果你不能让你的transactons更短（请不要告诉我
您在持有任何打开的
交易时进行用户交互），那么您可以通过
推迟外键检查来增加并发性直到提交。
Jan
看着pg_locks，我看到以下内容：
数据库|交易| pid |
模式|授予
---------- + ---------- + ------------- + ------- + - ----------------- + ---------
39356 | 34862 | NULL | 18671 |
AccessShareLock | t
39356 | 34862 | NULL | 18671 |
RowExclusiveLock | t
NULL | NULL | 9914 | 18671 |
ExclusiveLock | t
39354 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
RowShareLock | t
16759 | 34862 | NULL | 18671 |
AccessShareLock | t
（7行）
ExclusiveLock来自哪里？什么被锁定？
我们同时运行多个事务是至关重要的 - 事实上这是选择PostgreSQL而不是MySQL的原因之一。在数据库事务中需要进行大量的文件系统操作和其他处理。那些东西需要很长时间，因此BEGIN和COMMIT之间的间隔通常达到5分钟。当用户尝试运行事务时，我们无法阻止生产层5分钟，因此作为临时修复，我们将摆脱开始/提交。但显然我们不会失去原子性。
所以，总结一下：为什么PostgreSQL会锁定整个表？
我们能做些什么呢？
这是在PostgreSQL 7.4.0和7.3.2上测试的。
提前感谢，
Eugene
__________________________________
你是雅虎吗？？
免费弹出窗口阻止程序 - 立即获取
http://companion.yahoo.com/
------------------------ ---（播出结束）---------------------------
提示9：规划师会忽略你的选择欲望如果您的
加入列的数据类型不匹配，则进行索引扫描
Help!
I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manually. Here is the situation:
CREATE TABLE take2
(
id serial not null,
timestamp timestamp NOT NULL DEFAULT now(),
description text,
iteration smallint,
asset_id integer,
-- FOREIGN KEY (asset_id) REFERENCES public.asset
(id), -- ON UPDATE CASCADE ON DELETE CASCADE,
primary key(id)
);
(notice that the foreign key statement is commented
out). Just to make sure I am not causing excessive
locking unintentionally, I did "set transaction
isolation level read committed" in both psql shells
(default was serializable).
Now I type the following commands:
shell 1:
1. BEGIN
2. insert into take2 values(default, ''now()'', ''t1'', 1,
1);
shell 2:
1. BEGIN
2. insert into take2 values(default, ''now()'', ''t2'', 1,
1);
this works.
However, if I uncomment the foreign key statement and
recreate the table, then the second shell blocks on
the insert statement. As soon as the first transaction
is either committed or rolled back, the insert
statement goes through.
My question is why??? The two insert operations do not
conflict with each other (at least not in the
real-world situation). Also, why does the foreign key
make a difference?
Because PostgreSQL does not implement shared read locks on the row level
and therefore the "lightest" lock the foreign key constraint can take is
a write lock.
If you cannot make your transactons shorter (and please don''t tell me
that you have user interaction going on while holding any open
transactions), then you might be able to increase your concurrency by
deferring the foreign key check until commit.
Jan
looking at pg_locks, I see the following:
relation | database | transaction | pid |
mode | granted
----------+----------+-------------+-------+------------------+---------
39356 | 34862 | NULL | 18671 |
AccessShareLock | t
39356 | 34862 | NULL | 18671 |
RowExclusiveLock | t
NULL | NULL | 9914 | 18671 |
ExclusiveLock | t
39354 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
RowShareLock | t
16759 | 34862 | NULL | 18671 |
AccessShareLock | t
(7 rows)
Where does the ExclusiveLock come from? What is being
locked?
It is critical for us to run multiple transactions
concurrently -- in fact that was one of the reasons
for choosing PostgreSQL over MySQL. There are a lot of
file system operations and other processing that need
to happen along side the DB transaction. Those things
take a long time, so there is typically up to a
5-minute span between BEGIN and COMMIT. We cannot
block the production floor for 5 minutes when a user
tries to run a transaction, so as a temporary fix, we
got rid of the begin/commit. But obviously we would
rather not lose the atomicity.
So, in summary:
why does PostgreSQL lock the entire table?
what can we do about it?
This was tested on PostgreSQL 7.4.0 and 7.3.2.
thanks in advance,
Eugene
__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
-
＃========= ======================================== ========== ===========＃
＃因为错误而得到宽恕比对正确更容易。 ＃
＃让我们打破这个规则 - 请原谅我。 ＃
＃======================================== ========= = Ja******@Yahoo.com ＃
---------------------------（播出结束）-------------- -------------
提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org
--
#================================================= =====================#
# It''s easier to get forgiveness for being wrong than for being right. #
# Let''s break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
Dr NoName< sp * *******@yahoo.com>写道：
Dr NoName <sp********@yahoo.com> writes:
我的问题是为什么???这两个插入操作不会相互冲突（至少不是在真实情况下）。另外，为什么外键有所作为呢？
My question is why??? The two insert operations do not
conflict with each other (at least not in the
real-world situation). Also, why does the foreign key
make a difference?
它没有锁定整个表，它锁定了记录外键
引用。请注意，它们都引用相同的外键。
它这样做是因为它害怕有人会在
之前删除该键事务提交。它必须采取锁定，以防止某人
删除记录（或更新引用的列）。
不幸的是，唯一可供选择的锁是独家写锁。你已经注意到，这是'b $ b矫枉过正。我认为这是多人会想通过引入共享锁来解决问题，但我不希望很快就能找到解决方案
。
我不知道是否有任何解决方法比仅仅放弃国外
关键参考更好。
- -
greg
---------------------------（播出结束） ---------------------------
提示4：不要'杀'-9''邮政局长
It''s not locking the whole table, it''s locking the record that the foreign key
references. Note that they''re both referencing the same foreign key.
It does this because it''s afraid someone will go and delete that key before
the transaction commits. It has to take a lock that will prevent someone from
deleting the record (or updating the referenced column).
Unfortunately the only lock to choose from is an exclusive write lock. That''s
overkill as you''ve noticed. I think this is something multiple people would
like to fix by introducing shared locks, but I wouldn''t expect a solution
soon.
I don''t know if there''s any work-around better than just dropping the foreign
key reference.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster
这篇关于postgresql锁定整个表！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
