Other Statements

Transaction-related syntax

Default database isolation level

The default isolation level of an M-compatible database is READ COMMITTED, and that of MySQL is REPEATABLE READ.

Only the READ COMMITTED and REPEATABLE READ isolation levels take effect in M-compatible databases.

Transaction-related syntax

Transaction nesting

In M-compatible mode, nested transactions are not automatically committed, but in MySQL, they are automatically committed.

Transaction-related syntax

Autocommit

In M-compatible mode, GaussDB is used for storage and the GaussDB transaction mechanism is inherited. If DDL or DCL is executed in a transaction, the transaction is not automatically committed. In MySQL, if DDL, DCL, management-related, or lock-related statements are executed, the transaction is automatically committed.

Transaction-related syntax

Rollback is required after an error is reported.

If an error is reported for a transaction in an M-compatible database, rollback needs to be performed. There is no such restriction in MySQL.

Transaction-related syntax

Lock mechanism

The M-compatible lock mechanism can be used only in transaction blocks. There is no such restriction in MySQL.

Lock mechanism

Lock mechanism

• After the read lock is obtained, write operations cannot be performed on the current session in MySQL, but write operations can be performed on the current session in an M-compatible database.
• After MySQL locks a table, an error is reported when other tables are read. M-compatible does not have such restriction.
• In MySQL, if the lock of the same table is obtained in the same session, the previous lock is automatically released and the transaction is committed. M-compatible databases do not have this mechanism.
• M-compatible databases allow LOCK TABLE to be used only inside a transaction block, and have no UNLOCK TABLE command. Locks are always released at the end of transactions.

PBE

PBE

• In an M-compatible database, if a PREPARE statement with the same name is repeatedly created, an error is reported, indicating that the statement already exists. You need to delete the existing statement first. In MySQL, the old statement will be overwritten.
• M-compatible databases and MySQL report errors in different phases, such as parsing and execution, during SQL statement execution. PREPARE statements process prepared statements till the parsing phrase. Therefore, in abnormal scenarios in PBE, an M-compatible database may be different from that in MySQL in terms of whether the error is reported in the PREPARE or EXECUTE phase.

Single-line comment syntax

Single-line comment syntax

The single-line comment syntax is consistent with MySQL only when the m_format_behavior_compat_options parameter is set to 'forbid_none_space_comment'.