deadlock errors when IdP under load

[Cantor, Scott]

> All IdP instances are reporting "Transaction (Process ID 165) was deadlocked
> on lock resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction.". These errors result in failed authentications
> ("Process ID X" values do differ, it is not always 165).

There is really no way that a correctly functioning database can deadlock on that code, the queries it does are very simple and they just ask that locking be done properly in light of the isolation level it requests, which basically means "lock the whole table on a select", because that's the only way to prevent it from non-repeatable reads that would create insert collisions that the code assumes can't happen.

All modern databases appear to have decided to abdicate support for proper locking, from what I can tell, so I doubt it will ever work reliably again under any kind of load.
 
We do have the retry logic available to catch errors and force it to attempt the operations again. That's the only workaround.

I used to do queries much more complex than this under at least roughly similar loads and never had a problem, but that was in 1995-2000. Apparently that's no longer possible without applications essentially taking on all of the locking responsibility, at which point we might as well use DBASE or Clipper.

-- Scott