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
