Apache::DBI has done all the hard work for sharing a database handle, but for reasons I won't go into here, I could not use it.
So I did my own sharing, until I kept getting a:
- "MySQL Lock wait timeout exceeded"
Took a bit of effort between me and a colleague to work it out. A user was entering a duplicate record, and our code correctly threw an exception, which was correctly displayed to the user as a popup in their browser of the failed AJAX request.
However, then we exit, leaving the DB handle to be used by the next client.
The same user goes in again, hitting a different apache instance, and different DB handle and try to modify the same rows.
The old handle had not had a rollback, so still had record locks.
- The data class should not have allowed the duplicate entries
- The data class should have rolled back on error
- The database pool should have cleaned up the database on return
The last one is done by Apache::DBI by doing a rollback on child clean up if Auto Commit is off.