在SELECT期间如何等待未决的INSERT提交? [英] How to wait during SELECT that pending INSERT commit?

问题描述

我在Windows环境中使用PostgreSQL 9.2. 我处于使用MSDTC的2PC( 2阶段提交)环境中.

I'm using PostgreSQL 9.2 in a Windows environment. I'm in a 2PC (2 phase commit) environment using MSDTC.

我有一个客户端应用程序，该应用程序以SERIALIZABLE隔离级别启动事务，在表中为特定的外键值(该列上有一个索引)插入新的数据行，并投票完成该操作.交易(交易已准备).交易将由交易协调员提交.

I have a client application, that starts a transaction at the SERIALIZABLE isolation level, inserts a new row of data in a table for a specific foreign key value (there is an index on the column), and vote for completion of the transaction (The transaction is PREPARED). The transaction will be COMMITED by the Transaction Coordinator.

此后，立即在事务外部，同一客户请求所有行以获取相同的特定外键值.

Immediatly after that, outside of a transaction, the same client requests all the rows for this same specific foreign key value.

由于在真正提交之前的事务之前可能会有一个延迟，因此SELECT子句可能返回该数据的以前的快照.实际上，它有时确实会发生，这是有问题的.当然，可能会重新设计该应用程序，但在此之前，我一直在寻找一种锁定解决方案.咨询锁?

Because there may be a delay before the previous transaction is really commited, the SELECT clause may return a previous snapshot of the data. In fact, it does happen sometimes, and this is problematic. Of course the application may be redesigned but until then, I'm looking for a lock solution. Advisory Lock ?

我已经解决了在特定行上执行UPDATE，然后使用SELECT ... FOR SHARE的问题，并且效果很好. SELECT等到事务提交并返回旧行和新行.

I already solved the problem while performing UPDATE on specific rows, then using SELECT...FOR SHARE, and it works well. The SELECT waits until the transaction commits and return old and new rows.

现在，我正在尝试为INSERT解决它. SELECT ... FOR SHARE不会阻止并立即返回.

Now I'm trying to solve it for INSERT. SELECT...FOR SHARE does not block and return immediatley.

这里没有并发问题，因为只有一个客户端处理一组特定的行.我已经了解MVCC.

There is no concurrency issue here as only one client deals with a specific set of rows. I already know about MVCC.

任何帮助表示赞赏.

推荐答案

要等待尚未提交的INSERT，您需要使用谓词锁. PostgreSQL对可序列化支持的谓词锁定功能有限，但不会直接向用户公开.

To wait for a not-yet-committed INSERT you'd need to take a predicate lock. There's limited predicate locking in PostgreSQL for the serializable support, but it's not exposed directly to the user.

简单的SERIALIZABLE隔离对您无济于事，因为SERIALIZABLE仅要求存在 个顺序，在该顺序中可以发生事务以产生一致的结果.在您的情况下，此顺序为SELECT，后跟INSERT.

Simple SERIALIZABLE isolation won't help you here, because SERIALIZABLE only requires that there be an order in which the transactions could've occurred to produce a consistent result. In your case this ordering is SELECT followed by INSERT.

我唯一想到的选择是在INSERT之前对表进行ACCESS EXCLUSIVE锁定.这只会在COMMIT PREPARED或ROLLBACK PREPARED时间释放，与此同时，任何其他查询都将等待锁定.您可以通过BEFORE触发器强制执行此操作，从而无需更改应用程序.如果这样做的话，您可能会得到奇数的死锁和回滚，因为INSERT将获得较低的锁，然后您将尝试在触发器中进行锁升级.如果可能的话，最好在INSERT之前先运行LOCK TABLE ... IN ACCESS EXCLUSIVE MODE命令 .

The only option I can think of is to take an ACCESS EXCLUSIVE lock on the table before INSERTing. This will only get released at COMMIT PREPARED or ROLLBACK PREPARED time, and in the mean time any other queries will wait for the lock. You can enforce this via a BEFORE trigger to avoid the need to change the app. You'll probably get the odd deadlock and rollback if you do it that way, though, because INSERT will take a lower lock then you'll attempt lock promotion in the trigger. If possible it's better to run the LOCK TABLE ... IN ACCESS EXCLUSIVE MODE command before the INSERT.

正如您所提到的，这主要是一个应用程序设计错误的问题.期望看到尚未提交的行实际上没有任何意义.

As you've alluded to, this is mostly an application mis-design problem. Expecting to see not-yet-committed rows doesn't really make any sense.

这篇关于在SELECT期间如何等待未决的INSERT提交?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！