11. Locking Issues

One of the usual merits the use, by PostgreSQL, of Multi-Version Concurrency Control (MVCC) is that this eliminates a whole host of reasons to need to lock database objects. On some other database systems, you need to acquire a table lock in order to insert data into the table; that can severely hinder performance. On other systems, read locks can impede writes; with MVCC, PostgreSQL eliminates that whole class of locks in that "old reads" can access "old tuples." Most of the time, this allows the gentle user of PostgreSQL to not need to worry very much about locks.

Unfortunately, there are several sorts of Slony-I events that do require exclusive locks on PostgreSQL tables, with the result that modifying Slony-I configuration can bring back some of those "locking irritations." In particular:

Each of these actions requires, at some point, modifying each of the tables in the affected replication set, which requires acquiring an exclusive lock on the table. Some users that have tried running these operations on Slony-I nodes that were actively servicing applications have experienced difficulties with deadlocks and/or with the operations hanging up.

The obvious question: "What to do about such deadlocks?"

Several possibilities admit themselves:

Regrettably, there is no perfect answer to this. If it is necessary to submit a MOVE SET request, then it is presumably necessary to accept the brief application outage. As Slony-I/ pgpool linkages improve, that may become a better way to handle this.