This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Transaction Design Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Transaction Design" Watch "Transaction Design" New topic

Transaction Design

Kris Reid
Ranch Hand

Joined: Jan 05, 2005
Posts: 247

I'm building a game 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.

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
Ranch Hand

Joined: Jan 05, 2005
Posts: 247
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
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
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
Ranch Hand

Joined: Jan 05, 2005
Posts: 247
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
Ranch Hand

Joined: Jan 05, 2005
Posts: 247
Never event crossed my mine to do the calculations in SQL. Test show its at least 500% faster!
I agree. Here's the link:
subject: Transaction Design
Similar Threads
updating db after lengthy paypal transaction - transactional integrity
Usage of XA DataSource while working with single resource.
Hibernate error when loading JSP
DAO transactions
EJB Transaction Rollback with JBDC