帮助！ 我有一个表，多个进程必须能够同时写入写入。但是，它出于某种原因 被锁定在独占模式。我把它缩小到了一个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/ ---------------------------（...结束广播）--------------------------- 提示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 companion.yahoo/ ---------------------------(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 ---------------------------（广播结束）------------- -------------- 提示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 ---------------------------(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 __________________________________ 你是雅虎吗？？免费弹出窗口阻止程序 - 立即获取 companion.yahoo/ ------------------------ ---（播出结束）--------------------------- 提示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 companion.yahoo/ ---------------------------(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 ＃ ---------------------------（播出结束）-------------- ------------- 提示1：订阅和取消订阅命令转到 ma ******* @ postgresql
-- #================================================= =====================# # It''s easier to get forgiveness for being wrong than for being right. # # Let''s break this rule - forgive me. # #================================================= = Ja******@Yahoo # ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql
Dr NoName< sp * *******@yahoo>写道： Dr NoName <sp********@yahoo> 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锁定整个表！
发布评论