On 2013-10-02 12:50, Paul J Stevens wrote:
The maintenance runs are already designed to avoid long-running table
locks.
I would prefer something like:
BEGIN;
DELETE FROM table WHERE table.id in (SELECT ...);
COMMIT;
but this will lock up the table involved for the duration, which could
be long. Also, mysql (still) being somewhat brain-dead it does allow
you
to reference the table you delete from in the sub-select.
So, as a compromise dbmail first fetches a list of ids to delete, and
then iterates over the list, deleting the rows involved one by one.
This is the slowest possible solution, but also one that doesn't hold
long-lasting write locks.
As to
very short stored procedure (which should be easy to maintain on all
the platforms)
that seems to me a contradiction. Each database platform seems to have
it's own ideas about syntax and capabilities of stored procedures.
But indeed, they would be very fast.
Apart from the mysql issue you mentioned.
Would it not be a good compromise to do something like this instead of
every row seperately?:
BEGIN;
DELETE FROM table WHERE table.id in (SELECT ... LIMIT 100);
COMMIT;
that way it is not that many queries and also doesn'T put long write
locks - the 100 is just an example but I assume you get what I mean ;)
not sure how you'd go about the mysql problem though without a stored
procedure, or temporary table / view maybe?
Regards
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
