Optimistic Locking and TIMESTAMP Columns
By including a self-updating TIMESTAMP column in your table, QCubed can automatically generate the functionality to perform Optimistic Locking for that database object. In this example, the person_with_lock table is defined with a TIMESTAMP column so that we can demonstrate Optimistic Locking.
Optimistic Locking is the loosest form of row- or object-level locking that, in general, works best for database-driven web-based applications. In short, everyone is always allowed to read any row/object. However, on save, you are only allowed to save if your object is considered "fresh". Once your object is "stale", then you are locked out from being able to save that stale object. (This is also called a "mid-air collision".) Objects might go stale if two browsers are editing the same object in the database, or in certain situations if the user presses the back button on the browser to re-edit a record already edited.
So whenever you Load an object, you also get the latest TIMESTAMP information. On Save, the TIMESTAMP in your object will be checked against the TIMESTAMP in the database. If they match, then the framework knows your data is still fresh, and it will allow the Save. If they do not match, then it is safe to say that the data in the object is now stale, and QCubed will throw an Optimistic Locking Exception.
The Optimistic Locking constraint can be overridden at any time by simply passing in the optional $blnForceUpdate as true when calling Save. However, if you override it, or you do not provide a TIMESTAMP column for a table, then you are allowing the user to overwrite data that someone else changed, but the user did not get a chance to see.
How you create your TIMESTAMP column will depend on the database you are using. MySQL TIMESTAMP columns by default will have the current time inserted into them, and be automatically updated. MySQL 5.6 limits each table to only having one of these columns, but 5.7 lifts this restriction. QCubed will automatically detect a column set up this way.
However, other databases, like PostgresSQL, require you to set up a trigger to auto-update a TIMESTAMP
column, which QCubed cannot detect. You can work around this limitation by specifying in a comment on the
column in the database that you would like a column to be considered a Timestamp for purposes of
Optimistic Locking. You can also tell QCubed to automatically update such a column with
the current timestamp. To do this, you enter a JSON expression into the comments field that sets the Timestamp and
the AutoUpdate values to 1. You can find an example of how to do this in the pgsql.sql file for the
examples database. Essentially, you want your comment to contain this:
{"Timestamp": 1, "AutoUpdate": 1}
You can handle optimistic locking exceptions in a number of ways. The default forms generated by the codegenerator and templates that come with QCubed handle it by informing the user that another user has simulataneously updated a record, and asking if the user would like to over-write those changes, or reload the form to get the changes.