On Fri, Mar 28, 2014 at 08:29:03AM -0500, k...@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@mailman.powerdns.com > http://mailman.powerdns.com/mailman/listinfo/pdns-users > _______________________________________________ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
