CODEWITHSUNDEEP
javadatabasejdbcconcurrency
ItamarG3
ItamarG3

Reputation: 4122

Concurrent Conflicting statements with db

I am attempting to make a website (using html, javascript and jsp) that sends modification and selection queries to a db at the same time. MySQL apparently doesn't like that (ConcurrectModificationExceptions everywhere).

I thought of creating something that receives sql statements concurrently and then orders them into a queue based on some property, then execute the queue one by one, after making sure that they don't contradict each other(an insert statement after on that deletes a table would contradict).

The problem is that I'm not sure how to check if two statements conflict. What I had in mind is checking what the tables would theoretically look like if the statements were executed (by running them on a duplicated table) and then if an error is thrown, a statement conflicts with another statement. But this means I have to duplicate the table many times, and I highly doubt it would work.

So, How can I check if two statements conflict?

For example:

String sql1 = "DELETE FROM users WHERE id=3625036";
String sql2 = "UPDATE users SET displayName=\\"FOO\\" WHERE id=3625036";

If these two are received concurrently and then ordered in some way, then sql2 might be executed after sql1 and that would throw an exception. How can I check for conflict in the given example?

Upvotes: 3

Views: 132

Answers (2)

jithin joseph
jithin joseph

Reputation: 176

It should be a driver issue of the mysql try updating the mysql driver. Another workaround is to implement the table level synchronization at your code.

example:

class UserDAO{

public void upateUsers(String sql){
   synchronized(UserDAO.class){
    // do update operations
   }
}

public void deleteUser(String sql){
   synchronized(UserDAO.class){
    // do delete operations
   }
}

}

Upvotes: 1

Rory
Rory

Reputation: 41917

MySQL like all full DB systems supports lots of concurrent operations, within normal transactional and locking restrictions. You're best off solving your particular problem by asking a question on stack overflow.

I think you shouldn't set students the task of managing queueing etc. The complexity of what you're describing is significant, and more importantly that's what database systems are for. They should be taught not to reinvent the wheel when they can make use of something that's far better than they can build. Unless you're specifically wanting to teach such low-level DB construction.

Upvotes: 3

Related Questions