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.

Object Save and Double Saves on the PersonWithLock Object

Saving a Single Object will perform the save normally

Attempting to save a Two Instances of the Same Object will throw an Optimistic Locking exception

Using $blnForceUpdate to avoid the Optimistic Locking Exception

Saving two instances that change different fields, showing that no collision happens so no Optimistic Locking exception is thrown.