Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MySql (5.5) database with a MS Access frontend. If two users are viewing the same record at the same time how can I make each user aware of each other? For example how would I go about displaying a label indicating that another user is also viewing the same record?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

I may be wrong, but I don't think this is possible unless you restrict access to the data to be via stored procedures - then the procedure can trigger audit mechanism that you can expose to the users. You would also need polling of the audit if both users need to be aware.

share|improve this answer
    
I'm already using stored procedures to Read and Update records so this sounds promising. What is the this audit mechanism you mention? Is it a built-in MySql feature? –  David May 27 '11 at 11:28
    
I wasn't referring to anything built-in I'm afraid - I meant you could create you own audit tables and use them to log the calls to your stored procedures. –  Jack Douglas May 27 '11 at 11:34
    
OK, but how would the system know when a record is no longer being viewed: (1) by moving to a new record - not a problem (2) by closing the front-end - again not a problem (3) front-end crashes and closes unexpectedly! How would I deal with this situation and not leave a record apparently being viewed when it is not? –  David May 27 '11 at 13:51
    
You could do this by polling from the client - if the client stops polling (ie calling the polling procedure), it is considered dead. Altogether, this is quite a difficult project in general and shouldn't be undertaken lightly! –  Jack Douglas May 27 '11 at 13:56
    
Nice idea! But I am starting to wonder if this is worth all the hassle. Maybe a nice side project which I'm sure I'd learn a lot from undertaking. –  David May 27 '11 at 14:13
add comment

This process will be a bit cumbersome for the DB. Not only do you want to know if the current record is being viewed, but you also want to notify the users of each other. When a user reads a record, the DB system quickly fetches the data and returns it to the user. As far as the DB is concerned, once the data is sent to the client, nobody is 'viewing' said record. From the application side, the user might still be reading the details, but the DB has finished its part. This whole process can be completed in micro seconds by the DB.

To handle the real life process, the front end application will need some sort of 'finished reading' button to explicitly notify other users (the DB is not concerned as it had done its part).

  1. What I would have done is create another read tracking table, lets say rtrack.
  2. When a user wants to read a record from the table, insert a record into the rtrack table with the row id and the user name of the user who issued the select.
  3. Next, check the rtrack table for the same row id, to see the other users who are reading the same record.
  4. Update the notification area on the front end with the other user information if any.
  5. A polling interval (or trigger) should be setup to check the rtrack table to see when other users read the same record, while it is still being read by the current user.
  6. Once the user clicks the 'finished reading' button, the corresponding record in the rtrack table can be deleted, based on the user name/row id.

This is what I could think of quickly. If there is a more efficient method, please let me know.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.