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!
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@muug.ca https://muug.ca/mailman/listinfo/roundtable
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@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@muug.ca https://muug.ca/mailman/listinfo/roundtable
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@muug.ca https://muug.ca/mailman/listinfo/roundtable
On 2020-07-31 Adam Thompson wrote:
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.
Yes, something like that would be ideal. I was glancing at it as a possible solution before, but the bit about not being able to catch some errors, like E_ERROR or E_PARSE, mean it likely won't help. Maybe I'll do a test run to see exactly what error is produced by things like calling an invalid fn, or calling with the wrong # of args.
Luckily programmer-stupidity mistakes like that are very rare in my production code :-) However, they are common on my dev box. In the end, if the mysql innodb lock timeout really works as I think it does (rolling back that thread after 50s) then the repercussions aren't as bad as I had feared. A locked-out user for 50s is possibly acceptable. Locking all users out for 50s is not.
And I have that rollback to start every hit as Scott suggested, so that should help too.
What I really require is something in the php-fpm engine that lets me trigger a script to run after every cgi hit is done processing (whether successful or crashed). In there I could put a connect/rollback.
Does anyone have any guess what sort of performance hit my site would take if I turned off all of PHP's mysql connection persistence? Does starting a new connection for each hit really take that many cycles?
On 2020-07-31 23:24, Trevor Cordes wrote:
Does anyone have any guess what sort of performance hit my site would take if I turned off all of PHP's mysql connection persistence? Does starting a new connection for each hit really take that many cycles?
AFAIK, if you're using local (AF_UNIX) socket connections, MySQL still has the lowest connection "cost" of any major database. Which isn't trivial, but should be low enough you can almost ignore it. Connections over AF_INET (even to 127.0.0.1) are much more expensive, but still pretty cheap. It'll become a problem at scale, I think, if you're establishing hundreds of new connections per second. Not 100% sure. -Adam
On 2020-08-01 Adam Thompson wrote:
AFAIK, if you're using local (AF_UNIX) socket connections, MySQL still has the lowest connection "cost" of any major database. Which isn't trivial, but should be low enough you can almost ignore it. Connections over AF_INET (even to 127.0.0.1) are much more expensive, but still pretty cheap. It'll become a problem at scale, I think, if you're establishing hundreds of new connections per second. Not 100% sure. -Adam
Good idea. I checked and the ini's are set to blank (default) and my php code specifies it in the connect as "localhost". However, I can't definitively find if that triggers php-mysqli to use UNIX or INET... because the examples to connect to a UNIX socket are localhost:/path/sock
However... it wouldn't be hard for me to just explicitly make it the sock on all my deployments, as it's a deploy-level var in my code.
We can (and in the future certainly plan to) get 100's of hits a second. I think for now my plan will be to deploy with defaults and persistence and cross my fingers and check for the email it'll send me if some lock ever hangs for the 50s...
I'm also curious to see how many real-life transaction deadlocks occur with all my new transaction usage. Are deadlocks endemic or uber-rare? (Assuming non-pathological code.)
Thanks for all the help, MUUGers
-
Adam Thompson -
Dan Keizer -
Scott Toderash -
Trevor Cordes