Configuring a Version Counter

The Mapper supports management of a version id column, whichis a single table column that increments or otherwise updates its valueeach time an UPDATE to the mapped table occurs. This value is checked eachtime the ORM emits an UPDATE or DELETE against the row to ensure thatthe value held in memory matches the database value.

Warning

Because the versioning feature relies upon comparison of the in memoryrecord of an object, the feature only applies to the Session.flush()process, where the ORM flushes individual in-memory rows to the database.It does not take effect when performinga multirow UPDATE or DELETE using Query.update() or Query.delete()methods, as these methods only emit an UPDATE or DELETE statement but otherwisedo not have direct access to the contents of those rows being affected.

The purpose of this feature is to detect when two concurrent transactionsare modifying the same row at roughly the same time, or alternatively to providea guard against the usage of a “stale” row in a system that might be re-usingdata from a previous transaction without refreshing (e.g. if one sets expire_on_commit=Falsewith a Session, it is possible to re-use the data from a previoustransaction).

Concurrent transaction updates

When detecting concurrent updates within transactions, it is typically thecase that the database’s transaction isolation level is below the level ofrepeatable read; otherwise, the transaction will not be exposedto a new row value created by a concurrent update which conflicts withthe locally updated value. In this case, the SQLAlchemy versioningfeature will typically not be useful for in-transaction conflict detection,though it still can be used for cross-transaction staleness detection.

The database that enforces repeatable reads will typically either have locked thetarget row against a concurrent update, or is employing some formof multi version concurrency control such that it will emit an errorwhen the transaction is committed. SQLAlchemy’s version_id_col is an alternativewhich allows version tracking to occur for specific tables within a transactionthat otherwise might not have this isolation level set.

See also

Repeatable Read Isolation Level - PostgreSQL’s implementation of repeatable read, including a description of the error condition.

Simple Version Counting

The most straightforward way to track versions is to add an integer columnto the mapped table, then establish it as the version_id_col within themapper options:

  1. class User(Base):
  2.     __tablename__ = 'user'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     version_id = Column(Integer, nullable=False)
  6.     name = Column(String(50), nullable=False)
  7.  
  8.     __mapper_args__ = {
  9.         "version_id_col": version_id
  10.     }

Note

It is strongly recommended that the version_id columnbe made NOT NULL. The versioning feature does not support a NULLvalue in the versioning column.

Above, the User mapping tracks integer versions using the columnversion_id. When an object of type User is first flushed, theversion_id column will be given a value of “1”. Then, an UPDATEof the table later on will always be emitted in a manner similar to thefollowing:

  1. UPDATE user SET version_id=:version_id, name=:name
  2. WHERE user.id = :user_id AND user.version_id = :user_version_id
  3. {"name": "new name", "version_id": 2, "user_id": 1, "user_version_id": 1}

The above UPDATE statement is updating the row that not only matchesuser.id = 1, it also is requiring that user.version_id = 1, where “1”is the last version identifier we’ve been known to use on this object.If a transaction elsewhere has modified the row independently, this version idwill no longer match, and the UPDATE statement will report that no rows matched;this is the condition that SQLAlchemy tests, that exactly one row matched ourUPDATE (or DELETE) statement. If zero rows match, that indicates our versionof the data is stale, and a StaleDataError is raised.

Custom Version Counters / Types

Other kinds of values or counters can be used for versioning. Common types includedates and GUIDs. When using an alternate type or counter scheme, SQLAlchemyprovides a hook for this scheme using the version_id_generator argument,which accepts a version generation callable. This callable is passed the value of the currentknown version, and is expected to return the subsequent version.

For example, if we wanted to track the versioning of our User classusing a randomly generated GUID, we could do this (note that some backendssupport a native GUID type, but we illustrate here using a simple string):

  1. import uuid
  2.  
  3. class User(Base):
  4.     __tablename__ = 'user'
  5.  
  6.     id = Column(Integer, primary_key=True)
  7.     version_uuid = Column(String(32), nullable=False)
  8.     name = Column(String(50), nullable=False)
  9.  
  10.     __mapper_args__ = {
  11.         'version_id_col':version_uuid,
  12.         'version_id_generator':lambda version: uuid.uuid4().hex
  13.     }

The persistence engine will call upon uuid.uuid4() each time aUser object is subject to an INSERT or an UPDATE. In this case, ourversion generation function can disregard the incoming value of version,as the uuid4() functiongenerates identifiers without any prerequisite value. If we were usinga sequential versioning scheme such as numeric or a special character system,we could make use of the given version in order to help determine thesubsequent value.

