Gregory Stark wrote:
>
> The insert is deadlocking against the update delete.
>
> The problem is that the insert has to lock the records to be sure they aren't
> deleted. This prevents the update for updating them. But the update has
> already updated some other records which the insert hasn't referred to yet.
> When the insert tries to insert a record referring to those it can't lock them
> before they're already locked by the update and you have a deadlock.
>
> Do you really need the update at all? Do you use the last_seen field for
> anything other than diagnostics?
>
> You could try breaking the update up into separate transactions instead of a
> single batch statement. That would perform poorly but never deadlock.
>
> You could try to order them both but I don't know if that's possible. UPDATE
> doesn't take an ORDER BY clause. I suppose you could execute the update
> statement as separate queries within a single transaction in whatever order
> you want which would avoid the performance issue of issuing hundreds of
> transactions while allowing you to control the order.
>
The last_seen is a purge control -- when last_seen < current_date - ?? then I
remove the record.
I think there are two ways I could do this without killing performance. Please
let me know what you think...
I could modify the update to something more like:
update tokens set last_seen = now() where token_idx in (...)
and last_seen < current_date
or even push it back multiple days.
There's always the risk of losing a few records, but I'm probably not going to
notice. (Not bank transactions)
The other approach would be to use an external file to queue these updates and
run them from a crontab. Something like:
open (my $fh, ">> /var/spool/last_seen");
flock($fh, LOCK_EX);
seek($fh, 0, 2)
print join("\n", @$tokens),"\n";
flock($fh, LOCK_UN);
close $fh
and then run a job daily to read all these in to a hash (to make them unique
values) and then run one SQL statement at the end of the day.
Is there a limit to the number of values you can have in an IN(...) statement?