Master MySQL Advanced Features: Locks, Transactions, SQL Modes, and Functions Explained

Lock Statements

MySQL supports three lock granularities: table‑level locks (MyISAM, MEMORY), row‑level locks (InnoDB), and page‑level locks (BDB). Table‑level locks are fast but coarse, row‑level locks offer the finest granularity but may cause deadlocks, and page‑level locks fall in between. The article demonstrates locking a table with LOCK TABLE cxuan005 READ;, showing that other sessions can still read the locked table but cannot perform updates until the lock is released with UNLOCK TABLES;.

Transaction Control

MySQL defaults to autocommit mode, where each statement is committed automatically. Autocommit can be inspected with SHOW VARIABLES LIKE 'autocommit'; and disabled with SET AUTOCOMMIT = 0;. Manual transaction control uses START TRANSACTION;, followed by one or more DML statements, and ends with COMMIT; or ROLLBACK;. The guide shows two concurrent MySQL windows: one holds a read lock, the other can still query but cannot update until the lock is released. It also covers COMMIT AND CHAIN; (commit and immediately start a new transaction) and the use of SAVEPOINT and ROLLBACK TO SAVEPOINT to partially undo work.

SQL Security Issues

SQL injection is highlighted as a common attack where user‑supplied data is concatenated into SQL strings, allowing attackers to read, modify, or delete data. While typical DML statements (SELECT, INSERT, UPDATE, DELETE) do not force an implicit commit, DDL statements (CREATE/DROP/ALTER) and LOCK TABLES do trigger an immediate commit.

SQL Mode

SQL Mode defines the SQL syntax and validation rules MySQL enforces. The current mode can be viewed with SELECT @@sql_mode;. Modes such as ONLY_FULL_GROUP_BY enforce strict GROUP BY rules, while STRICT_TRANS_TABLES makes data validation rigorous. The article shows how to remove a mode temporarily with

SET SESSION sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');

and permanently via the my.cnf file. It explains the three scopes of mode settings: session, global, and configuration.

Regular Expressions

MySQL’s REGEXP operator enables pattern matching. Common constructs are demonstrated: ^ (start of string), $ (end of string), . (any single character), character classes [abc], negated classes [^xyz], quantifiers * (zero or more), + (one or more), and ? (zero or one). Example queries such as SELECT 'aaaabbbccc' REGEXP '^a'; illustrate the results.

Common SQL Tricks

Random row selection is achieved with ORDER BY RAND();. The guide creates a sample table clerk_info and shows random sampling and limiting results. It also covers GROUP BY … WITH ROLLUP to produce hierarchical aggregates, noting that ORDER BY cannot be used together with WITH ROLLUP.

Case Sensitivity

The variable lower_case_table_names controls case sensitivity of database and table names. On Unix‑like systems the default is case‑sensitive (value 0), while Windows defaults to case‑insensitive (value 2). Changing this variable affects cross‑platform migrations, as clerk_info and CLERK_INFO are distinct on Unix but identical on Windows.

Foreign‑Key Support

Only the InnoDB storage engine enforces foreign keys; MyISAM ignores them.

MySQL Built‑in Functions

Functions are grouped into categories:

String functions : LOWER(), UPPER(), CONCAT(), LEFT(), RIGHT(), INSERT(), LTRIM(), RTRIM(), REPEAT(), TRIM(), SUBSTRING(), LPAD(), RPAD(), STRCMP(), REPLACE().

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

Date and time functions : NOW(), CURDATE(), CURTIME(), YEAR(), WEEK(), HOUR(), MINUTE(), MONTHNAME(), UNIX_TIMESTAMP(), FROM_UNIXTIME(), DATE_FORMAT(), DATE_ADD(), DATEDIFF().

Flow‑control functions : IF(), CASE expressions, and other conditional utilities (illustrated in a table image).

Other utilities : VERSION(), DATABASE(), USER(), PASSWORD(), MD5(), INET_ATON(), INET_NTOA().

Each function is accompanied by a short usage example in the original article.