funky-eyes commented on PR #8007: URL: https://github.com/apache/incubator-seata/pull/8007#issuecomment-4008934242
> 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. -- 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]
