I would strongly advise you test the patch against SA 3.4.0

I suggest reporter reading through discussion the in bug report that the referred blogger already opened years ago.

Closing as duplicate, I suggest continuing in the old bug if it still seems to be a problem and not some local configuration issue / missing index or whatever.

*** This bug has been marked as a duplicate of bug 6444 ***

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.

(In reply to peter gervai from comment #3)

> 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. 

sidenote: Redis Bayes backend would give you way less problems than SQL and a huge performance advantage. (site wide bayes only)

(In reply to AXB from comment #4)

> sidenote: Redis Bayes backend would give you way less problems than SQL and
> a huge performance advantage. (site wide bayes only)

Indeed redis is interesting despite its several shortcomings, including a serious FD leak (Bug 7034) I had to play with this morning. Seems to be fixed now so doesn't die in every few hours. 

Still have to use SQL for AWL and probably I have to ditch redis when I move over per-user-configs (unless mr. author prefix the keys with userid and solve the problem, don't ask me why he haven't).

This doesn't quite solve the original problem though: for a massively parallel updating code ignoring locking is a nonworking solution in the long run. Now redis saves me to SELECT FOR UPDATE'ing all the code to rowlock everything beforehand and release after, which ought to have been done.

(In reply to peter gervai from comment #5)
> and probably I have to ditch redis when I move
> over per-user-configs (unless mr. author prefix the keys with userid and
> solve the problem, don't ask me why he haven't).

Probably because you do not want n^2 users each using 1GB of memory for tokens?

(In reply to Henrik Krohns from comment #6)
> (In reply to peter gervai from comment #5)
> > and probably I have to ditch redis when I move
> > over per-user-configs (unless mr. author prefix the keys with userid and
> > solve the problem, don't ask me why he haven't).
> 
> Probably because you do not want n^2 users each using 1GB of memory for
> tokens?

Well even if you have n^2 users then you probably don't have much choice apart from storing the data you need. 

I'm not familiar with redis, so this is only educated guessing: if you believe you'll have more tokens than users (which seems to be a good assumption) I guess the way to go could be:

HMSET w:token1 user:bob:spam 55432 user:bob:ham 23
HMSET w:token1 u:joe:s 22 u:joe:h 8171091
HMSET w:token1 u:henrik:s 86373 u:henrik:h 832

But I guess this will _not_ help fixing psql locking problems, only avoiding it. :-(

Looking back at Bug 6444, it does seem a bit silly that a potentially deadlock solving function was canned for a mere "20 ms" performance penalty. Surely having deadlocks is much worse. Should test if "tok_touch_all_6444" works these days.

*** Bug 5999 has been marked as a duplicate of this bug. ***

All the errors are hidden since they are logged with dbg(), sigh.. tried looking into things a bit, but the SQL stuff is just a mess of a mess. Deadlocks here and there. Not going to waste my time, just use a global Redis and be done with it.

Postponing since probably no one will rework these for 4.0.