MySQL Storage Engines, Lock Types, and Security Practices

047 MySQL Database Engine Types

Since MySQL 5.5.5, InnoDB has been the default storage engine; earlier versions used MyISAM, and before that ISAM. The engines you can use depend on how MySQL was compiled. By default MySQL supports three engines: ISAM, MyISAM, and HEAP, with optional InnoDB and BerkeleyDB (BDB).

ISAM is a well‑defined, time‑tested table‑management method designed for workloads where reads far outnumber writes. It offers fast read performance with low memory and storage usage, but it lacks transaction support and fault tolerance—if the disk fails, the data files cannot be recovered. Regular backups are required for critical applications, and MySQL’s replication features can assist with backup strategies.

MYISAM is the ISAM‑derived default engine before MySQL 5.5. It adds many indexing and field‑management features and uses table‑level locking to optimise concurrent reads and writes, at the cost of needing frequent OPTIMIZE TABLE commands to reclaim space. MYISAM also provides utilities such as myisamchk for repairing tables and myisampack for reclaiming space.

MYISAM’s emphasis on fast reads explains why MySQL is popular for web development, where most operations are read‑heavy; consequently many shared‑hosting providers only allow MYISAM tables.

HEAP stores temporary tables entirely in memory, making it faster than ISAM and MYISAM. However, the data is volatile and will be lost if not saved before shutdown. HEAP does not waste space when rows are deleted and is useful for SELECT‑based manipulations, but tables should be dropped when no longer needed.

INNODB and BERKELEYDB (BDB) are the engines that give MySQL its flexibility. They address the lack of transaction support and foreign‑key constraints in ISAM and MYISAM. Although slower than the earlier engines, they provide full ACID compliance, row‑level locking, and foreign‑key enforcement, making them essential when those features are required.

048 MySQL Lock Types

Locks can be classified by type into shared locks, exclusive locks, intention‑shared locks, and intention‑exclusive locks.

By granularity, locks are divided into row locks and table locks.

In MySQL, the transaction mechanism is largely implemented by the storage engine. The MySQL server itself only provides table locks, while InnoDB implements row locks (record locks on indexed rows), gap locks (locks on the gaps between index records), and next‑key locks (a combination of record and preceding gap locks). These locks enable repeatable‑read (RR) isolation; gap locks work only under RR isolation, and next‑key locks combine record and gap locking.

Note: For update operations (which do not acquire read locks), only index‑based access may acquire row‑level locks; otherwise a write lock is applied to each row of the clustered index, effectively acting as a table‑level lock.

If multiple physical records map to the same index entry, concurrent access can cause lock conflicts.

When a table has multiple indexes, different transactions can lock different rows using different indexes, and InnoDB may simultaneously hold row locks on the clustered index data.

Under MVCC (multi‑version concurrency control), reads never block other operations and writes never block reads because reads do not acquire locks.

Shared lock : Acquired by a read operation; other sessions can also acquire shared locks but cannot obtain exclusive locks, so the data can be read but not modified.

Exclusive lock : Acquired by a write operation; no other session can obtain any lock on the same table or row, typical for UPDATE statements within a transaction.

Intention shared lock (IS) : A transaction that intends to acquire row‑level shared locks must first obtain an IS lock on the table.

Intention exclusive lock (IX) : A transaction that intends to acquire row‑level exclusive locks must first obtain an IX lock on the table.

052 How to Improve MySQL Security

1. Use an SSH tunnel to encrypt client‑server communication when the connection traverses an untrusted network.

2. Change user passwords with the SET PASSWORD statement, e.g., UPDATE mysql.user SET password = PASSWORD('newpwd'); FLUSH PRIVILEGES; .

3. Protect against eavesdropping, tampering, replay, and denial‑of‑service attacks by applying ACL‑based access controls and enabling SSL where possible.

4. Restrict all users except root from accessing the mysql.user table; store passwords encrypted and avoid exposing them.

5. Use GRANT and REVOKE statements to manage user privileges.

6. Store passwords using one‑way hash functions such as MD5 or SHA1 instead of plain text.

7. Avoid dictionary words when creating passwords.

8. Place the database behind a firewall or in a DMZ to block roughly 50 % of external threats.

9. Block external access to port 3306 via firewall rules; test with tools like nmap or telnet.

10. Validate and escape all user‑supplied input to prevent SQL injection (e.g., encode special characters like quotes, #, and %).

11. Enforce size limits on data sent to MySQL.

12. Applications should connect with limited‑privilege accounts, granting only the necessary permissions.

13. Use proper escaping functions in all programming interfaces (C/C++, PHP, Perl, Java, JDBC, etc.) and prefer SSL/SSH for encrypted transmission.

14. Monitor traffic with tools such as tcpdump and strings to verify security of data in transit.

15. Disable symbolic links with the --skip-symbolic-links option.

16. Ensure only the database service user has read/write permissions on MySQL directories.

17. Do not grant PROCESS or SUPER privileges to non‑admin users; use mysqladmin processlist to monitor active queries.

18. Restrict file permissions so only administrators can read/write files, preventing unauthorized LOAD DATA attacks.

19. Use IP addresses instead of DNS names in the host‑allow list if the DNS provider is untrusted.

20. Limit the number of concurrent connections per account with the max_user_connections variable.

21. The GRANT statement also supports resource‑limit options.

22. Disable local data loading with --local-infile=0 and consider --skip-grant-tables only for emergency recovery, re‑enabling privileges with FLUSH PRIVILEGES afterwards.

23. To recover from error 1045 (access denied for root), start mysqld with --skip-grant-tables , then update the password using UPDATE mysql.user SET password = PASSWORD('newpassword') WHERE user='root'; FLUSH PRIVILEGES; and restart the server.