[RndTbl] hung mysql transaction locks after a php runtime error?

Adam Thompson athompso at athompso.net
Fri Jul 31 15:04:03 CDT 2020


I think what you need is ErrorException.  See 
https://www.php.net/manual/en/class.errorexception.php.

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.

-Adam

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"), 
> and
> then PHP hits a runtime error (say a call to non-existant function), 
> will
> 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 
> I'm
> 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
> reasons).
> 
> 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
> persistence?
> 
> 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 
> that
> proves what is going on one way or another.  However, I could swear 
> that
> I've already hit the problem and had web hits hang until restarting 
> mysql.
> In the meantime I'm putting a "rollback" call in my mysql connect 
> function
> 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 
> right
> away!
> _______________________________________________
> Roundtable mailing list
> Roundtable at muug.ca
> https://muug.ca/mailman/listinfo/roundtable


More information about the Roundtable mailing list