[RndTbl] hung mysql transaction locks after a php runtime error?
athompso at athompso.net
Fri Jul 31 15:04:03 CDT 2020
I think what you need is ErrorException. See
Basically it's a way to turn regular PHP errors into Exceptions so you
can Catch them, and then you also get to have a Finally block where you
can rollback unconditionally.
The comments on that page are more valuable than the official
documentation, as usual.
On 2020-07-30 02:45, Trevor Cordes wrote:
> I'm doing a lot with mysql innodb transactions in php these days.
> I'm wondering, if I start a transaction and use "FOR UPDATE" to lock a
> bunch of rows (or maybe more critically, the "next auto-insert row"),
> then PHP hits a runtime error (say a call to non-existant function),
> something in php or mysql (or probably more precisely php's mysqli
> library) rollback my transaction?
> Or will my transaction locks stay in place until the next user of that
> php-fpm thread calls "rollback" or "start transaction"? ... possibly
> hanging a subsequent web hit if they need the locked rows! (Yes,
> innodb_lock_wait_timeout in mysql will eventually clear things up, but
> using the default of 50 to be safe, and I don't want an end user UX to
> wait 50s for a page load!)
> I'm using php's default mysqli persistence options, so the connection,
> while not "pooled", does persist across web page hits (for performance
> I'm guessing if I turned off all mysql persistence in php that mysql
> itself would rollback when it senses the lost connection. However, I'm
> not sure how much performance I'll lose if php can't use connection
> I assume this problem would also exist for other db engines and other
> hit-based web languages (with persistence).
> Maybe it's late, but I'm having a hard time coming up with test case
> proves what is going on one way or another. However, I could swear
> I've already hit the problem and had web hits hang until restarting
> In the meantime I'm putting a "rollback" call in my mysql connect
> so after (re)connecting to a persistence connection it'll rollback any
> previous-errored-out-hit leftover garbage. However, with many fpm
> threads/ps's who knows if the errored-out thread will get a new hit
> Roundtable mailing list
> Roundtable at muug.ca
More information about the Roundtable