[Pdns-users] Slight problem with PostgreSQL backend AXFR query

[ktm at rice.edu]

On Fri, Mar 28, 2014 at 08:29:03AM -0500, ktm at rice.edu wrote:
> Dear PDNS community,
> 
> While debugging a new installation we noticed that a slow zone transfer
> using a PostgreSQL backend would insert duplicate records if the transfer
> took over 1 minute and a second transfer was started before the first
> one completed. The problem is that the default isolation level for a
> postgres DB is READ COMMITTED, which means that the second query sees
> an identical picture of the DB as the first query, until the first
> query commits. By then it is too late and the second query commits
> as well, resulting in duplicate records in the DB. There is an easy
> fix, change the transaction isolation level to serializable at the
> start of the transfer. The first command is the delete-zone-query
> so it should be:
> 
> set transaction isolation level serializable; delete from records where domain_id=%d
> 
> instead of the bare:
> 
> delete from records where domain_id=%d
> 
> Then when the second AXFR starts, it errors out and is not allowed to
> insert the duplicate records.
> 
> Regards,
> Ken Marshall
> 

Hi,

I just looked at the MySQL documentation, and it looks like MySQL also
supports the "SET TRANSACTION ISOLATION LEVEL *" syntax. Unfortunately,
it looks like it must be issued before the "BEGIN" or "START TRANSACTION"
block, instead of after for PostgreSQL. Yuck. Is it possible to have the
server keep track of an existing AXFR and not try a new one if one is in
progress? This problem can also hit MySQL if they change their isolation
level. It just happens that the default is REPEATABLE READ for MySQL
while PostgreSQL uses READ COMMITTED by default. Another alternative
would be to explicitly set the isolation level when establishing the
connection to a PostgreSQL backend for the entire session with:

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

and use the corresponding MySQL idiom (from the manual):

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

I tested the latter command variant on PostgreSQL 8.3 and 9.3 and it works
for both so you can just use that for both MySQL and PostgreSQL in the
initial connection to the DB:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Regards,
Ken




> _______________________________________________
> Pdns-users mailing list
> Pdns-users at mailman.powerdns.com
> http://mailman.powerdns.com/mailman/listinfo/pdns-users
>