Hi all
Today, we had another replag problem, caused by Aka's user stats script. Replag kept rising even after the script was stopped (not killed). Apparently, the reason lies with the transaction mode used: a long running query may lock (parts of) the tables it uses, denying any updates the replication daemon wants to make.
As far as I know, this should not happen if the long running query uses READ UNCOMMITTED (dirty read). Using it should not cause serious problems in our context, save a bit of resources and allow replication to continue while reading from tables. Details on transaction isolation levels can be found here: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html
DaB. has set the global default isolation level to READ UNCOMMITTED for now. If you are experiencing problems or need data that is guarantied to be consistent under all circumstances, *and* your query does not take long (no more than, say, a minute or so), you can change the isolation level like this:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
REPEATABLE READ is MySQL's "normal" default, i.e. what you have been using until now if you did not set the isolation level explicitly. There are other options, look at the documentation link above.
I hope this actually does solve the problem, and does not cause you too much trouble.
Regards -- Daniel