René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"A better optimistic concurrency solution","articleBody":"In chapter 7, we implement a [version number to provide optimistic concurrency controls](https://www.cosmicpython.com/book/chapter_07_aggregate.html#_optimistic_concurrency_with_version_numbers) around each aggregate. However, the version number doesn't actually do anything to prevent concurrency conflicts in itself. All of the behaviour comes from the \"REPEATABLE READ\" transaction isolation level. This is a feature of PostgreSQL that allows the DB to detect a conflict between two concurrent transactions, hence the `serialization` error raised by psycopg2. The version number is only checked in the test, but this is kind of superfluous (we should easily just check that the changes weren't made by the second transaction). \r\n\r\nSqlAlchemy has some built-in functionality for handling [optimistic concurrency using version numbers](https://docs.sqlalchemy.org/en/14/orm/versioning.html#simple-version-counting). This works by using the version number within any `UPDATE` or `DELETE` statements to ensure the version in the DB is the same as the version you initially read and made changes to. This supports \"offline\" concurrency conflicts - where you take a copy of the object out of the session, make changes to it (i.e. maybe within a UI app or other service), and then merge it into the DB. It can handle this because your copy still has its original version number that is compared against the DB. It also handles other situations where things might get out of sync, e.g. from the docs:\r\n\r\n\u003e The purpose of this feature is to... provide a guard against the usage of a “stale” row in a system that might be re-using data from a previous transaction without refreshing (e.g. if one sets expire_on_commit=False with a [Session](https://docs.sqlalchemy.org/en/14/orm/session_api.html#sqlalchemy.orm.Session), it is possible to re-use the data from a previous transaction).\r\n\r\nI've put quite a few hours into it already. The problem I've found is the version number managed by SqlAlchemy is only bumped on the table where the `INSERT`/`UPDATE`/`DELETE` is happening. This means the version number that lives in the table that represents my root aggregate doesn't get bumped when only some child entity is updated. Effectively, every table would need its own version number. One solution might be to have a property on the aggregate that gets touched every update, e.g. a `last_updated_at` timestamp, which would force an update on the root entity. Thus, making the whole aggregate one consistent boundary. I don't really like this idea, though, as it could easily allow bugs where the `last_updated_at` property is not updated.\r\n\r\n\r\nJust wondered if anyone has some insights into this problem. Are the version numbers worth adding (provided you don't need offline semantics or hit the other edge cases), or should you just set `REPEATABLE READ` and call it a day? \r\n\r\n","author":{"url":"https://github.com/gregbrowndev","@type":"Person","name":"gregbrowndev"},"datePublished":"2022-07-29T11:30:09.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/53/code/issues/53"}