Evil Trout Posted:
Forumwarz is a pretty complicated application as far as web apps go.
There are about 125 tables in the database. Now not all of them are used for storing character data, but there are at least 20-30 that do. Every time a character is reset we have to begin a transaction, lock those 25 tables and delete most of the rows, then update data to their initial levels.
In the meantime, those 20-30 tables are being hit by people playing the game, wanting to insert their gained cred or whatever, which causes the deadlocks. If we weren’t serving up multiple requests every second it might not be such an issue (in fact on the development server it’s never a problem), but it’s not a simple operation Log in to see images!
Again, I’m not totally sure, but wouldn’t the easier thing to do be have each character saved as its own table, which is then part of a bigger table?
I mean, like I said, I’m unfamiliar with web based stuff for the most part, but if I were designing something like this in java, I’d have an object representing a character, with all the needed info, then a object that stores all of the characters. As I understand it that would allow for each character to reset its values individually without interfering with the others.
Tables for databases might not work the same way, but I wouldn’t think that the storage would be that much different that it would cause problems. Though it might be a less efficient use of space, which could be your bigger priority until you get better servers.