Preventing Access to Rows While in Use

[naifwonder]

I have around 5 tables in a database used in different combinations based on what the user needs to do. The tables are for a group management system. I have split the tables according to how frequently they are used and for what purpose (consolidating them results in more processing to get things done).

 

 

So, for the sake of example, let's say that there are 5 tables, each with a row for each group.

 

There are 10 groups.. Group 1, Group 2, Group 3, etc.

 

Also, there are two users, User A and User B.

 

 

Scenario:

 

User A is is accessing the row for Group 1 on any of the tables, and at the same time, User B tries accessing the row Group 1 on any of the tables. I want to make it so that User B will have to wait until User A's operation is complete before User B can continue with their operation.

 

 

Summary of Objective:

 

Basically, I want to make it so that if a group's row is in use in any of the tables, anyone else wishing to access that group's row in any of the tables will have to wait until the first operation is complete. Things should be running on a first come first serve basis. Also, I want to make it so that php scripts that are trying to access the DB are not aborted if the group's row is in use.. just simply "paused" in a que system, and resumes when the row becomes available.

 

 

I am curious as to some of the solutions that are possible for this scenario. Any help would be greatly appreciated.

[fenway]

You're talking about locking -- there are optimistic and pessimistic versions.

[JustLikeIcarus]

Seems like you are wanting application level locking described here http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_get-lock