Hi all,
I have search the forum but haven't found a post that addressed my questions.
I have a client that has requested a database that will keep track of a small private school. Their requirements are fairly simple for the most part: keeping track of student attendance and keeping track of various miscellaneous notes on each student.
The only thing that is worrying me at this time is that the database will need to be accessed concurrently by many users. So I see the situation where the same record will be accessed at the same time and the subsequent issues with updating that particular row at the same time: for example when both user open the same unedited row and then proceed to modify it at different times causing one user to overwrite the previous update's changes.
What kind of implementations would you recommend that would help combat these scenarios. I was thinking of implementing last update timestamps and while this would be sufficient in a site of such requirements I am curious as to how to have a more solid application.
Sorry for the long post, if I haven't elaborated properly I will gladly expand further.
Thanks in advance everyone
Frank
Properly allowing concurrent UPDATEs in MySQL???
Moderator: General Moderators
-
frank_mark
- Forum Newbie
- Posts: 13
- Joined: Fri Jan 16, 2009 6:35 am
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Properly allowing concurrent UPDATEs in MySQL???
I would not lock the row because some users might leave the web page open on their screen and go out for meetings and it would prevent others from editing the user.
Version control is an option.
Version control is an option.
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: Properly allowing concurrent UPDATEs in MySQL???
Or improperly leave the page by, say, turning off their computer.some users might leave the web page open on their screen and go out for meetings
You might want to consider the term "many." How many is "many" in your case? What are the real odds of two users deciding to change a record at precisely the same time, and what are the consequences if it does happen that one user's changes are not recorded? Your use of "a small private school" suggests the odds are, perhaps, fairly low and may be acceptable. The issue should be explained to them, of course, but...
You can minimize the risk by having info for each class in its own table. Then a teacher recording his class info will not clash with another teacher recording his class info. The less info in each table the less chance of conflict. (That is to say, the more highly "normalized" the db is.) This will tend to require using "many to many relationships."
Re: Properly allowing concurrent UPDATEs in MySQL???
I'd go with your timestamp idea. phpBB3 does this - you may notice that when you go to add a followup to a thread, it adds an extra confirmation step when someone else has posted between the time you clicked the 'Post reply' button, and the 'Submit button.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: Properly allowing concurrent UPDATEs in MySQL???
The problem with the timestamp idea is that all it can do is warn that "you are about to wipe out somebody else's data." So what does the user do in response to that warning? That implementation gets a little complex.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Properly allowing concurrent UPDATEs in MySQL???
Keep a version control similar to a wiki.
So things can be undone or previous versions can be reviewed.
So things can be undone or previous versions can be reviewed.
Re: Properly allowing concurrent UPDATEs in MySQL???
How about only updating the specific fields that a user changed. The latest modification will take precendence over previous modifications.
So if you have a form with first name, last name, address and phone number, if they only change the phone number, don't bother placing the rest of the fields in the update query. When you redisplay the form, populate the unchanged fields with the record from the database. Make sense?
So if you have a form with first name, last name, address and phone number, if they only change the phone number, don't bother placing the rest of the fields in the update query. When you redisplay the form, populate the unchanged fields with the record from the database. Make sense?
-
frank_mark
- Forum Newbie
- Posts: 13
- Joined: Fri Jan 16, 2009 6:35 am
Re: Properly allowing concurrent UPDATEs in MySQL???
Thanks everyone, I hadn't checked the post in a while.
I really appreciate the input. Thanks!!
Either way I'm sure that if I did try to implement this I would encounter some form of deadlock.
I really appreciate the suggestions and will experiment with timestamps and updating only the modified fields.
Thanks again.
I really appreciate the input. Thanks!!
I was thinking of making the session timeout to address this problem.some users might leave the web page open on their screen and go out for meetings
In this case would MySQL release the row if a connection is lost with a lock wait timeout?Or improperly leave the page by, say, turning off their computer.
Either way I'm sure that if I did try to implement this I would encounter some form of deadlock.
I really appreciate the suggestions and will experiment with timestamps and updating only the modified fields.
Thanks again.