Master MySQL Transactions, Locks, and Advanced Functions: A Hands‑On Guide

Transaction Control and Lock Statements

MySQL supports different lock granularities: MyISAM and MEMORY use table‑level locking, InnoDB uses row‑level locking, and BDB uses page‑level locking. Page‑level locks sit between table and row locks in speed and concurrency, can cause deadlocks, and have moderate concurrency. Table‑level locks are fast, coarse, and have the lowest concurrency, while row‑level locks are the finest granularity, offer better concurrency but higher overhead and are prone to deadlocks.

Typical lock statements are LOCK TABLES and UNLOCK TABLES. For example: LOCK TABLE cxuan005 READ; While a read lock is held, other sessions can still execute SELECT queries on the same table, but any UPDATE or DELETE from a non‑owner session will block until the lock is released. UNLOCK TABLES; Releasing the lock allows pending write operations to proceed.

Transaction Basics

A transaction groups multiple SQL statements so that they either all succeed (commit) or all fail (rollback). MySQL provides the following statements for transaction management: SET AUTOCOMMIT=0 – disables automatic commit. START TRANSACTION – begins a transaction. COMMIT – commits the current transaction. ROLLBACK – aborts the current transaction.

Example workflow with two client windows:

START TRANSACTION;
INSERT INTO cxuan005(id,info) VALUES (555,'cxuan005');
COMMIT;

After committing in one window, the changes become visible in the other.

Autocommit

By default MySQL runs in autocommit mode, meaning each statement is its own transaction. You can verify the setting with: SHOW VARIABLES LIKE 'autocommit'; To turn off autocommit: SET AUTOCOMMIT=0; Remember to re‑enable it when you are done:

SET AUTOCOMMIT=1;

Manual Commit and Rollback

When autocommit is disabled, you control transaction boundaries explicitly with START TRANSACTION, COMMIT, and ROLLBACK. If an error occurs, issuing ROLLBACK restores the database to its state before the transaction began.

Savepoints

Savepoints let you roll back part of a transaction without aborting the whole transaction.

SAVEPOINT test;
-- do some work
ROLLBACK TO SAVEPOINT test;

Multiple savepoints can be defined; later ones overwrite earlier ones with the same name. To discard a savepoint you can use RELEASE SAVEPOINT.

SQL Mode

SQL Mode determines the SQL syntax rules and data‑validation behavior for a session. Common modes include: ONLY_FULL_GROUP_BY – enforces strict GROUP BY semantics. STRICT_TRANS_TABLES – makes InnoDB enforce strict data validation. NO_ZERO_IN_DATE and NO_ZERO_DATE – control handling of zero dates. ERROR_FOR_DIVISION_BY_ZERO – raises an error on division by zero.

Modes can be set at three scopes:

Session level: SET SESSION sql_mode='...'; Global level: SET GLOBAL sql_mode='...'; Configuration file ( my.cnf) for permanent effect.

Common SQL Tricks

RAND() Function

Generate random rows with:

SELECT * FROM clerk_info ORDER BY RAND() LIMIT 10;

GROUP BY … WITH ROLLUP

Obtain subtotals and grand totals in a single query:

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

Note: ORDER BY cannot be used together with WITH ROLLUP .

Case Sensitivity of Database and Table Names

On Unix, MySQL treats database and table names as case‑sensitive; on Windows they are not. The variable lower_case_table_names controls this behavior.

Foreign Key Support

Only InnoDB supports foreign keys; MyISAM ignores them.

MySQL Built‑in Functions

String Functions

Examples include LOWER(), UPPER(), CONCAT(), SUBSTRING(), REPLACE(), TRIM(), LPAD(), RPAD(), etc.

Numeric Functions

Examples: ABS(), CEIL(), FLOOR(), ROUND(), TRUNCATE(), RAND(), MOD().

Date and Time Functions

Key functions: NOW(), CURDATE(), CURTIME(), DATE_FORMAT(), DATE_ADD(), DATEDIFF(), UNIX_TIMESTAMP(), FROM_UNIXTIME().

Flow Control Functions

Conditional logic can be expressed with IF(), IFNULL(), and CASE … END constructs.

Other Useful Functions

Utility functions include VERSION(), DATABASE(), USER(), MD5(), INET_ATON(), INET_NTOA().

Summary

This article provides a hands‑on walkthrough of MySQL’s advanced features, covering lock mechanisms, transaction control (including autocommit, manual commit, savepoints, and rollback), SQL mode configuration, practical SQL tricks, regular‑expression usage, and a comprehensive catalog of built‑in functions for strings, numbers, dates, flow control, and system information.