Psycopg;如何检测行锁定？ [英] Psycopg; How to detect row locking?

问题描述

美好的一天，


我有很多线程在表中插入，之后我想要

来做一个选择。这意味着将要锁定（由DB）锁定我想要选择的行

。在这些情况下，我相信我收到了一个

的OperationalError（或者它是一个InterfaceError？）。


是否可能（如果是的话 - 如何？）由于行锁定，验证发生异常

，以便我可以等待再试一次？


或者有更好的方法来实现这一目标吗？我不太喜欢

轮询循环可能永远不会结束。


我正在使用python 2.3和psycopg 1.1.13以及PostgreSQL 7.4 .2。


问候，Alban Hertroys。

Good day,

I have a number of threads doing inserts in a table, after which I want
to do a select. This means that it will occur that the row that I want
to select is locked (by the DB). In these cases, I believe I receive an
OperationalError (or is it an InterfaceError?).

Is it possible (and if so - how?) to verify that the exception occured
because of row locking, so that I can wait and try again?

Or are there better ways to achieve this? I''m not too charmed about
polling loops that may never end.

I''m using python 2.3 with psycopg 1.1.13 and PostgreSQL 7.4.2.

Regards, Alban Hertroys.

推荐答案

Alban Hertroys< al *** @ magproductions.nl>写道：

Alban Hertroys <al***@magproductions.nl> writes:

美好的一天，

我有很多线程在表中插入，之后我想要做一个选择。这意味着我想要选择的行将被锁定（由DB）。在这些情况下，我相信我收到了一个
OperationalError（或者它是一个InterfaceError？）。

是否有可能（如果是这样 - 如何？）来验证发生了异常
或者有更好的方法来实现这一目标吗？我对于可能永远不会结束的轮询循环并不太感兴趣。

我正在使用python 2.3和psycopg 1.1.13以及PostgreSQL 7.4.2。

Good day,

I have a number of threads doing inserts in a table, after which I want
to do a select. This means that it will occur that the row that I want
to select is locked (by the DB). In these cases, I believe I receive an
OperationalError (or is it an InterfaceError?).

Is it possible (and if so - how?) to verify that the exception occured
because of row locking, so that I can wait and try again?

Or are there better ways to achieve this? I''m not too charmed about
polling loops that may never end.

I''m using python 2.3 with psycopg 1.1.13 and PostgreSQL 7.4.2.

您手动锁定这些行吗？如果是这样，你可以保持一些

结构来跟踪锁定的行。


如果你没有锁定，PostgreSQL会使用MVCC，它只能锁定

可能，你可以选择同一个

交易中的新数据和它外面的旧数据（直到它被提交）。

-

Godoy。 < go *** @ ieee.org>

Are you manually locking those rows? If so, you can maintain some
structure to keep track of locked rows.

If you are not locking, PostgreSQL uses MVCC where it locks as little as
possible and you are able to select the new data inside the same
transaction and old data outside of it (until it is commited).

--
Godoy. <go***@ieee.org>

Jorge Godoy写道：

Jorge Godoy wrote:

你手动锁定那些行吗？如果是这样，您可以保留一些
结构来跟踪锁定的行。


不，我不是。不过，这可能是一个解决方案。然后至少我*知道*当

a记录被锁定时......如果

交易在多个线程之间分配，那将会使事情复杂化。我不期待

到那个。


我认为行是锁定的，因为插入没有完成

插入了。 select在同一个会话AFAIK中进行，但不是在我的Python应用程序的同一个线程中的
。我很可能在这里看到一个

的竞争条件......（不需要多线程乐趣......）


我也是不知道我是否真的在看同一笔交易。

有没有办法验证这个？


我确实知道所有交易使用相同的数据库

连接（我在上下文对象中传递它，以及配置

设置和调试方法）。而且我也很确定它之间没有
提交。

如果你没有锁定，PostgreSQL会使用MVCC来锁定它可能，您可以在同一个交易中选择新数据，并在其外部选择旧数据（直到提交）。

Are you manually locking those rows? If so, you can maintain some
structure to keep track of locked rows.
No, I don''t. That may be a solution, though. Then at least I *know* when
a record is locked... But it''s going to complicate things, if the
transaction is split among multiple threads... I''m not looking forward
to that.

I think the rows are locked because the inserts haven''t finished
inserting yet. The select takes place in the same session AFAIK, but not
in the same thread of my Python application. I''m probably looking at a
race condition here... (Ain''t multithreading fun...)

I''m also not sure whether I''m actually looking at the same transaction.
Is there a way to verify such?

I do know for certain that all transactions use the same database
connection (I pass it along in a context object, together with config
settings and debugging methods). And I''m also quite sure that it doesn''t
commit in between.
If you are not locking, PostgreSQL uses MVCC where it locks as little as
possible and you are able to select the new data inside the same
transaction and old data outside of it (until it is commited).

我想在

插入期间必须有一段时间锁定行，我可能已经尝试选择它。如果这确实是这样的话，我希望收到行被锁定的情况。类型的错误。


或者，select可能正在等待（在psql中），直到插入

完成（在所有情况下都应该很快[*]），但这取决于我无法实现的实施
。不重要的是，在这种情况下，我不应该有这个问题。


[*]除非你用锁定的触发器来破坏你的数据库或者

类似的东西。这可能是PostgreSQL团队没有

的原因让我们选择等待直到同一行上的插入完成，但是

会返回错误。

I suppose there must be a short while where the row is locked during the
insert, where I may already be trying to select it. If this is indeed
the case, I would expect to receive a "row is locked" type of error.

Alternatively, the select may be waiting (w/in psql) until the insert
finished (which should be pretty soon in all cases[*]), but that depends
on implementations beyond my reach. Not that that matters, I shouldn''t
have this problem in that case.

