https://issues.apache.org/SpamAssassin/show_bug.cgi?id=7014
peter gervai <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|RESOLVED |REOPENED CC| |[email protected] Resolution|DUPLICATE |--- --- Comment #3 from peter gervai <[email protected]> --- Okay, I have commented in bug 6444 but let me reopen this and share the reasoning. Bug 6444 is about tok_touch_all; my problem is on a much wider scale. I am new to the SQL modules and I wanted to use it because the data must be shared between several pretty busy servers, think of 200+ parallel connections on the same table. I see deadlocks every second. I am fiddling with it for the last few days now, trying various db settings (like changing the deadlock detection time), trying to move functionality in and out of a given transaction, and while I'm no SQL wizard I may guess some problems. If I'm right nobody really thought about locking. Automatic locking, explicit locking and transactions.For example I was completely unable to expire bayes tokens due to immediate deadlock; as it turned out most of the functions hold extremely long transations which touch plenty of tuples in bayes_tokens (holding sharelocks and row exlusive locks) then trying to fiddle with bayes_vars (requiring the same, usually with the same userid as other processes) while holding the sharelocks and deadlocking with token updates (require share and row exclusive locks as well). I was able to expire when I have changed the code to release the locks held on bayes_tokens in token_expiration after the first update (which creates locks through most of the recent parts of the bayes_tokens table) by doing a commit. Probably most of the code does the same: hold wide locks on tokens and waits for vars, others hold locks on vars and wait for tokens, others hold row locks on some tokens then wait for row locks on others, in parallel, keeping a long transaction. For example there's put_tokens SQL statement which deadlocked every second due to holding token lock and waiting for vars lock, and I have resolved it by changing to separate updates releasing their respective locks; it possibly hurts performance but I'm under time pressure to test, volunters welcome. I'm not yet ready to figure out the best solution; surely it involves commits (lock release) where it's acceptable and explicit locking (prelocking) otherwise, most probably by SELECT FOR UPDATE statements. As I mentioned in 6444 it requires time for me since I'm not that familiar with the code. I'm doing it against v3.4.0-1 debian package. -- You are receiving this mail because: You are the assignee for the bug.