See also

Backend-agnostic GUID Type

Server Side Version Counters

The version_id_generator can also be configured to rely upon a valuethat is generated by the database. In this case, the database would needsome means of generating new identifiers when a row is subject to an INSERTas well as with an UPDATE. For the UPDATE case, typically an update triggeris needed, unless the database in question supports some other nativeversion identifier. The PostgreSQL database in particular supports a systemcolumn called xminwhich provides UPDATE versioning. We can make useof the PostgreSQL xmin column to version our Userclass as follows:

  1. from sqlalchemy import FetchedValue
  2.  
  3. class User(Base):
  4.     __tablename__ = 'user'
  5.  
  6.     id = Column(Integer, primary_key=True)
  7.     name = Column(String(50), nullable=False)
  8.     xmin = Column("xmin", Integer, system=True, server_default=FetchedValue())
  9.  
  10.     __mapper_args__ = {
  11.         'version_id_col': xmin,
  12.         'version_id_generator': False
  13.     }

With the above mapping, the ORM will rely upon the xmin column forautomatically providing the new value of the version id counter.

creating tables that refer to system columns

In the above scenario, as xmin is a system column provided by PostgreSQL,we use the system=True argument to mark it as a system-providedcolumn, omitted from the CREATE TABLE statement.

The ORM typically does not actively fetch the values of database-generatedvalues when it emits an INSERT or UPDATE, instead leaving these columns as“expired” and to be fetched when they are next accessed, unless the eager_defaultsmapper() flag is set. However, when aserver side version column is used, the ORM needs to actively fetch the newlygenerated value. This is so that the version counter is set up _before_any concurrent transaction may update it again. This fetching is alsobest done simultaneously within the INSERT or UPDATE statement using RETURNING,otherwise if emitting a SELECT statement afterwards, there is still a potentialrace condition where the version counter may change before it can be fetched.

When the target database supports RETURNING, an INSERT statement for our User class will looklike this:

  1. INSERT INTO "user" (name) VALUES (%(name)s) RETURNING "user".id, "user".xmin
  2. {'name': 'ed'}

Where above, the ORM can acquire any newly generated primary key values alongwith server-generated version identifiers in one statement. When the backenddoes not support RETURNING, an additional SELECT must be emitted for everyINSERT and UPDATE, which is much less efficient, and also introduces the possibility ofmissed version counters:

  1. INSERT INTO "user" (name) VALUES (%(name)s)
  2. {'name': 'ed'}
  3.  
  4. SELECT "user".version_id AS user_version_id FROM "user" where
  5. "user".id = :param_1
  6. {"param_1": 1}

It is strongly recommended that server side version counters only be usedwhen absolutely necessary and only on backends that support RETURNING,e.g. PostgreSQL, Oracle, SQL Server (though SQL Server hasmajor caveats when triggers are used), Firebird.

New in version 0.9.0: Support for server side version identifier tracking.

Programmatic or Conditional Version Counters

When version_id_generator is set to False, we can also programmatically(and conditionally) set the version identifier on our object in the same waywe assign any other mapped attribute. Such as if we used our UUID example, butset version_id_generator to False, we can set the version identifierat our choosing:

  1. import uuid
  2.  
  3. class User(Base):
  4.     __tablename__ = 'user'
  5.  
  6.     id = Column(Integer, primary_key=True)
  7.     version_uuid = Column(String(32), nullable=False)
  8.     name = Column(String(50), nullable=False)
  9.  
  10.     __mapper_args__ = {
  11.         'version_id_col':version_uuid,
  12.         'version_id_generator': False
  13.     }
  14.  
  15. u1 = User(name='u1', version_uuid=uuid.uuid4())
  16.  
  17. session.add(u1)
  18.  
  19. session.commit()
  20.  
  21. u1.name = 'u2'
  22. u1.version_uuid = uuid.uuid4()
  23.  
  24. session.commit()

We can update our User object without incrementing the version counteras well; the value of the counter will remain unchanged, and the UPDATEstatement will still check against the previous value. This may be usefulfor schemes where only certain classes of UPDATE are sensitive to concurrencyissues:

  1. # will leave version_uuid unchanged
  2. u1.name = 'u3'
  3. session.commit()

New in version 0.9.0: Support for programmatic and conditional version identifier tracking.