Master MySQL Transactions, Locks, and Advanced SQL Techniques

Transaction Control and Lock Statements

MySQL supports three lock granularities: table‑level locking (MyISAM, MEMORY), row‑level locking (InnoDB), and page‑level locking (BDB). Table‑level locks are fast but coarse, row‑level locks provide higher concurrency but can cause deadlocks, and page‑level locks fall between the two.

Lock Statements

The LOCK statement can lock a table for the current session. Example: lock table cxuan005 read; After acquiring a read lock, other sessions can still execute SELECT queries on the table but cannot perform updates.

Attempting an update while another session holds a read lock results in the operation being blocked:

update cxuan005 set info='cxuan' where id = 111;

Unlock Statements

Releasing a lock is done with: unlock tables; Updates blocked by the lock resume once the lock is released.

Transaction Basics

A transaction groups multiple SQL statements so that they either all succeed or all fail. MySQL supports SET AUTOCOMMIT, START TRANSACTION, COMMIT, and ROLLBACK.

Automatic Commit

By default MySQL runs in autocommit mode, where each statement is committed immediately. You can check the setting with: show variables like 'autocommit'; To disable autocommit: set autocommit = 0; Re‑enable it later with set autocommit = 1;.

Manual Commit

Typical manual transaction flow:

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

If an error occurs, ROLLBACK will undo the changes.

Commit and Chain

The COMMIT AND CHAIN command commits the current transaction and immediately starts a new one:

start transaction;
insert into cxuan005(id,info) values (555,'cxuan005');
commit and chain;

Savepoints

Savepoints allow partial rollbacks within a transaction:

savepoint test;
... -- more statements
rollback to savepoint test;

Note that RELEASE SAVEPOINT removes a savepoint.

Transaction vs. Non‑Transaction Tables

Only storage engines that support transactions (e.g., InnoDB) can be used with COMMIT and ROLLBACK. Non‑transactional engines like MyISAM ignore these commands.

SQL Security

SQL injection attacks exploit unsanitized user input to execute arbitrary SQL, potentially exposing or modifying data. Developers should validate inputs and use prepared statements to mitigate this risk.

SQL Mode

SQL Mode determines the SQL syntax and validation rules MySQL applies. You can view the current mode with: select @@sql_mode; Common modes include ONLY_FULL_GROUP_BY, which enforces strict GROUP BY rules, and STRICT_TRANS_TABLES, which enforces strict data validation.

To remove a mode temporarily:

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

Permanent changes belong in my.cnf under the sql-mode option.

Scope of SQL Mode

Session level: set session sql_mode='...'; Global level: set global sql_mode='...'; Configuration file: add sql-mode="..." under [mysqld] and restart MySQL.

Regular Expressions in MySQL

MySQL provides the REGEXP operator for pattern matching. Examples:

select 'aaaabbbccc' regexp '^a';   -- matches start of string
select 'aaaabbbccc' regexp 'c$';   -- matches end of string
select 'berska' regexp '.s';
select 'whosyourdaddy' regexp '[abc]';
select 'x' regexp '[^xyz]';
select 'aabbcc' regexp 'd*';   -- matches zero or more 'd'
select 'aabbcc' regexp 'd+';   -- matches one or more 'd'

Common SQL Tricks

Random Row Selection

Use RAND() with ORDER BY to retrieve rows in random order:

select * from clerk_info order by rand();

GROUP BY WITH ROLLUP

Aggregates with subtotals can be obtained using WITH ROLLUP:

select name, sum(salary) from clerk_info group by name with rollup;

Note: ORDER BY cannot be combined with WITH ROLLUP.

Case Sensitivity

Database and table name case sensitivity depends on the operating system and the lower_case_table_names system variable.

Foreign Keys

Only InnoDB supports foreign keys; MyISAM ignores them.

MySQL Built‑in Functions

String Functions

LOWER(str)

, UPPER(str) – change case. CONCAT(...) – concatenate strings. LEFT(str,n), RIGHT(str,n) – extract characters. INSERT(str,pos,len,newstr) – replace a substring. LTRIM(str), RTRIM(str) – trim spaces. REPEAT(str,n) – repeat a string. TRIM(str) – remove leading/trailing spaces. SUBSTRING(str,pos,len) – extract a substring. LPAD(str,n,pad), RPAD(str,n,pad) – pad strings. STRCMP(s1,s2) – compare strings. REPLACE(str,old,new) – replace occurrences.

Numeric Functions

ABS(x)

– absolute value. CEIL(x), FLOOR(x) – round up/down. MOD(x,y) – modulus. ROUND(x,dec) – round to decimals. TRUNCATE(x,dec) – truncate without rounding. RAND() – random number between 0 and 1.

Date and Time Functions

NOW()

– current timestamp. CURDATE(), CURTIME() – current date or time. UNIX_TIMESTAMP(date), FROM_UNIXTIME(ts) – convert to/from Unix epoch. DATE_FORMAT(date,fmt) – format date. DATE_ADD(date,INTERVAL n unit) – add interval. DATEDIFF(date1,date2) – days between dates.

Flow Control Functions

Functions such as IF(), CASE, and COALESCE() enable conditional logic inside queries.

Other Useful Functions

VERSION()

– MySQL version. DATABASE() – current database name. USER() – current user. PASSWORD(str), MD5(str) – hashing. INET_ATON(ip), INET_NTOA(num) – IP address conversion.

The article includes numerous screenshots illustrating each command’s output; these images are retained to aid understanding.