record locking 1

[southbeach]

Simple question to an age old problem:

How do you guys handle record locking in PHP/MySQL applications?



--
SouthBeach

http://www.fp2php.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[ChrisHirst]

I've never found it to be a problem, as there is only ever one 'user' accessing the data, and the operations/transactions are, for all intents and purposes, instantaneous. Record locking is really only necessary when "slowpoke" humans are involved and opening queries then staring intently at the information for maybe, .... oooh whole seconds before making a decision to do something. PHP transactions are more of a Connect -> Query -> Gone style and the time between "Connect" and "Gone" is measured in milliseconds.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[southbeach]

Here is my scenario:
1) SELECT and echo to render a data grid
2) User clicks on row and triggers
3) SELECT and echo to render a full page record

At this point, user has chosen to edit a row. As you said, the process time to get to this point is not even a second.

Now, imagine an office with 5 or more people handling calls and attempting to access the same row (full record mode) at once.

As of this moment, whomever [ SAVE ] last, retains content.

Normally, in a multiuser environment, record locking is always a concern and I hate the idea of "tagging the record as locked" by setting a field since the same user that just opened the record, can walk away, close the browser, let the session expire or simply spend the rest of the day in social media.

One option is to tag the record as "locked" upon opening the "full record mode page", and run a cron process to scan for "locked" rows set 3 minutes ago or later and unset them.



--
SouthBeach

http://www.fp2php.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[ChrisHirst]

Okay, If your database is using the MyISAM engine you can only 'lock' at table level, to use row level locking you need to be using the InnoDB storage engine,

http://dev.mysql.com/doc/refman/5.7/en/internal-locking.html

You don't really need a cron task to release the locks as SET_LOCK() takes a timeout value.

Then again, one of our hosting clients has a website that using PHP & MySQL that is accessed by website customers and a seven strong sales team with no 'locking' in place, without any "collision issues".

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum