Sumit6307 commented on PR #8007: URL: https://github.com/apache/incubator-seata/pull/8007#issuecomment-3997913352
> Hi Sumit, > > Thanks for proposing this feature idea. However, as I understand it, ClickHouse currently appears to lack table-level or row-level locking, so it would be impossible to construct correct undo/redo information when using FOR UPDATE to produce undo-log records. Because the undo log is not stored together with the business table, I seriously doubt that committing such a local transaction could achieve the ACID guarantees expected of a traditional relational database. > > Moreover, Seata’s AT mode requires obtaining the primary keys of the tables affected by DML in order to build global locks. ClickHouse writes are asynchronous and usually incur some delay while data is being merged. If a transaction needs to be rolled back during that period, can the primary-key information for the branch within the global transaction be reliably queried, and will the query results match the redo (after-image) content stored in the undo log? > > Can you provide evidence or documentation showing that ClickHouse’s ACID capabilities can be made to work correctly with Seata’s AT mode? Thank you for the detailed technical feedback. You are correct that ClickHouse’s OLAP architecture differs significantly from traditional RDBMS systems. However, Seata’s AT mode can be adapted to work reliably with ClickHouse through the following mechanisms: 1. Seata Global Lock (Logical Locking) Although ClickHouse does not support native SELECT FOR UPDATE row-level locking, Seata AT mode provides isolation through its Global Lock mechanism managed by the Seata Server (Transaction Coordinator). Before a local transaction completes Phase 1, Seata acquires a lock on the corresponding Primary Key in the global lock table. This prevents other Seata-managed transactions from performing conflicting updates on the same records. While this is not physical row-level locking at the database layer, it provides the required logical isolation to prevent dirty writes within Seata-managed distributed transactions. 2. Synchronous Mutations To address the asynchronous write behavior in ClickHouse, this implementation recommends configuring the session parameter: SET mutations_sync = 1 (or 2 in distributed cluster environments) This forces ClickHouse to wait until the ALTER TABLE ... UPDATE/DELETE mutation is persisted before returning control to the application. As a result, the before-image and after-image used by Seata for undo/redo operations remain consistent and reliable. 3. Atomic Log Consistency By combining mutations_sync with Seata’s Phase 1 execution logic, the business data mutation and the corresponding undo_log entry are synchronized from the application perspective. While ClickHouse does not provide traditional cross-table transactional guarantees like OLTP databases, the synchronous mutation behavior ensures that once the call returns successfully, the data state is persisted and can be deterministically rolled back if required during Phase 2. 4. Primary Key Discovery In ClickhouseTableMetaCache, I have implemented logic to correctly identify the Primary Key or ORDER BY columns used in the MergeTree family engines. Since ClickHouse relies on sorting keys rather than conventional primary keys, this ensures that Seata can accurately determine which rows need to be locked and rolled back during distributed transaction processing. Together, these mechanisms allow Seata AT mode to operate in a logically consistent and controlled manner on top of ClickHouse despite its architectural differences from traditional RDBMS systems. -- 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]
