I have a list of clients and they are listed in a nice GUI. When you double click the name you can access and see all information related to them. You can also update some of the information and add new ones.
The system is on a network � so everyone can access the list.
Now, consider this scenario: user A double clicks on JAMES and change his email address AT THE SAME TIME, user B double clicks on JAMES and changes his cell phone. The result is dire! Because either user�s A or user B�s info will not be updated.
Is there any mechanism in mySQL to prevent such a thing??? What I did now is adding a field called LOCK and if someone double clicks on JAMES he locks the record; when exists he releases the lock.
Is there a better way to do this???
Thanks for any thoughts
Peter [ April 07, 2005: Message edited by: Bear Bibeault ]
Peter, One way to deal with this is to add a "last updated column". When the user wants to make a change, you check the value of this column against what the user passes. If they are the same, make the update and change the timestamp. If not, someone else made a change and you should try again.
Peter, Any semi-decent DBMS will know how to handle a situation where multiple users are simultaneously attempting to modify the same data. That's one of the advantages of using a DBMS -- you don't need to handle this low-level stuff.
Although I am unfamiliar with mySQL, I understand that it is a very popular database, so it is hard for me to believe that you are the only mySQL user who is experiencing these problems.
Have you tried reading the mySQL documentation? Or asked on some mySQL specific forum (or newsgroup or mailing-list or similar)?
Good Luck, Avi.
P.S. Perhaps you should consider investing in a more robust DBMS? (Remember, you get what you pay for!)
Joined: Sep 10, 2004
thanks for your tips. What other robust DBMS do you have in mind??? MySql is very popular among developers and does a very good job; but being open minded I wonder what would you recomend on.
Avi, How does Oracle handle this situation if each numbered item is in a separate db transaction? 1) User A gets record and displays it to webpage 2) User B gets record and displays it to webpage 3) User A updates one field 4) User B updates one field (or the same field)
As far as I know, the database can't deal with conflicts because they are four separate transactions from the db point of view. This could even be a legitimate set of activities.
Jeanne, If you are unfamiliar with how Oracle works, why are you the moderator of the Oracle forum on JavaRanch?
Oracle will lock a row, in a table, that is being modified -- so as to prevent simultaneous modification to that same row. So, using your example, I assume that you are asking, "how does Oracle handle a situation where two users (in separate transactions) are attempting to simultaneously update the same datbase table row?"
Quite simply, User B will be blocked until User A has completed modifying the row. You can tst this very simply. Open two different SQL*Plus sessions. In the first session (equivalent to your User A), issue an update statement (but don't "commit"). In the second session, try to update the same row in the same table. The second session will hang -- until you "commit" (or "rollback") in the first session.
Like I said (to Peter) any semi-decent DBMS knows how to handle the situation you describe -- otherwise what would be the benefit of using a DBMS? Perhaps you need to become more familiar with the way Oracle works?
Avi, I am familiar with Oracle. It was more of a statement of laying out an example than an actual question.
I agree with your description if you are in the same transaction of mySql session. That is why I included the example of displaying to a web page. When you display to a web page, you return something to the user and end the transaction. This is done to avoid locking the database. What if a user starts looking at something and then gets coffee and never comes back? In a web app, you don't lock the actual database while the user peruses.
The benefit of using a RDBMS comes from when things really are in the same transaction. For example, the standard read a row, add one and write a row scenario.
Back to Peter's question: it depends on the user base. If this is a Swing GUI with users who do things quickly, you can do everything in the same transaction (and timeout the user if he/she doesn't respond quickly enough.) If it is a web app, you need to use a different mechanism such as the one I described.
Originally posted by Avi Abrami: Jeanne, If you are unfamiliar with how Oracle works, why are you the moderator of the Oracle forum on JavaRanch?
Unfortunately it looks like you have misunderstood the purpose of moderation. At no stage do we say the moderators are experts, in their own forum or any other. Moderators main responsibilities are to keep the peace, make sure people obey the rules and perform other book-keeping such as moving topics to the correct forum. I would expect Oracle forums to have Oracle experts, but this is an unrealistic expectation for a site staffed by volunteers.
That said, Jeanne has great knowledge is great across a range of topics and she is an extremely valued member of this community, and I see no problem with her post
Originally posted by David O'Meara: Unfortunately it looks like you have misunderstood the purpose of moderation.
I agree with David! Any time we should address the issue not any person
Jeanne has great knowledge is great across a range of topics and she is an extremely valued member of this community
Again I am agree with this
Originally posted by Avi Abrami: why are you the moderator of the Oracle forum on JavaRanch?
This is not only for Oracle Forum ! So depth of deep concepts should not be expected.
This is JDBC forum which comprises all database and their interaction with java and at many instance Jeanne stands far ahead even if It is oracle or non -Oracle [ April 07, 2005: Message edited by: Shailesh Chandra ]
Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
even if database provide locking mechanism for update etc but There are some condition which are not handled by database itself
1) User A gets record and displays it to webpage(User is displayed value X1) 2) User B gets record and displays it to webpage(User is displayed value X1) 3) Both User A and B decides to Modify (still value X1) 4) User A issues his update query and row is locked after commit value is updated to X2 5) User B issues his update query based on previous value is X1 but value is changed
now this is the condition database will not handle itself. We will have to handle same manually in our application.
Originally posted by Avi Abrami: Jeanne, If you are unfamiliar with how Oracle works, why are you the moderator of the Oracle forum on JavaRanch? .....Perhaps you need to become more familiar with the way Oracle works?
I think it's important to take a minute and familiarize yourself with how Javaranch works. We're a friendly place, and we go out of way to make people feel welcome. Thus, we don't try to make people feel dumb, or badly, or whatevertheheck, when they ask a question. If you're able to be helpful in a kind manner, then you've found the right place. If you are not, perhaps you have not.