我在Windows环境中使用PostgreSQL 9.2。 我在使用MSDTC的2PC（ 2阶段提交 ）环境中。
我有一个客户端应用程序，它在SERIALIZABLE隔离级别启动一个事务，在表中为特定的外键值插入一个新的数据行（列上有一个索引），并投票完成事务（交易已准备好）。 交易将由交易协调员进行交易。
在此之后，在事务之外，同一客户端请求此相同特定外键值的所有行。
由于在真正提交上一个事务之前可能存在延迟，因此SELECT子句可能会返回数据的先前快照。 事实上，它确实有时发生，这是有问题的。 当然应用程序可能会重新设计，但在那之前，我正在寻找一个锁定解决方案。 咨询锁？
我已经在特定行上执行UPDATE时解决了问题，然后使用SELECT ... FOR SHARE，它运行良好。 SELECT等待直到事务提交并返回旧行和新行。
现在我正在尝试为INSERT解决它。 SELECT ... FOR SHARE不会阻止并返回immediatley。
这里没有并发问题，因为只有一个客户端处理一组特定的行。 我已经了解MVCC。
任何帮助赞赏。
I'm using PostgreSQL 9.2 in a Windows environment. I'm in a 2PC (2 phase commit) environment using MSDTC.
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.
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 ?
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.
Now I'm trying to solve it for INSERT. SELECT...FOR SHARE does not block and return immediatley.
There is no concurrency issue here as only one client deals with a specific set of rows. I already know about MVCC.
Any help appreciated.
最满意答案
要等待尚未提交的INSERT您需要进行谓词锁定 。 PostgreSQL中的序列化支持的谓词锁定有限，但它不直接暴露给用户。
简单的SERIALIZABLE隔离在这里对你没有帮助，因为SERIALIZABLE只需要有一个订单来进行交易以产生一致的结果。 在您的情况下，此顺序是SELECT然后是INSERT 。
我能想到的唯一选择是在INSERT之前对表进行ACCESS EXCLUSIVE锁定。 这只会在COMMIT PREPARED或ROLLBACK PREPARED时间释放，同时任何其他查询都会等待锁定。 您可以通过BEFORE触发器强制执行此操作，以避免更改应用程序。 但是，如果你这样做，你可能会得到奇怪的死锁和回滚，因为INSERT将采用较低的锁定，然后你将在触发器中尝试锁定升级。 如果可能，最好在INSERT 之前运行LOCK TABLE ... IN ACCESS EXCLUSIVE MODE命令。
正如您所提到的，这主要是应用程序错误设计问题。 期待看到尚未提交的行并没有任何意义。
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.
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.
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.
更多推荐
发布评论