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

[Scott Toderash]

You could put a rollback statement at the start of your code. With 
connection pooling, I'm not sure how else you could ensure you don't 
have a transaction already started. If the pool manager is not doing it, 
then I don't see any other way.




On 2020-07-30 2:45 a.m., 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