frontend: basic support for `TRANSACTION` statements

[BugenZhao]

Since we are adding support for transactional DDL to enable transactional CDC sources, and we have also added support for DML atomicity after #10077, I believe it's a good opportunity to start working on the TRANSACTION statements in the frontend.

It's worth noting that the reads from Hummock operate under snapshot isolation, so supporting read-only transactions would be relatively easy. However, it's hard to handle "read-you-write" in the current DML architecture, so we may not plan to support read-write transactions in the near future.

Considering that transaction support is not that significant for a streaming database, we can still try our best to pick a subset of the SQL TRANSACTION feature to support. It's not necessary for it to be complete or perfect.

Possible roadmap:

• [x] Support read-only transactions
• [ ] Support write-only transactions for DML (TBD)
• [ ] Support DDL-only transactions for pipeline reusing (#9892)
• [x] Decide the access mode during transaction, or with SET TRANSACTION
• [ ] Handle failures and ABORT/ROLLBACK during transactions (#11510)
• [x] Integrate with pgwire

---

Old issues:

• 2541

• 2595

[github-actions[bot]]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean. Don't worry if you think the issue is still valuable to continue in the future. It's searchable and can be reopened when it's time. 😄