Multi user application

[eluser]WanWizard[/eluser]
Don't get me wrong Jedd.

I'm absolutely with you that you need transactions if you do multi table updates and want to make sure the database stays consistent. It's not a matter of either/or, both are valid, but have a different application.

But transactions don't protect you from concurrent updates. They do in a traditional client/server application, where you start a transaction, read the data, present it to the user, wait until the user presses 'save', update the tables, then end or rollback the transaction. However, in a web environment you have stateless clients. The moment you present the data to the user (you output the form), the PHP script ends, and all transactions and locks are released. Therefore someone else can update the data before you can press the 'save' button. Same is true for locks, they are useless in a web environment.

Even if you could work around this issue, it's not a solution, because you hugely increase the chance of a deadlock (one of the biggest issues in client/server application design). Imagine a user requesting an update form, your script starts a transaction and locks tables. Then the user closes the browser and goes home. You end up with locks that stay in place until you restart your database engine. Not a good idea. In a proper design, deadlocks should not be possible.

As for atomicity, this is not required in this solution. There isn't a single database that supports concurrent updates (which will always lead to corruption). Even if two users do an update or the same record at exactly the same moment (using the same timestamp value), one of them will always fail. So, the update query that gets queued first will succeed, the second will fail because of the fact the first updated the timestamp.

Again, this works fine without transactions when you're updating a single table (which is most common). When a post requires multiple tables to be updated, you need a transaction to keep it consistent. But still, for each of the tables in the transaction you need the timestamp check, because each of them could be modified since you read the record to populate the form.

Without the timestamp, how are you going to detect that? The only option is the old-fashioned way: lock, read, compare, act. Which is horribly slow and complex, compared to the timestamp check.

[eluser]sqwk[/eluser]
Just read through the thread: How does the timestamp work? When loading the form, you read out the timestamp, keep it in memory and then compare it when updating the form?

[eluser]WanWizard[/eluser]
Correct. The sequence is this:

User requests an update form:
- SELECT * FROM table WHERE id = 'something'
- field table.last_update_timestamp contains the timestamp of the last update
- form is displayed, contains a hidden field containing the timestamp value
( you can store it in the session if you don't want to expose it in the form )

User posts the form:
- validate the form
- UPDATE table SET fields, last_update_timestamp = 'new_timestamp' WHERE id = 'something' AND last_update_timestamp = 'retrieved_timestamp_value'.

If this UPDATE returns affected_rows() == 0, than no record was updated, and the assumption is made that this is because of a mismatch of the timestamp. This can be caught so action can be taken.

[eluser]jedd[/eluser]
Arthur - I hope this is all useful stuff.

WanWizard - thank you for taking the time to explain what you're doing - I'm feeling much better informed of the problem you're trying to solve here.

I grabbed ExiteCMS from VCS, but couldn't (quickly) find the schema and mechanism you're describing here. FWIW I was never advocating locking tables during a read request and holding them open for a web client until they clicked 'save'. I've not tried to solve this problem before, but (perhaps embarrassingly) I had always assumed that I would use a process that you disparagingly refer to as the '... old-fashioned way: lock, read, compare, act'. That is, the client gets a copy of all the data they're about to manipulate, no database tables are locked during this process - then when the client does an update/save operation, tables are locked, a transaction is started, data is re-read, compared to the data the user was basing their changes on, and at that point if there's a discrepancy the app has to 'handle it' - if there isn't, the changes are written, transaction is closed, tables are unlocked, life goes on.

As I say, though, I speak from the naiveté of never actually having had to do this before.

Btw, what's the etymology for Exite - I kept on getting confused trying to navigate around the directory structure because I keep typing excite.

[eluser]WanWizard[/eluser]
ExiteCMS is still in very early alpha state.

As we (still) haven't made the move from subversion to something more distributed, most of the development happens in (non-public) branches to avoid breaking the trunk. This modification hasn't made the trunk yet, as development in that branch also involves rewriting models and changing tables.

I always used to follow your way of working as well, but noticed that very often I only needed the re-read for validation, and I needed to validate all fields. You can still find traces of this in the current trunk code. To solve this, I started thinking about a safe alternative that didn't involve the entire re-read and validate cycle.

ExiteCMS started out as an internal development, first to power the front and backoffice, later to power some internal sites for clients of Exite, the company that now sponsors the open source development of ExiteCMS.

Exite is an ICT company based in Rotterdam, The Netherlands. It's name is a contraction of "Experts in Information Technology". Native English speakers keep struggling with the 'c'...