Search

Has anyone dealt managing data with multiple users interacting with a symphony site previously? How did you deal with data consistency?

Since it doesn’t look like symphony was built with this idea in mind (unless I’m missing something), would it be possible to override the default publish page that deals with entries? If that were possible, you could implement simple locking with mysql, or whatever really depending on your need.

As I was thinking about a project ensemble in symphony, this question occurred to me, but I’m not sure how to deal with it.

I haven’t looked at the code yet but something as complex as Symphony should definitely use transactions.

Edit: Whops, I think I’ve misunderstood. I like nickdunns idea though (one message below).

Scott, I imagine this could be achieved with an extension, if the requirement is to lock entries to a specific author as they are edited.

The extension would create a table to store locked entry references (entry_id, user_id and timestamp). When a user views an entry in Edit mode (or after creating an entry) it becomes locked to them only and a row added to the “lock” database. Other users coming to this entry would perhaps still be able to view, but the textfields set as “readonly”, submit button removed and an appropriate system message at the top.

After saving this lock reference is removed and other users are free to edit the same entry. A lock should time out after X minutes to prevent entries being locked for a long period of time. When viewing an entry there could be a frequent AJAX request updating the lock reference so that if I leave my browser open on an entry it remains locked until I navigate away.

The majority of this could be done on the client-side with JavaScript, preventing any changes to the core.

That’s an interesting way of going about it, then all you’d need is a simple extension that adds some JS, and some functions to manage the table and a page to send requests too.

The timing of locking is always a tricky subject, but I don’t imagine you’d ever really want to make an intense project that thousands of people update with symphony. Or perhaps it wouldn’t be your first choice.

This is easy if you assume they’re using the backend, but what if they want to use events? I guess if there’s a submit button with action[‘something’] with a hidden input called id, you could setup the lock just like on the /edit page.

This is something I might try to do within the next couple days, I have an upcoming project for a client that was a simple invoicing application, but a couple people will be using simultaneously. Normally, I just go and use rails because it’s easy for things like this, but if I could get symphony running quickly, well, that would be interesting.

I imagine the extension could expose an Event “Filter Rule” named “Lock Entry”. On successful creation, so long as the user ID is sent along with the request (if you feel the need to store the user account locking the entry) this could be auto-locked.

Rowan’s Email Template Filter extension uses this type of functionality — the extension provides a filter than can be attached to an Event.

On several projects I’ve worked we’ve not allowed the client access to Symphony at all, instead favouring to build our own UI layer using events (hence Form Controls and EventEx). In this instance you could lock an entry with a “Locked” checkbox and update in a similar manner to the above.

Timing shouldn’t be much of a problem since MySQL comes with its own set of concurrency-prevention-tools like locking or transactions.

I for one would wrap the whole thing up into a field (for backend) and a filter (for events).

Unfortunately, we can’t use mysql’s row-level locking because the sym_entries table is a MyISAM table, which doesn’t allow row-level locking (only InnoDB does), so something like Nick mentioned would have to be used.

I like the idea for a filter on the frontend, for the backend at first, I was going to apply locking to every section, then just have an option to exclude sections as you like.

The only thing that would be slightly annoying is that after a user closed a page, the lock would stick around for so long after. Is there a way in jQuery to setup a callback just before the window is closed?

Edit: Yes, unload :P

Scott, you can still lock the whole table, select the state of the entry to be edited, set the appropriate flag for it and unlock the table again. All other actions will just wait until the lock has been released.

I even suggest using a new sym_locks table to do just that.

I would simply display a message saying “You are holding a lock on this entry. Please do not close the window.” while the editor is opened. If somebody closes the window nonetheless, one has to wait for the timeout.

I don’t really like the idea of using JavaScript on such critical elements. :-)

I would have thought a lock timeout of 20–30 seconds would be sufficient. So long as the AJAX request fires more frequently than this, the lock is maintained while the user views the page. And if the unload event doesn’t fire (browser crashes, user doesn’t have JavaScript) you fall back to the timeout.

Although a database-level lock is the most robust from a technical point of view, from a user point of view the lock needs to prevent them editing at all. Would a MySQL table lock persist only for the duration of the actual UPDATE transaction? Having it reflected accurately in the UI is imperative so that the user can’t start editing and then be told the entry is locked when they come to save.

Scott, I’d be willing to work on this with you if you need a hand.

nickdunn: I think 20-30 seconds is not enough. People without JavaScript would really quickly hit the timeout. What about 2-5 Minutes?

Additionally I don’t think there is enough time on unload to fire a reliable AJAX-call.

