Web lists-archives.com

Re: [PHP] LOCKS???




On 29 November 2016 at 16:51:24, Tedd Sperling (tedd.sperling@xxxxxxxxx)
wrote:


> On Nov 28, 2016, at 11:06 AM, Stuart Dallas <stuart@xxxxxxxx> wrote:
>
> On 28 November 2016 at 16:03:17, Stuart Dallas (stuart@xxxxxxxx) wrote:
>> On 28 November 2016 at 15:19:12, Tedd Sperling (tedd.sperling@xxxxxxxxx)
wrote:
>>> This experiment demonstrates that LOCK TABLES does not work for me.
>>>
>>> My question is”Why?”
>>
>> The better question to ask is why are you trying to lock tables? There’s
usually a number of better ways to achieve something that can be solved
using table locking. What’s the actual operation you’re doing, or is it a
purely academic question?
>>
>
> Also, based on your restatement of the problem it would suggest you run
the two scripts in sequence, not in parallel. If that’s the case then the
answer is clear: when a session is terminated (i.e. you disconnect from the
database whether explicitly, or due to an error, or because the script
ends) all locks obtained by that session are released.
>
> -Stuart


-Stuart:

To answer your first question:

>> The better question to ask is why are you trying to lock tables? There’s
usually a number of better ways to achieve something that can be solved
using table locking. What’s the actual operation you’re doing, or is it a
purely academic question?

<snip>


Now, considering there may be several users working on the database at the
same time, I want to devise a way for users to do that without conflict
(i.e., several users working on the same record at the same time).

Sure, I can use transactions, but that simply assures the record being
saved is actually recorded — but that’s not the issue here.

Sure, I can simply allow only one user at a time to edit database, but I
think this problem can be solved less harshly.

So, I am currently looking at LOCKing down the tables involved with any
specialized editing — such as editing a member, or editing an Event, or
editing an AOI, or editing an Association, or connecting AOI/Associations
where each of these operations require specific tables access in editing.

What I am thinking is when a user shows interest in editing any table
(i.e., users accesses a page that allows editing) to LOCK down the tables
involved until such time the user is finished with editing (i.e., moves to
a different page).

In the event that two (or more) users access the same page, they all can
read the page, but editing is limited to first one. The late comer(s) will
be shown a pop-up that states they cannot edit the page at this time. Then
when the first user leaves that page, then the next user will be told
(another pop-up) the editing is open to them and then LOCKs are in place
for them.

I know this will require me to keep track of what users are looking at what
pages, but that’s not too much of a problem provided I can actually LOCK
down tables — and thus the reason for my question.


What you are trying to do cannot be achieved with table locking due to the
nature of PHP. The share-nothing approach where there is no persistence
between requests extends to table locks. Each request to the server,
whether full page or AJAX, will initialise a new connection to the database
(even when using connection pooling each use is treated as a new
connection). This means that:

1) Page request locks the table.
2) AJAX request modifies the table.
3) Page or AJAX request unlocks the table.

…will not work because the table lock will be implicitly released one
request 1 has finished processing.

This is one of the classic problems of software engineering - how do you
handle conflicts when you have multiple editors of the same piece of data.
Even in traditional client/server applications the approach you’re trying
to use become rare as it introduces a large number of potential problems.

So, to achieve your desired result you can do one of the following (other
options do exist but these are the main solutions that tend to be used):

A) Implement your own locking mechanism that persists across requests.

This is relatively simple to do but introduces problems around expiring
locks in case a user does not take the action required to release their
lock. This option also has the potential for over-limiting access to the
resource. Users are idiots and don’t understand the concept of having
locked something simply by having it open on their desktop. You can try to
educate them about this but it will likely be an uphill struggle.

Locking mechanisms are trivial to implement. I’ve used MySQL (see the
example I use in my job queues: http://3ft9.com/php-job-queue/) or
memcached (using the ADD operation with an expiry:
http://php.net/manual/en/memcache.add.php) in the past but there are lots
of ways of doing it. The key requirements are avoiding race conditions and
ensuring stale locks are handled gracefully.

B) Check for edits on save and reject if the data has been changed.

This could be through a timestamp or a hash of the content, and the
granularity is up to you.

So, when you retrieve the data to display the edit form (or whatever) you
also read a value that will change whenever the data is updated. When you
write an update to the data you do so in a way that will get rejected if
that value has changed. In many cases that can be as simple as adding a
where clause to the update statement, but gets a little more complicated if
your granularity extends across multiple rows or multiple tables.

The key thing is to make sure that the check and the update are the same
atomic operation, otherwise you end up with a race condition where another
user could update the row between the check and the update.

C) Attempt to automatically merge the updates on a conflict, or present the
user with a UI so they can do it manually.

This is the best option from a usability point of view but is obviously one
of the more complex solutions to develop. The nature of the data you’re
handling is often the deciding factor when it comes to the manual/automatic
question.


When deciding which approach to use you should consider the expected
frequency of conflicts, how switched on your lowest common denominator user
is, and how much effort you want to put in to the user experience.

—

Your second Question:

> Also, based on your restatement of the problem it would suggest you run
the two scripts in sequence, not in parallel. If that’s the case then the
answer is clear: when a session is terminated (i.e. you disconnect from the
database whether explicitly, or due to an error, or because the script
ends) all locks obtained by that session are released.


>From my perspective, running the scripts in sequence or in parallel should
be both accommodated with respect to different users accessing and editing
the same table. Additionally, I am assuming that when the session is
terminated so is the LOCK — but I have not proved that to myself — I can’t
even get the damn LOCKs to work in the first place.

I have read that LOCKs exist for only a short time, but that presents other
problems, such as “Why did you lock the table in the first place, if the
LOCK may expire before you are done editing? That kind of defeats the
purpose of the LOCK, doesn’t it? So, I am not convinced that is true.

I have also read that LOCKs can only be used in InnoDB engines — so I have
changed my tables to that type, but the problem remains.

I have also read about the dangers of LOCKing tables that may result in a
deadlock (two, or more, operations require saving data to other LOCKed
tables) but also I have read MySQL automatically allows the first user to
LOCK their tables to win. Interesting, huh?

Ok, I’ve read lot, but I still cannot get a single example of LOCK to work.

As mentioned I’m pretty sure this is because you’re attempting to lock in
one request and expect the lock to still be there in subsequent requests.
This is fundamentally not how MySQL table locks work so you’ll need to
implement your own mechanism.

-Stuart