Transaction Design

[Kris Reid]

Hi

I'm building a game www.mobwarrior.com and am having a wee problem with transactions and I'm not sure about the best way to do it.

I've got a session filter on the servlets which starts a transaction and comits it at the end of a user action which works fine.
I also have a back ground process that every 10 minutes grabs all the users, calculates their scores and ranks them. Sometimes this process is not able to commit the transaction.

   
AbstractFlushingEventListener:301 - Could not synchronize database state with session
org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update
 

I'm guessing this is because the user has logged in and done something as it was updating all the users rank.
I'm not sure how else to do this with out starting and commiting a heap of transcations. The other way I was thinking is starting transaction, calc user score, commit, repeat for all users.
Then start transaction, rank all users, commit.
Is this the best way to do it? Are starting that many transactions expensive?

Any ideas would be great, Thanks

[Kris Reid]

I redesigned the process to wrap a transaction around each users calculation and then another transaction to rank the users.

This has doubled the time it takes to complete. And it'll just keep taking longer.
Any ideas would be greatly appreciated

[John Kimball]

If you can, you should calculate a user's score each time they finish a game and ideally, let the database perform the calculation; i.e., a pure SQL solution.

But since things can fail, you should have your background process look for users whose calculated score is older than when they last logged-in. The obvious way is to have two timestamps: One for the login (or last-played) and another that says when the user's score was last calculated. It's also going be an inefficient query, so you'll want to limit the last-played search to the last day, the last hour, etc.

You'll have to think this through carefully and consider the different ways this can fail and/or lead to bad results (and there are plenty), but I think it's worth the effort unless somebody offers a cleaner approach.

Not that I've been thinking hard about this (slave to TV) but I don't see an obvious & generic way of assigning a ranking # efficiently, since it's relative to other users' scores.

[Kris Reid]

Cheers mate!

I've changed it to calc the users score every time they finish an action and just do the ranking every 10 minutes and seems to be working a lot fast.
I'm not much of an SQL guy but I'll try and figure out how to do the calculation in SQL and see how that goes too

[Kris Reid]

Never event crossed my mine to do the calculations in SQL. Test show its at least 500% faster!
cheers