Mixing Active Record & mini_sql leads to unexpected behavior

AI-generated summary

A potential issue has been identified when mixing Active Record and mini_sql in Discourse. concerto discovered that incrementing the version of reviewables within a transaction using increment_version! (which utilizes mini_sql) is not actually part of the transaction, but rather occurs before the transaction begins. This is due to Active Record’s lazy feature, which issues the BEGIN statement right before the first query inside the transaction.

The result is that the version increment is not atomic with the transaction, potentially breaking guarantees provided by optimistic locking. sam agrees that this issue is worth fixing and suggests teaching mini_sql to inform Active Record about its actions.

When going through some SQL logs, I noticed that the some queries are not coherent with what the code intend to do due to mixing usage of Active Record & mini_sql.

When trying to update some reviewables, we increment the version inside a transaction to prevent some possible race conditions (as the code snippet below shows). Although it appears that the increment is part of the transaction, it is actually not.

Reviewable.transaction here is a Active Record feature, while increment_version! uses mini_sql:

Active Record is not aware of what mini_sql does. It seems that Active Record uses some lazy feature, in which the BEGIN is issued right before the first query inside the transaction is issued. So version is incremented first and then the transaction begins right before the first query from Active Record is issued. It become something like this:

UPDATE reviewables SET version = version + 1 WHERE version=version AND id = reviewable.id RETURNING version;
BEGIN;
...

With the version increment outside transaction, some guarantee given by optimistic locking won’t be achieved. Because the increment is not atomic with the transaction.

4 Likes

Interesting, we should teach mini sql to inform AR, certainly worth fixing

3 Likes