Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Lock Mechanism in mySql. any idea?

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is the deal:

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 ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!)
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Avi,

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.

thanks
Peter
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Peter,
I'm afraid I am only familiar with Oracle and Informix, and I don't know enough about your situation to say whether one of those will be suitable for you.

I can only suggest that you provide a few more details about your database requirements and ask for recommendations.

Good Luck,
Avi.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Good Luck,
Avi.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Thanks
[ April 07, 2005: Message edited by: Shailesh Chandra ]
 
Max Habibi
town drunk
( and author)
Sheriff
Posts: 4118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?


Avi,

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.

M
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic