Sumit6307 commented on PR #8007:
URL: https://github.com/apache/incubator-seata/pull/8007#issuecomment-4015680424
> > Snapshot isolation and conflict detection can be used through
experimental transactions by enabling allow experimental transactions equals 1.
This provides snapshot isolation where, if another transaction modifies the
data after the before image is read but before the local commit occurs,
ClickHouse MVCC will detect the conflict and the transaction will fail. This
effectively shifts the approach from pessimistic locking used in systems like
MySQL to optimistic concurrency control, which aligns better with OLAP database
behavior.
>
> I want to know: if two transactions, TX1 and TX2, both modify the same row
— suppose the original username is "John".
>
> TX1:
>
> ```
> begin T1
>
> select for update (before image) T2
>
> update user set name = 'jackson' where id = 1 T3
>
> after image T6
>
> commit T7
> ```
>
> TX2:
>
> ```
> begin T1
>
> select for update (before image) T4
>
> update user set name = 'Johnny' where id = 1 T8
>
> after image T9
>
> commit T10
> ```
>
> Can both transactions succeed? At T4, when TX2 reads the image, is the
username "John" or "jackson"? If TX2 fails, that’s fine because its local
transaction will simply roll back. However, if both transactions can commit
locally, the correct username should be "jackson" — if it remains "John" that
would be a serious problem. In a traditional relational database, the SELECT
... FOR UPDATE at T4 would be blocked until after T7; if it is not blocked and
instead reads the data directly, then if TX2’s global transaction later decides
to roll back, it could effectively erase the result that TX1 already committed.
@funky-eyes
That is a very sharp observation regarding the T4 race condition. You are
correct that without pessimistic locking, TX2 could capture a stale
before-image ('John') before TX1 commits.
However, I've researched the concurrency behavior of ClickHouse's
experimental transactions, and here is how this implementation maintains
correctness:
> Write-Conflict Detection: Since ClickHouse 22.x+ uses Snapshot Isolation
for transactions, it implements First-Committer-Wins. If TX1 and TX2 both read
'John' and TX1 commits first, ClickHouse will detect that the snapshot for TX2
is now stale. When TX2 attempts to commit its update at T10, ClickHouse will
throw an Update Conflict error and force TX2 to roll back locally.
> Rollback Protection: Because TX2 fails its local transaction at T10, it
never reports a 'Success' to the Seata TC. Therefore, Seata will never trigger
a global rollback for TX2 using the stale before-image. The data 'jackson'
committed by TX1 remains safe.
> The Role of Global Lock: Even if TX2 were to wait for the Global Lock, the
ClickHouse MVCC still ensures that any write based on an old snapshot is
rejected.
>Pragmatic Recommendation: For production users, we specifically recommend
combining this AT mode with:
>allow_experimental_transactions = 1 for the conflict
detection mentioned above.
>mutations_sync = 1 to close the asynchronous window as
much as possible.
Does the behavior of ClickHouse's 'First-Committer-Wins' Snapshot Isolation
address your concern about the T4 image capture? I am happy to add these
technical details to the README or a Clickhouse-AT-Mode.md guide to help users
understanding the isolation level.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
