Handling concurrent database access

[adrian_quah]

Hey guys,

What is a good way to handle concurrent (simultaneous) access database where users can edit the database contents..A record shd be allowed to be edited by one user at a given time...

I'm using MySQL currently, but will be switching to MS SQL soon. Would appreciate ur ideas n experience on both of these database systems..Thanks!

[adrian_quah]

i've added a new field in each table affected. this field, 'Record_Status' stores values such as 'FREE, 'LOCK',...denoting the status of each record...This is an OK solution i guess systems that migrate from one db to another...

For the sake of options and learning, any other idea to implement this? Maybe there's an SQL statement that can lock a record?

Thanks for making PHP fun 2 learn n use 🙂 the community behind it

[jweissig]

hi,

There are a couple things to keep in mind with a solution like this one.

What happens if the row is locked at 10am by the user wanting to edit their profile and the user leaves your websites. But they don't save the record!

They come back around 1pm and attempt to edit their profile and it is already locked! I guess you could set a timestamp and remove it with a garbage collection routine after a while..

You might want to check out transaction before you do something like the above. Or just double check the value before you save.

• Justin

[adrian_quah]

Yes Justin, ive thought abt it too...good thinking..have u or anyone implemented this kind of routine check on the db? can a php script in a page handle it or shd this be done by some other sub-routines?