4

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.

wildplasser
  • 43,142
  • 8
  • 66
  • 109

1 Answers1

1

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • If all other queries will wait and never fail with a serialization issue (error 40001), this could work. –  Jul 08 '13 at 13:52
  • @omatrot That should be the case, but they may well fail with `40P01 Deadlock Detected` if you attempt to do it with triggers, since you'll have two tx's, each of which has a low-order lock for the `INSERT`, and each of which needs an `ACCESS EXCLUSIVE` lock that the other holds blocking lock for. That's only a problem if you `INSERT` from more than one transaction at a time, though. – Craig Ringer Jul 08 '13 at 13:59
  • I will take the LOCK TABLE... before INSERT route, I may have multiple transaction at the same time. I'll keep you updated about the progress. –  Jul 08 '13 at 14:58
  • @omatrot Just remember that the `LOCK` isn't released until the transaction `COMMIT`s or does a `ROLLBACK`. There's no manual `UNLOCK`. So you'll want to keep your transactions as short as possible and do the minimum possible in them if you're going to be doing full-table locking. You won't get any benefit from multiple worker threads, since you're forcing all inserts to happen serially. Come to think of it, you can avoid that doing an explicit `LOCK TABLE ... IN ACCESS EXCLUSIVE MODE` before the `SELECT` that must see consistent data and not worrying about manual locking before the `INSERT`s. – Craig Ringer Jul 09 '13 at 02:00
  • 1
    The problem is that 2phase commit is not 3phase commit. After the prepare+final commit, the resource manager (the "remote" database) has detached from the transaction, since its work is done. And it can participate in new (even distributed) transactions. Other RMs can commit (locally) *after* the first RM, and the coordinator ("transaction monitor") will *always* finalize its transaction after the last "committed" message comes in. The only way to avoid this kind of ghosts would be to disallow access to the RM without the coordinator in the middle. (Source: Gray&Reuter 10.4) – joop Jul 12 '13 at 16:05
  • @CraigRinger I stopped using this method as I discovered that because pg_dump takes a AccessSharedLock on the database, acquiring the lock may take the time for the backup to complete :-( –  Jan 07 '14 at 16:05
  • @omatrot Yeah. You should actually be able to use an `EXCLUSIVE` lock, not `ACCESS EXCLUSIVE`. That'll still block all writes, but won't block reads or `pg_dump`. – Craig Ringer Jan 07 '14 at 22:50