PostgreSQL 锁定问题

发布于 2024-10-17 20:41:07 字数 260 浏览 2 评论 0原文

我试图弄清楚如何锁定整个表以防止在 Postgres 中写入,但它似乎不起作用,所以我假设我做错了什么。

例如,表名称是“users”。

在独占模式下锁定表用户;

当我检查视图 pg_locks 时,它似乎不在那里。我也尝试过其他锁定模式,但无济于事。

其他事务也能够执行 LOCK 功能,并且不会像我想象的那样阻塞。

在 psql 工具 (8.1) 中,我只是返回 LOCK TABLE。

任何帮助都会很棒。

I am trying to figure out how to lock an entire table from writing in Postgres however it doesn't seem to be working so I am assuming I am doing something wrong.

Table name is 'users' for example.

LOCK TABLE users IN EXCLUSIVE MODE;

When I check the view pg_locks it doesn't seem to be in there. I've tried other locking modes as well to no avail.

Other transactions are also capable of performing the LOCK function and do not block like I assumed they would.

In the psql tool (8.1) I simply get back LOCK TABLE.

Any help would be wonderful.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

腻橙味 2024-10-24 20:41:07

SQL 标准中没有 LOCK TABLE,而是使用 SET TRANSACTION 来指定事务的并发级别。您应该能够在像这样的事务中使用 LOCK,

BEGIN WORK;
LOCK TABLE table_name IN ACCESS EXCLUSIVE MODE;
SELECT * FROM table_name WHERE id=10;
Update table_name SET field1=test WHERE id=10;
COMMIT WORK;

我实际上在我的数据库上测试了这一点。

There is no LOCK TABLE in the SQL standard, which instead uses SET TRANSACTION to specify concurrency levels on transactions. You should be able to use LOCK in transactions like this one

BEGIN WORK;
LOCK TABLE table_name IN ACCESS EXCLUSIVE MODE;
SELECT * FROM table_name WHERE id=10;
Update table_name SET field1=test WHERE id=10;
COMMIT WORK;

I actually tested this on my db.

送你一个梦 2024-10-24 20:41:07

请记住,“锁定表”仅持续到事务结束。所以除非你已经在psql中发出了“开始”,否则它是无效的。

(在 9.0 中,这会出现错误:“LOCK TABLE 只能在事务块中使用”。8.1 非常旧)

Bear in mind that "lock table" only lasts until the end of a transaction. So it is ineffective unless you have already issued a "begin" in psql.

(in 9.0 this gives an error: "LOCK TABLE can only be used in transaction blocks". 8.1 is very old)

微凉徒眸意 2024-10-24 20:41:07

该锁仅在当前事务结束之前有效,并在事务提交(或回滚)时释放。

因此,您必须将该语句嵌入到 BEGINCOMMIT/ROLLBACK 块中。执行后:

BEGIN;
LOCK TABLE users IN EXCLUSIVE MODE;

您可以运行以下查询来查看当前 users 表上哪些锁处于活动状态:

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid WHERE relation = 'users'::regclass::oid;

该查询应显示 users 表上的独占锁。执行 COMMIT 并重新运行上述查询后,锁不应再存在。

此外,您可以使用锁跟踪工具,例如 https://github.com/jnidzwetzki/ pg-lock-tracer/ 实时了解 PostgreSQL 服务器的锁定活动。使用此类锁跟踪工具,您可以实时查看哪些锁被获取和释放。

The lock is only active until the end of the current transaction and released when the transaction is committed (or rolled back).

Therefore, you have to embed the statement into a BEGIN and COMMIT/ROLLBACK block. After executing:

BEGIN;
LOCK TABLE users IN EXCLUSIVE MODE;

you could run the following query to see which locks are active on the users table at the moment:

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid WHERE relation = 'users'::regclass::oid;

The query should show the exclusive lock on the users table. After you perform a COMMIT and you re-run the above-mentioned query, the lock should not longer be present.

In addition, you could use a lock tracing tool like https://github.com/jnidzwetzki/pg-lock-tracer/ to get real-time insights into the locking activity of the PostgreSQL server. Using such lock tracing tools, you can see which locks are taken and released in real-time.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文