Support for Postgres transaction syntax in Parser

[AmoebaProtozoa]

Development Task

Description

The SQL standard syntax for starting a transaction is START TRANSACTION, which both MySQL and PostgreSQL supports. They also support syntax like START TRANSACTION + [modifier], where modifier can be READ WRITE, READ ONLY, etc. For the ease of use, they also support use BEGIN instead of START TRANSACTION However, they treat BEGIN + [modifier] differently. (like BEGIN READ WRITE)

In Postgres this is completely fine, BEGIN/BEGIN WORK/BEGIN TRANSACTION are all treated as aliases for START TRANSACTION

MySQL, on the other hand, does not allow modifier after BEGIN, a limitation which TiDB inherits.

In TiDB, the standard way to start new transaction via go code is

txn, err := db.Begin()

which calls underlying go-mysql-driver via go's database module, and return a transaction handle (the txn above). After we switch the driver from lib/pq, because pq starts transaction by BEGIN READ WRITE when no modifier provided, it will cause parser to complain.

Previously in unit tests fixing, we have been solving the problem by skipping Begin()method and execute SQL statement START TRANSACTION; and execute COMMIT; at the end of query block. But since we are not using Begin(), we can't get txn handle. It will cause problem when multiple transactions are nested or intersected.

The ideal solution is for parser to support begin + modifier syntax.

References

PostgreSQL: Documentation: 9.1: BEGIN MySQL :: MySQL 8.0 Reference Manual :: 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements MySQL Difference between BEGIN and START TRANSACTION - Database Administrators Stack Exchange

[RogueJin]

it should update dcparser firstly to understand pg transaction syntax. https://github.com/DigitalChinaOpenSource/DCParser

[AmoebaProtozoa]

The corresponding issue can be find under DCParser https://github.com/DigitalChinaOpenSource/DCParser/issues/20