[eluser]jedd[/eluser]
[quote author="WanWizard" date="1279288248"]Transactions are not really a solution for concurrency.
[/quote]
I'm not sure how you do concurrency
without transactions - either using the RDBMS's built-in transaction facilities, or engineering your own pseudo transaction handling mechanism. It seems that you opted for the latter - but I'm genuinely confused how this:
Quote:For a table that needs concurrency protection, we add a column named 'last_updated', which contains the last update timestamp. When an update query is run by the update method, it adds 'WHERE last_updated = <last_update_timestamp>' to the query (the timestamp is retrieved with the other fields in the select to display the form).
If the result is that no records were updated, the record must have been updated by someone else (timestamp in the record doesn't match the one we retrieved earlier), and the code needs to take appropriate action.
... is substantively different from using the DB (or even CI's front-end to the DB's) transaction feature.
In cases where you're updating and inserting across several tables, I think your approach would be very expensive - have you done any testing on this scale, or is this much more of one-customer-per-table approach to the 'database update method'?
You say that each table that needs concurrency protection gets this extra column - isn't this a touch expensive in terms of size? TIMESTAMPs are only four bytes, I know, but any extraneous columns worry me. Is TIMESTAMP even granular enough for this activity - don't you need something sub-second?
Actually, the more I think about it, it seems the really expensive bit would be trying to coordinate several tables being updated by a single transaction (in the loose sense of the word) and then having code wrapped around all those updates to check
each and every table for any updates that happened at the same time.
I don't use CI's AR, as a rule, but the trans_start() and trans_complete() wrappers appear to be a very simple way of achieving exactly what you've just re-engineered - especially as in either case, as you allude to, you still have to write the code that 'takes appropriate action' if a contention is detected. I think retrying
$x number of times to re-commit after a failed trans_complete(), and once
retries == $x handling it further up the stack is almost identical to the
approach you describe you're using in ExciteCMS.
If it isn't, I'd love to learn more about how you're doing it -- this really isn't an area I've done much practical programming in.
