Parallel conflicting statements with db
I am trying to create a site (using html, javascript and jsp) that sends modification and fetch requests to db at the same time. MySQL doesn't seem to like it ( ConcurrectModificationException
everywhere).
I thought about creating something that receives sql statements at the same time and then orders them into a queue based on some property, and then queues them up one by one, after they do not contradict each other (insert statement after deleting the table will contradict).
The problem is that I'm not sure how to check if two operators are in conflict. What I had in mind was 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 occurs, the statement conflicts with another statement. But that means I have to duplicate the table many times and I highly doubt it will work.
So how can I check if two operators are in 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 at the same time and then ordered in some way, then it sql2
can be fired after sql1
and this will throw an exception. How to check the conflict in this example?
source to share
There must be a problem with the mysql try driver updating the mysql driver. Another workaround is to implement table-level synchronization in 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
}
}
}
source to share
MySQL, like all complete database systems, supports many concurrent operations within the normal transactional and blocking constraints. Your best bet is to solve your specific problem by asking a Stack Overflow question.
I think you shouldn't give the students the task of managing the queue etc. The complexity of what you are describing is significant and, more importantly, for which database systems. They should be taught not to reinvent the wheel when they can use something that is much better than they can build. Unless you specifically want to teach such a low-level DB design.
source to share