Advanced MySQL Topics: Transaction Control, Locking, SQL Mode, Regular Expressions, and Common Functions

Transaction Control and Lock Statements

MySQL supports table‑level, row‑level, and page‑level locks depending on the storage engine (MyISAM/MEMORY, InnoDB, BDB). Table‑level locks are fast but coarse, row‑level locks provide higher concurrency but may cause deadlocks, and page‑level locks fall in between.

Locking is performed with LOCK TABLES and released with UNLOCK TABLES . Example:

lock table cxuan005 read;

While a read lock is held, other sessions can still query the table but cannot modify it:

select * from cxuan005 where id = 111;

Attempting an UPDATE under a read lock from a non‑owner session fails, demonstrating the lock’s effect.

Unlocking

The lock is released with:

unlock tables;

Transaction Control

A transaction groups multiple SQL statements so that they either all succeed or all fail. MySQL defaults to autocommit mode, where each statement is its own transaction. Autocommit can be disabled:

set autocommit = 0;

Manual transaction boundaries are defined with START TRANSACTION , COMMIT , and ROLLBACK . Example of a multi‑statement transaction:

start transaction;
... # one or more statements
commit;

Savepoints allow partial rollbacks:

savepoint test;
... # statements
rollback to savepoint test;

SQL Security Issues

The article briefly discusses SQL injection, emphasizing that developers must validate and parameterize user input to prevent unauthorized database access.

SQL Mode

SQL Mode controls syntax and validation rules. Common modes include ONLY_FULL_GROUP_BY , STRICT_TRANS_TABLES , NO_ZERO_IN_DATE , and others. The mode can be inspected and changed at session, global, or configuration level:

select @@sql_mode;

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE';

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE';

Regular Expressions in MySQL

MySQL provides the REGEXP operator for pattern matching. Examples of common regex constructs ( ^ , $ , . , [...] , [^...] , * , + , ? ) are demonstrated with simple queries.

Common SQL Tricks

Random Sampling : Use RAND() with ORDER BY (and optionally LIMIT ) to retrieve random rows.

SELECT * FROM clerk_info ORDER BY RAND();

GROUP BY WITH ROLLUP : Generates subtotals and grand totals for grouped data.

SELECT name, SUM(salary) FROM clerk_info GROUP BY name WITH ROLLUP;

MySQL Functions Overview

The article lists and exemplifies major function categories:

String functions (e.g., LOWER() , CONCAT() , SUBSTRING() )

Numeric functions (e.g., ABS() , CEIL() , ROUND() )

Date and time functions (e.g., NOW() , DATE_FORMAT() , DATE_ADD() )

Flow control functions (e.g., IF() , CASE )

Other utilities (e.g., VERSION() , DATABASE() , USER() )

Each function is illustrated with a short query and the resulting output.

Conclusion

The article walks readers through practical MySQL advanced topics, showing that “advanced” often means understanding the nuances of transaction isolation, lock granularity, mode settings, and the rich set of built‑in functions that can simplify development.