<div dir="ltr"><div>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.</div><div>Otherwise, you could re-architect the design to not allow for that to occur.</div><div>Dan.<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, 30 Jul 2020 at 02:46, Trevor Cordes <<a href="mailto:trevor@tecnopolis.ca">trevor@tecnopolis.ca</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">I'm doing a lot with mysql innodb transactions in php these days.<br>
<br>
I'm wondering, if I start a transaction and use "FOR UPDATE" to lock a<br>
bunch of rows (or maybe more critically, the "next auto-insert row"), and<br>
then PHP hits a runtime error (say a call to non-existant function), will<br>
something in php or mysql (or probably more precisely php's mysqli<br>
library) rollback my transaction?<br>
<br>
Or will my transaction locks stay in place until the next user of that<br>
php-fpm thread calls "rollback" or "start transaction"? ... possibly<br>
hanging a subsequent web hit if they need the locked rows! (Yes,<br>
innodb_lock_wait_timeout in mysql will eventually clear things up, but I'm<br>
using the default of 50 to be safe, and I don't want an end user UX to<br>
wait 50s for a page load!)<br>
<br>
I'm using php's default mysqli persistence options, so the connection,<br>
while not "pooled", does persist across web page hits (for performance<br>
reasons).<br>
<br>
I'm guessing if I turned off all mysql persistence in php that mysql<br>
itself would rollback when it senses the lost connection. However, I'm<br>
not sure how much performance I'll lose if php can't use connection<br>
persistence?<br>
<br>
I assume this problem would also exist for other db engines and other<br>
hit-based web languages (with persistence).<br>
<br>
Maybe it's late, but I'm having a hard time coming up with test case that<br>
proves what is going on one way or another. However, I could swear that<br>
I've already hit the problem and had web hits hang until restarting mysql.<br>
In the meantime I'm putting a "rollback" call in my mysql connect function<br>
so after (re)connecting to a persistence connection it'll rollback any<br>
previous-errored-out-hit leftover garbage. However, with many fpm<br>
threads/ps's who knows if the errored-out thread will get a new hit right<br>
away!<br>
_______________________________________________<br>
Roundtable mailing list<br>
<a href="mailto:Roundtable@muug.ca" target="_blank">Roundtable@muug.ca</a><br>
<a href="https://muug.ca/mailman/listinfo/roundtable" rel="noreferrer" target="_blank">https://muug.ca/mailman/listinfo/roundtable</a><br>
</blockquote></div>