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

[Dan Keizer]

The database itself maintains the state of the locks - so, unless you've
set up your app to undo your record sets on an exit error, it would wait
until the database engine clears the locks with the LWT.
Otherwise, you could re-architect the design to not allow for that to occur.
Dan.

On Thu, 30 Jul 2020 at 02:46, Trevor Cordes <trevor at tecnopolis.ca> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://muug.ca/pipermail/roundtable/attachments/20200730/38229b1f/attachment.htm>