Fair point. I see this as being configurable. JavaScript is a requirement for the Symphony UI anyway, so I don’t think it’s a problem to rely on this for locking.

It looks like if you setup a write lock on the sym_entries table prior to any entry commit, and there’s something wrong with the entry itself, or the power goes off, or something evil happens, the table would stay locked because it never hits the delegate that occurs after the commit to unlock the table.

It seems that there’s no way for symphony to tell an extension if a commit failed or not to unlock the table. Perhaps there is though.

As for unloading, I’m not sure if the ajax request would fire or not, perhaps you can setup a delay on closing the window to give the request ample time to be off on it’s way.

And from the mysql docs:

If a client connection drops, the server releases table locks held by the client. If the client reconnects, the locks will no longer be in effect.

So even if it fails, the table will be automatically unlocked next time someone tries to access the database, since every time the page, unless the database connection stays open between page loads.

Can you issue a lock on a specific row in the table, or do you need to lock sym_entries entirely, across all sections?

You can only lock the entire sym_entries table since it’s not an InnoDB type table. So it would be across all sections, but further writes to the sym_entries table should just be delayed until the lock is released, explicitly or implicitly.

I think you are confusing a few things here. You can not use MySQLs LOCK (upper case, it is an SQL-statement) to implement the feature we are talking about: After execution of the script, when the page is fully loaded, the MySQL-connection is terminated and the LOCK implicitly lifted!

All we can do is LOCK a table just for a brief moment to write a semaphore. This semaphore does not prevent you from editing the row, it is just a sign for others: careful, I am being edited!

The reason we need a LOCK is because somebody could be messing with our data in between checking for and writing the semaphore.

For example:

Person 1: SELECT semaphore FROM sym_entries WHERE id=1; // read: open
Person 2: SELECT semaphore FROM sym_entries WHERE id=1; // read: open
Person 1: UPDATE sym_entries SET semaphore = 1;         // write: protected
Person 2: UPDATE sym_entries SET semaphore = 1;         // write: protected

Both editors think that nobody else is trying to edit the entry.

The LOCK would prevent this:

Person 1: LOCK TABLE sym_entries;                       // LOCK accepted
Person 1: SELECT semaphore FROM sym_entries WHERE id=1; // read: open
Person 2: LOCK TABLE sym_entries;                       // LOCK denied
Person 1: UPDATE sym_etnries SET semaphore = 1;         // write: protected
Person 1: UNLOCK TABLE sym_entries;                     // LOCK released
Person 2: LOCK TABLE sym_entries;                       // LOCK accepted
Person 2: SELECT semaphore FROM sym_entries WHERE id=1; // read: protected

Now Person 1 was the only one to see the entry to be open. Of course it would make sense to set a “last edit”-timestamp instead of a boolean semaphore. That way the system will already provide a timeout-mechanism.

I think we might be on different pages. I didn’t want to modify symphony at all, including the sym_entries table, hence making another table that handles the locking that would (as per your example above) be handled by mysql using LOCK and UNLOCK.

That new table itself would be LOCKed and UNLOCKed through SQL when adding locks so as to not cause a deadlock.

So I spent some hours this afternoon putting this together, I have the backend nearly done. Most everything should work.

I wouldn’t go and use it in a production environment yet, I guarantee you there’s a few bugs (it’s so hard to test in php), but I could use help testing it! And I still have to figure out how to inject script/css onto a frontend page.

Hopefully I’ll be able to finish it by next week.

http://github.com/scottkf/pessimistic-db-locking/tree/master

Good work Scott! I had a play last night and came up with something almost identical to you in fact! Not as fully-featured, mind.

http://nick-dunn.co.uk/assets/files/entry_lock.zip

It’s rather buggy (in that it doesn’t actually work, and the setInterval AJAX call is recursive and will crash your browser!) but you might find one or two bits useful. For the lock message I opted to keep it in the familiar Symphony UI; using the notice paragraph at the top of the page rather than an overlay or anything more advanced.

Unfortunately you can’t force JS/CSS into a front-end page. What do you need this for? If it’s to test whether the current entry is locked, you could provide a Data Source with the extension that looks up in the locks table and returns entry IDs of currently-locked entries. In the XSLT one can then decide how this should be handled.

Looking forward to a stable release, this looks great :-)

Create an account or sign in to comment.

Symphony • Open Source XSLT CMS

Server Requirements

  • PHP 5.3-5.6 or 7.0-7.3
  • PHP's LibXML module, with the XSLT extension enabled (--with-xsl)
  • MySQL 5.5 or above
  • An Apache or Litespeed webserver
  • Apache's mod_rewrite module or equivalent

Compatible Hosts

Sign in

Login details