[*] Unless you break your database with triggers that lock up or
something similar. That could be a reason for the PostgreSQL team to not
let select wait until an insert on the same row finished, but to
return an error instead.

Alban Hertroys< al *** @ magproductions.nl>写道：

Alban Hertroys <al***@magproductions.nl> writes:

Jorge Godoy写道：

Jorge Godoy wrote:

你手动锁定那些行吗？如果是这样，你可以保持一些
结构来跟踪锁定的行。
不，我不是。不过，这可能是一个解决方案。然后至少我*知道*当一条记录被锁定时...但如果
交易在多个线程之间分开，它会使事情变得复杂......我不是在寻找转发
。

Are you manually locking those rows? If so, you can maintain some
structure to keep track of locked rows.
No, I don''t. That may be a solution, though. Then at least I *know* when
a record is locked... But it''s going to complicate things, if the
transaction is split among multiple threads... I''m not looking forward
to that.

:-)我不认为锁定是一种解决方案...... MVCC方法

对我来说听起来好多了。

我认为这些行是锁定的，因为插入还没有完成
插入。 select在同一个会话AFAIK中进行，但不在我的Python应用程序的同一个线程中进行。我可能正在寻找一个
竞争条件......（不要多线程乐趣......）


如果没有数据那么a SELECT完全没有返回任何内容，没有

任何错误，毕竟你在查询数据库中找到一些信息

不存在。

我也不确定我是否真的在看同一笔交易。
有没有办法验证这样的？


您必须查看psycopg的文档。我在这里使用pyPgSQL。

我确实知道所有事务都使用相同的数据库
连接（我在上下文对象中传递它，以及配置
设置和调试方法）。而且我也很确定它之间并没有承诺。


如果要插入大量的b $ b数据，或者数据（和约束条件）之间存在某种关系，那么您将从很多交易中受益br />
触发和......在数据库中）。 INSERT不会被提交，直到你发出一个COMMIT
。之前的任何SELECT都不会返回任何内容。如果你没有使用交易，那么你就是在为每一个

命令命中磁盘。我在这里做了一些测试，区别在于一些

秒（有交易）到几分钟（没有交易）

，一个65k行插入旧我们做过的项目。

:-) I don''t think that locking is a solution... The MVCC approach
sounds much better to me.
I think the rows are locked because the inserts haven''t finished
inserting yet. The select takes place in the same session AFAIK, but not
in the same thread of my Python application. I''m probably looking at a
race condition here... (Ain''t multithreading fun...)
If there''s no data then a SELECT would return nothing at all, without
any error after all you''re querying the database for some information
that doesn''t exist.
I''m also not sure whether I''m actually looking at the same transaction.
Is there a way to verify such?
You have to look at psycopg''s docs. I use pyPgSQL here.
I do know for certain that all transactions use the same database
connection (I pass it along in a context object, together with config
settings and debugging methods). And I''m also quite sure that it doesn''t
commit in between.
You would benefit a lot of transactions if you are inserting a lot of
data or if there''s some relationship between data (and constraints and
triggers and ... at the database). The INSERT isn''t commited until you
issue a COMMIT. Any SELECT before that will return nothing. If you''re
not using transactions, then you are hitting the disk for each and every
command. I''ve made some tests here and the difference goes from some
seconds (with transactions) to several minutes (without transactions)
for a 65k rows insert on an old project we did.

如果你没有锁定，PostgreSQL会使用MVCC来尽可能地锁定它，你可以选择同一个
事务中的新数据和它之外的旧数据（直到它被提交）。

If you are not locking, PostgreSQL uses MVCC where it locks as little as
possible and you are able to select the new data inside the same
transaction and old data outside of it (until it is commited).

我想在行被锁定期间必须有一段时间
插入，我可能已经尝试选择它。如果确实如此，我希望收到行被锁定的情况。错误的类型。

I suppose there must be a short while where the row is locked during the
insert, where I may already be trying to select it. If this is indeed
the case, I would expect to receive a "row is locked" type of error.

没有这样的需要。那里没有数据所以SELECT返回

什么都没有。如果有数据并且你正在更新它，那么直到你提交

交易你得到旧值。

或者，选择可能正在等待（ w / in psql）直到插件完成（在所有情况下都应该很快[*]），但这取决于我无法实现的实现。不重要的是，在这种情况下我不应该有这个问题。

[*]除非你使用锁定或类似的触发器来破坏你的数据库。这可能是PostgreSQL团队不要让选择等到同一行上的插入完成的原因，而是为了返回错误。

There''s no such need. There''s no data there so the SELECT returns
nothing. If there''s data and you''re updating it, then until you commit
the transaction you get the old values.
Alternatively, the select may be waiting (w/in psql) until the insert
finished (which should be pretty soon in all cases[*]), but that depends
on implementations beyond my reach. Not that that matters, I shouldn''t
have this problem in that case.
[*] Unless you break your database with triggers that lock up or
something similar. That could be a reason for the PostgreSQL team to not
let select wait until an insert on the same row finished, but to
return an error instead.

他们使用MVCC：多版本并发控制。您可能想要了解它的b $ b：

http://www.linuxgazette.com/issue68/mitchell.html
http://www.developer.com/open/article.php/877181

见到你，

-

Godoy。 < go *** @ ieee.org>

They use MVCC: Multi-Version Concurrency Control. You might want to
read about it:

http://www.linuxgazette.com/issue68/mitchell.html
http://www.developer.com/open/article.php/877181
Be seeing you,
--
Godoy. <go***@ieee.org>

这篇关于Psycopg;如何检测行锁定？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！