MySQL transactions/locks



  • Continuing the discussion from MS SQL Permissions:

    @Matches said:

    Php mutex lock

    New thread to quit crapping on the MS-SQL one.

    I could use some pointers on how to prevent database inconsistencies in my MUD. If you haven't been following, it's written in PHP/MySQL (mysqli) and is primarily a project for me to learn those platforms because I've never done either before. No idea if it will be completed or deployed, just a side project for fun.

    Anyway, I need to do something with transactions or table/row locking to prevent inconsistency, especially with the inventory system. Worst-case scenario is something like a room with 100 gold coins, player 1 and player 2 are there, and both of them by dumb luck manage to get the "pick up 100 gold coins" command to process at the same time. There's a strong chance both will succeed despite that not being logically possible.

    Another scenario is two players manage to get the command "attack goblin" to process at the same time. I imagine that would be very bad!

    I'm nearly a complete newb with PHP/MySQL. Thoughts? Suggestions? Links? Ideally if either of those scenarios occur, one of the players would be blocked for the few milliseconds it takes for the other player to process. No idea if transactions/locking/etc can do something like that.



  • Php mutex for php, this link for mysql, pdo and mysqli examples

    You should also be abstracting your room away from players so they issue commands but the server manages the queue and locks.



  • I think I have a muddy idea of what to do based on that link and on this

    http://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html

    @Matches said:

    You should also be abstracting your room away from players so they issue commands but the server manages the queue and locks.

    A "real" MUD would have a central process dealing with main execution and clients simply issue commands to that process. But my PHP architecture doesn't have a central process and each page load does a bunch of updates for the player's current state and the state of their current room. Perhaps using PHP is TRWTF here but I'm not going to significantly change anything.


  • :belt_onion:

    psh just do it the Dicsourse way and blindly issue the two commands to the db at the same time and let it have a race to see who wins, or possibly both win for double-like bonus. More bonuses for random trickle-down errors for the person whose command fails.



  • @darkmatter said:

    psh just do it the Dicsourse way and blindly issue the two commands to the db at the same time and let it have a race to see who wins, or possibly both win for double-like bonus. More bonuses for random trickle-down errors for the person whose command fails.

    That's basically the current system, but unlike Discourse I intend to improve the situation.


  • :belt_onion:

    Worthless! Did you learn nothing from the Race Condition as PRNGtopic?

    Don't tell me you're going to take your chances with the PHP super_real_rand_this_time_i_promise() function.



  • Generally item duping is the leading cause of MUD wipes.



  • Why not go all out and use the pcntrl functions to fork processes and really cut loose?



  • The solution is obviously to have the server join the game as a $DIETY, and it knows all, sees all, records all.

    Call it a Balanx



  • Surely it would be called Codethulhu?



  • Balanx are the little sub god things that do work for the big god, so I guess Codethulhu could be the mastermind.


  • ♿ (Parody)

    I don't see that anyone has suggested optimistic locking:

    Obviously, you have to figure out how to deal with conflicts, but you shouldn't get DB deadlocks.

    The "pick up gold" race condition turns into something like, "someone else already grabbed it."



  • My first thought is, "why aren't you using PDO?"



  • @mott555 said:

    A "real" MUD would have a central process dealing with main execution and clients simply issue commands to that process

    Yeah in the other thread about his MUD, I pointed out that his "one file per command" approach was gonna cause issues. This is one of those issues he now has to solve.

    That said, if you can lock the "atomic" operation in a plain ol' transaction, that should take care of the issues. The DB then becomes the arbitrator of "who was first", instead of anything in your code. But you need to be careful to handle transaction failures in your code.

    EDIT: the other catch here is the "atomic" operation could be an awful lot of SQL. Picking up 100 gold is a pretty easy case, but what about starting combat? That might involve 20 queries or more-- you might consider putting these operations in a stored procedure, then you could put the entire sproc in the transaction. Managing that in code, especially PHP, might be a PITA.



  • @blakeyrat said:

    My first thought is, "why aren't you using PDO?"

    I looked into it a while ago and didn't see any significant difference between PDO and mysqli. And the very first "PDO vs mysqli" guide I found was wrong and claimed mysqli doesn't support prepared statements.

    @blakeyrat said:

    Yeah in the other thread about his MUD, I pointed out that his "one file per command" approach was gonna cause issues. This is one of those issues he now has to solve.

    Erm...it's not "one file per command", not sure where that came from or what that has to do with anything.



  • @mott555 said:

    I looked into it a while ago and didn't see any significant difference between PDO and mysqli. And the very first "PDO vs mysqli" guide I found was wrong and claimed mysqli doesn't support prepared statements.

    If you use PDO you can trivially switch to a non-shitty database later. If you use mysqli, you're stuck with MySQL and it's bullshit until the end of time. And MySQL is a "shutdown risk" now that Oracle owns it. (I think there's already a fork, but, my point is at this point in time it's a bad idea to wager that MySQL will be around in 5 years.)



  • @mott555 said:

    Erm...it's not "one file per command",

    It was a couple months ago in the Lounge. But if you've already moved away from that, good.



  • @blakeyrat said:

    It was a couple months ago in the Lounge. But if you've already moved away from that, good.

    It was never that.

    @blakeyrat said:

    If you use PDO you can trivially switch to a non-shitty database later. If you use mysqli, you're stuck with MySQL and it's bullshit until the end of time. And MySQL is a "shutdown risk" now that Oracle owns it. (I think there's already a fork, but, my point is at this point in time it's a bad idea to wager that MySQL will be around in 5 years.)

    Point taken, but part of the purpose of this project was to learn MySQL since I've only ever dealt with MS-SQL.



  • @mott555 said:

    It was never that.

    I concede not because I am wrong, but because Discourse makes it impossible to find anything.



  • @blakeyrat said:

    That said, if you can lock the "atomic" operation in a plain ol' transaction, that should take care of the issues. The DB then becomes the arbitrator of "who was first", instead of anything in your code. But you need to be careful to handle transaction failures in your code.

    @blakeyrat said:

    EDIT: the other catch here is the "atomic" operation could be an awful lot of SQL. Picking up 100 gold is a pretty easy case, but what about starting combat? That might involve 20 queries or more-- you might consider putting these operations in a stored procedure, then you could put the entire sproc in the transaction. Managing that in code, especially PHP, might be a PITA.

    This is what I'm going to try for. Most stuff doesn't need locks or transactions, and the stuff that does is relatively short. When someone is picking stuff up I'll just lock the RoomItem table for the 2 - 3 DB commands that are issued (may or may not include inserts/deletes depending on situation).

    As for starting combat, that will be a "rare" enough occurrence that I don't think it needs to be too optimized, especially since the chances of multiple players trying to start combat with the same entity during the tiny window required to cause DB inconsistencies will be very low.



  • @mott555 said:

    Point taken, but part of the purpose of this project was to learn MySQL since I've only ever dealt with MS-SQL.

    Ok but mysqli isn't "learning MySQL", it's "learning one particular API for MySQL." And if you're interesting in the latter case, fine. But I'd prefer, "learning one particular API that works for every database engine ever in history ever."



  • And a wild Markdown quoting bug appears from nowhere!



  • @mott555 said:

    This is what I'm going to try for. Most stuff doesn't need locks or transactions, and the stuff that does is relatively short. When someone is picking stuff up I'll just lock the RoomItem table for the 2 - 3 DB commands that are issued (may or may not include inserts/deletes depending on situation).

    Don't manually lock shit. Use a transaction and let the DB worry about the locking.



  • @blakeyrat said:

    Don't manually lock shit. Use a transaction and let the DB worry about the locking.

    If I can better understand how PHP deals with MySQL transactions that may be a solution. But I've had past projects using ASP.NET and T-SQL where transactions would result in error pages presented to some users because data was modified during a transaction attempt. What I want is blocking behavior, i.e. if a transaction is in progress any parallel transactions of the same type should block/wait until the first one completes.



  • @mott555 said:

    If I can better understand how PHP deals with MySQL transactions that may be a solution. But I've had past projects using ASP.NET and T-SQL where transactions would result in error pages presented to some users because data was modified during a transaction attempt.

    Right; that's why you need to expect and correctly handle transaction rollbacks. This becomes a bit tricky and hard to test if they're super-rare.

    @mott555 said:

    What I want is blocking behavior, i.e. if a transaction is in progress any parallel transactions of the same type should block/wait until the first one completes.

    Well ok but put a gigantic message next to it that says, "THIS ONLY WORKS WHEN WE HAVE LESS THAN 50 USERS, THIS IS THE GODDAMNED FIRST THING WE GOTTA REFACTOR IF THIS SHIT EVER GETS POPULAR BECAUSE DAMN".

    If you add that message, then go for it.



  • I'm willing to accept locks may not be the right way...part of the reason I'm asking for advice here. Doing my research to see what the options are and what may work the best.



  • I'll take that bet.

    What's it worth to you, Blakey? And how shall we define 'Around'? Because I can pretty much guarantee, even if Oracle shuts down mysql, it will be around for at LEAST another 10-15 years.



  • @mott555 said:

    Point taken, but part of the purpose of this project was to learn MySQL since I've only ever dealt with MS-SQL.

    If all you want to do is learn another database, for fuck's sake, learn something sensible. MySQL is a fucking abortion, go learn something competent and learn to hate MySQL's edge cases and data loss scenarios when you're forced to.



  • @blakeyrat said:

    Use a transaction and let the DB worry about the locking.

    With MySQL? Jesus, man, what the fuck are you smoking?



  • < snip anger >

    Try something more reasonable, like Postgres.



  • @tufty said:

    If all you want to do is learn another database, for fuck's sake, learn something sensible. MySQL is a fucking abortion, go learn something competent and learn to hate MySQL's edge cases and data loss scenarios when you're forced to.

    Now I know how my coworkers feel when I suggest we throw away their flagship Qt/C++ application and rewrite it in the much more sensible C# and WPF.



  • I'm really having trouble wrapping my brain around transactions, and about all I can find with MySQL is "Transactions are good, m'kay? Use them." But no information on what happens during concurrent transactions.

    Player 1 and Player 2 are in the same room. There are 50 gold coins on the ground. Both players pick up 30 coins at the same time, meaning concurrent transactions.

    Algorithm:

    1. Search room inventory for the item name. This loads the RoomItem object from DB and into PHP.
    2. Confirm that the player's get amount is less than or equal to room count, cap to room count if needed.
    3. Update room count by subtracting player's get count from the pile. Save RoomItem back to database.
    4. Update player inventory by incrementing player's count if they already have the item, or insert new row into player inventory if they didn't already have that type of item.
    5. Send messages/notifications as needed.

    What happens in my scenario if #3 happens to execute at the same time for both players even if the entire thing is wrapped in a transaction? #1 has already happened, both think there are 50 coins so #2 passes for both. Player 1 takes 30 coins, leaving 20 behind, then saves to the database at the same time as Player 2.

    Does the transaction realize that the pertinent rows were modified by a different transaction during its execution and thus fail to commit?



  • Nothing happens in a transaction until you commit the transaction. At that point it tries to apply all the changes and if something couldn't be done, it falls back to you to let you decide whether to rollback or what you want to do about it.

    In your case, whichever gets committed first should be causing the other to fail.



  • @Arantor said:

    Nothing happens in a transaction until you commit the transaction. At that point it tries to apply all the changes and if something couldn't be done, it falls back to you to let you decide whether to rollback or what you want to do about it.

    In your case, whichever gets committed first should be causing the other to fail.

    And that makes sense, unlike everything I was just reading on StackOverflow. (Lots of top-rated answers actually said "Don't use transactions, instead add some kind of 'IsCheckedOut' field and have your code look at that to see if another thread/instance is modifying that row." :doh:)

    We briefly touched on transactions in college but it's been years and somehow I've yet to encounter a significant need for them in the Real World, at least for the applications I've had to write.



  • @mott555 said:

    Search room inventory for the item name. This loads the RoomItem object from DB and into PHP.
    Confirm that the player's get amount is less than or equal to room count, cap to room count if needed.
    Update room count by subtracting player's get count from the pile. Save RoomItem back to database.
    Update player inventory by incrementing player's count if they already have the item, or insert new row into player inventory if they didn't already have that type of item.
    Send messages/notifications as needed.

    If this is wrapped in a transaction, the database guarantees it'll happen atomically.

    @mott555 said:

    What happens in my scenario if #3 happens to execute at the same time for both players even if the entire thing is wrapped in a transaction?

    One of the two transactions will fail, and you will have to roll it back and report the failure back to your calling code so you can handle it intelligently.

    You will not get into a state where steps #1 and #2 happened but #3 didn't. If you rollback the transaction, it all is rolled-back, keeping the series of operations atomic.

    @mott555 said:

    Does the transaction realize that the pertinent rows were modified by a different transaction during its execution and thus fail to commit?

    Exactly.



  • @Arantor said:

    and if something couldn't be done

    Now I'm confused again. What determines this? Back to my scenario, player 1's transaction goes through and there are 20 coins left. Player 2's transaction begins, but it's also set up to update the room inventory and set to 20. Will basic row updates that don't violate any constraints conflict with each other and cause a commit failure?

    Or perhaps I need to reexamine how I'm dealing with inventory.



  • @mott555 said:

    (Lots of top-rated answers actually said "Don't use transactions, instead add some kind of 'IsCheckedOut' field and have your code look at that to see if another thread/instance is modifying that row." :doh:)

    Wow. StackOverflow is awful, but that's above and beyond awful.



  • @mott555 said:

    We briefly touched on transactions in college but it's been years and somehow I've yet to encounter a significant need for them in the Real World, at least for the applications I've had to write.

    ... I find this unlikely. I think it's more likely that your applications have subtle race conditions, but the load on your database isn't high enough to expose them.



  • @blakeyrat said:

    Wow. StackOverflow is awful, but that's above and beyond awful.

    I head-desked when I saw those responses. StackOverflow won't let me post questions because I don't have enough reputation, yet somehow the most mentally-deficient programmers in the universe are able to get enough reputation to post advice that is The Worst Of The Worst.


  • Discourse touched me in a no-no place

    @blakeyrat said:

    StackOverflow is awful, but that's above and beyond awful.

    Yeah, as solutions go, that's horrendous.
    Less solution, more moving the problem.



  • @blakeyrat said:

    but the load on your database isn't high enough to expose them.

    Key point here. At my last job the stuff I wrote was largely read-only, to the tune of hundreds of users. But there were only 2 - 4 users who would change data and they never worked on the same stuff at the same time.

    And my current job doesn't even involve databases. Which sucks because I like databases.



  • @mott555 said:

    Now I'm confused again. What determines this? Back to my scenario, player 1's transaction goes through and there are 20 coins left. Player 2's transaction begins, but it's also set up to update the room inventory and set to 20. Will basic row updates that don't violate any constraints conflict with each other and cause a commit failure?

    You start the transaction before you query the amount of coins in the room. Ensure the ENTIRE atomic operation of "player takes coins" is inside the transaction.

    The key is to define, "what is the atomic operation here?" In your case, the entire 5-step process you outlined is one single atomic operation. Ensure the entire operation is within the transaction.

    EDIT: and keep in mind, you can rollback your transaction due to a code or a query failure. So if you're checking the amount of coins in PHP, you can still have PHP rollback the transaction even though no SQL queries failed.



  • @blakeyrat said:

    You start the transaction before you query the amount of coins in the room. ensure the ENTIRE atomic operation of "player takes coins" is inside the transaction.

    Okay, that's what was apparently confusing me. For some reason it wasn't registering that SELECTs count as part of a transaction.

    Which raises another question...if I have code that has several SQL commands all part of the same transaction, but with various PHP statements between them, does that actually end up blocking PHP code somehow?

    1. Player 1's transaction begins.
    2. Player 2 tries to start a transaction and queries inventory count, then does a couple PHP things before moving on. Is this blocked until the first transaction finishes or aborts? Or does the query succeed, meaning the count will be wrong when the first transaction commits?

    I really get the feeling I'm over-complicating and over-thinking things.



  • @mott555 said:

    Which raises another question...if I have code that has several SQL commands all part of the same transaction, but with various PHP statements between them, does that actually end up blocking PHP code somehow?

    I'm no PHP expert, but I can't imagine how it would. I assume PHP is opening a new DB connection for each PHP script. (Hopefully, it's using a connection pool, too.)



  • PHP invariably opens a new DB connection for the length of the pool. Using a connection pool is optional and most shit hosts don't like it.



  • @blakeyrat said:

    mott555 said:
    Does the transaction realize that the pertinent rows were modified by a different transaction during its execution and thus fail to commit?

    Exactly.

    I think I missed this part when it was first posted.


  • Discourse touched me in a no-no place

    @Arantor said:

    Using a connection pool is optional and most shit hosts don't like it.

    Probably should avoid hosts who are against connection pools.



  • The good news is the host is an Ubuntu Server VM I'm running myself and I can do whatever I want.



  • Putting in transactions in certain areas now. If the commit fails I'm simply sending the player a message saying "Transaction failure" for now. Not very informative but specific enough for me to determine how often such things actually happen. If it's more than once in a blue moon I can do additional handling, most likely a retry mechanism which will either succeed or generate a more specific error message like "There are no gold coins here."



  • OK, so...

    • you need events to happen atomically in some order
    • you're running on a standard webserver, meaning different requests are isolated and have poor ways of communicating with each other or with the "application" on the whole
    • so you decide you will use database as sort of central arbitrator that will decide the order of events and the correct state of the world

    IMO this is unworkable long term. Transactions can work in the simple case stated here, but as the game grows you'll need more and more functionality that will need to be synchronized the same way as coins. MySQL will become the core application, with PHP relegated to the role of middleware. Except, DB is not designed to work like that. Eventually, you'll come up with a problem SQL can't handle.

    IMO this kind of game is a poor fit for basically stateless web-server hosted environment like PHP. You should have just made a blog. On the upside, dealing with shit like this, you'll get much deeper into the way the platform works than you would otherwise, so there's that...


Log in to reply