SQL DDL, DML, DCL, Constraints, Transactions and Locks

SQL

SQL is a declarative language for defining relational structures, querying and changing data, controlling access and managing transactions.

DDL

Data Definition Language statements create or alter schema objects and constraints, such as CREATE TABLE and ALTER TABLE.

DML

Data Manipulation Language statements retrieve or change rows, principally SELECT, INSERT, UPDATE and DELETE.

DCL

Data Control Language statements grant or revoke privileges from database principals.

TCL

Transaction Control Language statements establish transaction boundaries and outcomes through operations such as COMMIT, ROLLBACK and SAVEPOINT.

data type

A declared domain that determines valid values, representation, operations, comparison and storage behavior for a column or expression.

constraint

A constraint defines a condition every feasible decision must satisfy.

primary and foreign key

A primary key uniquely identifies a row; a foreign key enforces a reference to an existing candidate key.

transaction

A transaction groups database operations into one atomic commit or rollback under a chosen isolation level.

ACID

Atomicity makes a transaction all-or-nothing, consistency preserves declared rules.

lock and isolation

Locks coordinate conflicting access, while isolation defines which effects of concurrent transactions may become visible.

SQL Command Families

SQL is a declarative language for defining, querying, changing, protecting and controlling relational data.

Commands are commonly grouped as:

• DDL defines structure: CREATE, ALTER, DROP and TRUNCATE;
• DML changes rows: INSERT, UPDATE and DELETE;
• DQL retrieves rows through SELECT;
• DCL manages privileges through GRANT and REVOKE;
• TCL controls transactions through COMMIT, ROLLBACK and SAVEPOINT.

The boundaries are useful vocabulary, although products may classify individual commands differently. The actual transactional behavior of each MySQL command matters more than its category label.

Consistency

Consistency means a correct transaction takes the database from one valid state to another while preserving relevant invariants.

The DBMS enforces declared primary keys, foreign keys, checks and type rules. Application and procedural logic must enforce business rules that are not fully represented as constraints.

Consistency is not automatic merely because COMMIT succeeds. A transaction that adds money to both accounts can satisfy SQL constraints while violating the conservation rule of a transfer.

DDL and Transactions

Many MySQL DDL statements cause implicit commits. Creating, altering, dropping or truncating a table should not be mixed casually with a business transaction under the assumption that ROLLBACK reverses everything.

Atomic DDL improves crash consistency for supported schema operations, but it does not make ordinary DDL part of user-controlled transactional rollback.

Run schema migrations as controlled deployment steps and verify behavior for the exact MySQL version.

MySQL DDL and Transactions

Many MySQL DDL statements cause implicit commits before and after execution. A later ROLLBACK should not be assumed to undo CREATE, ALTER, DROP or TRUNCATE.

Modern atomic DDL improves crash safety for supported operations, but atomic execution is not the same as user-controlled transactional rollback.

Keep schema migration separate from ordinary business transactions. Test migration behavior on the exact MySQL version and storage engine in use.

Locks and Waiting

InnoDB uses locks to coordinate conflicting changes. Row-level locking improves concurrency, while indexes help locate and lock a narrow set.

Transactions can wait when another transaction holds an incompatible lock. A lock wait timeout reports excessive waiting. The correct response is usually rollback and controlled handling, not pretending the operation succeeded.

Keep transactions short, access rows in a consistent order, use selective indexed predicates and avoid unnecessary user interaction while locks are held.

Foreign Keys

CREATE TABLE order_header ( order_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_id BIGINT NOT NULL, ordered_at DATETIME NOT NULL, CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE RESTRICT ON DELETE RESTRICT );

A foreign key requires each non-NULL child value to match a referenced candidate key. Referencing and referenced columns need compatible definitions. InnoDB supports enforcement; storage-engine choice matters.

Referential actions such as CASCADE, SET NULL, RESTRICT and NO ACTION must match entity lifecycle. SET NULL also requires nullable child columns.

Transaction Boundaries and Autocommit

MySQL sessions commonly begin with autocommit enabled. Each standalone transactional statement commits when it succeeds unless an explicit transaction is active.

START TRANSACTION begins an explicit unit. COMMIT makes its effects permanent under the engine's durability model. ROLLBACK ends it and undoes its uncommitted effects.

Do not leave transactions open while waiting for user input or making slow network calls. Long transactions retain locks or old row versions, increase contention and can interfere with cleanup.

Designing a Transaction

Define one business invariant and the statements needed to preserve it. Begin the transaction as late as possible. Validate current database state under the required locking or conflict strategy. Check every affected-row count and error.

Commit only after the complete unit succeeds. Roll back on any failure and ensure the connection is returned to a clean state. For retryable conflicts, repeat the whole idempotent unit with a bound.

After correctness, measure contention and optimize indexes or transaction length. Transactions, constraints, privileges and the storage engine work together: none can compensate for an incorrectly chosen business boundary.

Primary Keys

A primary key uniquely and non-nullably identifies every row:

PRIMARY KEY (order_id)

or:

PRIMARY KEY (order_id, line_number)

A table has at most one primary-key constraint, but the constraint may contain several columns. MySQL indexes the primary key and InnoDB uses it in clustered storage, making compact and stable keys valuable.

Identity should not depend on row position or current display order.

InnoDB

InnoDB provides ACID transactions, crash recovery, row-level locking, multiversion concurrency control and foreign-key enforcement.

Its tables use a clustered primary-key organization: the primary key determines the clustered record organization and secondary index entries contain the primary-key value. Very wide or changing primary keys therefore enlarge secondary indexes.

If no suitable primary key is defined, InnoDB chooses another internal organization. Defining a stable explicit primary key is better for identity and storage.

Isolation

Isolation governs how concurrent transactions observe and interfere with each other. Complete serial execution would provide strong separation but poor concurrency.

Isolation levels permit different implementation tradeoffs while preventing specified phenomena. Application code must choose an appropriate level and use locking or conflict checks where the business decision depends on current shared state.

Isolation does not mean transactions run physically one after another. It defines allowed observations and outcomes.

Isolation Levels

The standard levels are:

Level	Dirty reads	Nonrepeatable reads	Phantoms
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	prevented	possible	possible
REPEATABLE READ	prevented	prevented by the model	product-specific handling
SERIALIZABLE	prevented	prevented	prevented

This table is a conceptual minimum. MySQL uses multiversion concurrency control and locking details that must be understood from its actual version and statement type.

InnoDB's default is commonly REPEATABLE READ, but deployments can change it.

CHECK Constraints

CHECK expresses a Boolean condition:

quantity INT NOT NULL, unit_price DECIMAL(12, 2) NOT NULL, CHECK (quantity > 0), CHECK (unit_price >= 0)

In modern MySQL, a row is rejected when the check evaluates FALSE. UNKNOWN from NULL does not itself reject the row, so NOT NULL is required when the value must exist.

Checks should be deterministic row rules. Rules requiring other rows may need keys, foreign keys, transaction logic or carefully designed triggers.

Transactions

A transaction is a sequence of database operations treated as one logical unit. A bank transfer must debit one account and credit another as one business action:

START TRANSACTION;

UPDATE account SET balance = balance - 500 WHERE account_id = 10 AND balance >= 500;

UPDATE account SET balance = balance + 500 WHERE account_id = 20;

COMMIT;

This outline is incomplete until the application verifies that both accounts exist, the debit changed exactly one row, every statement succeeded and business rules remain valid. A transaction provides mechanisms; correct boundaries and checks remain part of the design.

Building a Reliable Schema

Translate stable business rules into types and constraints. Use NOT NULL for required attributes, primary and unique keys for identity, foreign keys for references, checks for row-level conditions and deliberate defaults.

Choose exact types for exact values, complete Unicode settings for user text and temporal types for time. Name constraints and migrations clearly. Inspect generated DDL and catalog metadata rather than relying on tool displays alone.

A reliable schema prevents invalid states at the shared data boundary. Application validation remains valuable for guidance and user experience, while database constraints ensure that every authorized client observes the same rules.

Creating a Database and Table

CREATE DATABASE sales; USE sales;

CREATE TABLE customer ( customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(254) NOT NULL, full_name VARCHAR(120) NOT NULL, joined_on DATE NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE', CONSTRAINT uq_customer_email UNIQUE (email), CONSTRAINT chk_customer_status CHECK (status IN ('ACTIVE', 'INACTIVE')) );

CREATE TABLE gives every column a name and data type, then adds rules that valid rows must satisfy. Naming table-level constraints produces clearer diagnostics and makes later schema changes easier.

DROP and TRUNCATE

DROP TABLE removes the table definition and its data:

DROP TABLE obsolete_data;

TRUNCATE TABLE removes all rows while retaining the table definition. It is a DDL operation in MySQL, has different logging and identity behavior from DELETE and does not accept a WHERE clause.

DELETE is a DML statement that can remove selected rows and participates in transaction behavior according to the engine. These commands are not interchangeable merely because all can make rows disappear.

Storage Engines

MySQL storage engines implement table storage and important behavior behind the SQL layer. Tables in one server can use different engines, though mixing them in a business transaction can weaken guarantees.

Inspect the engine through metadata rather than assuming defaults. Engine choice affects transactions, locking, recovery, foreign keys, indexing and specialized capabilities.

For ordinary transactional applications, InnoDB is the standard choice.

Deadlocks

A deadlock forms when transactions wait in a cycle. Transaction A holds one resource and waits for B, while B holds another and waits for A.

InnoDB detects deadlocks and aborts a victim so the other transaction can continue. Deadlocks are possible in correct concurrent systems and should be handled.

Retry the complete appropriate business transaction, not only the final failed statement. Use bounded retries with fresh transaction state. Consistent lock order and smaller transactions reduce risk but cannot guarantee elimination.

Consistent and Locking Reads

A normal InnoDB SELECT often performs a nonlocking consistent read from a snapshot. SELECT ... FOR UPDATE performs a locking read for rows the transaction intends to update. SELECT ... FOR SHARE obtains shared-style protection where supported.

SELECT balance FROM account WHERE account_id = 10 FOR UPDATE;

A locking read must run inside an intentional transaction. Its effectiveness depends on predicates and indexes; a broad scan can lock more rows or ranges than expected.

UNIQUE Constraints

UNIQUE enforces candidate-key or business uniqueness:

CONSTRAINT uq_employee_email UNIQUE (email)

MySQL generally permits several NULL values in a nullable unique column because NULL values are not treated as equal for this constraint. Combine UNIQUE with NOT NULL when every row must possess one unique value.

A composite unique constraint applies to the combination. It does not make each participating column individually unique.

Choosing Data Types

Select a type from the meaning and required range of the value. A correct type prevents invalid representations, supports suitable operations and avoids wasted storage.

Do not choose every identifier as text or every number as the largest numeric type. Also do not select a narrow type without considering future range. Changing a heavily used column later can be expensive.

MySQL modes influence whether invalid or out-of-range values cause errors or conversions. Production systems should use strict behavior and validate important domain rules with constraints.

Roles and Least Privilege

A role groups privileges by responsibility. Accounts receive appropriate roles, simplifying review and revocation.

Least privilege gives each account only the operations and objects required for its task. A reporting user may need SELECT on controlled views, while an application service may need limited procedure execution or DML on selected tables.

Avoid broad wildcard grants when a narrower scope works. Separate migration privileges from runtime application privileges.

DELETE

DELETE removes matching rows:

DELETE FROM customer WHERE customer_id = 42;

Foreign keys may reject the deletion, cascade it or nullify child references. Understand these effects before deleting parent rows.

Deleting all rows with DELETE is different from TRUNCATE in logging, transaction, trigger, foreign-key and identity behavior. Select the command from its defined semantics.

Lost Updates

A lost update can occur when two sessions read the same value, compute separate replacements and one write overwrites the other.

Atomic SQL arithmetic avoids a read-modify-write race:

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 50 AND quantity > 0;

The affected-row count indicates whether stock was available.

Optimistic locking uses a version:

UPDATE document SET content = ?, version = version + 1 WHERE document_id = ? AND version = ?;

Zero affected rows signals a conflict requiring a refresh or retry.

Phantom Reads

A phantom occurs when repeating a predicate query produces a different qualifying row set because another transaction inserted, deleted or changed matching rows.

For example, a transaction counts open reservations for a date. Another transaction inserts a qualifying reservation. Repeating the count reveals a new phantom row.

Preventing a row from changing is not enough to protect a predicate range. MySQL InnoDB can use next-key and gap locking for locking reads under applicable isolation and indexes.

Nonrepeatable Reads

A nonrepeatable read occurs when a transaction reads one row, another transaction commits a change to that row and the first transaction rereads it and sees a different value.

READ COMMITTED can allow this because each statement may observe a newer committed snapshot. REPEATABLE READ aims to keep consistent reads stable within the transaction.

When the transaction intends to update a row after checking it, a locking read or optimistic version condition may be needed. Snapshot consistency alone does not reserve the row.

Dirty Reads

A dirty read occurs when one transaction reads changes made by another transaction that has not committed. If the writer rolls back, the reader acted on a value that never became part of committed history.

The READ UNCOMMITTED level can permit dirty reads. Higher standard isolation levels prevent them.

Dirty reads rarely suit business decisions because later rollback can invalidate calculations, notifications and external actions based on the temporary value.

SAVEPOINT

A savepoint marks an intermediate point:

START TRANSACTION; UPDATE ...; SAVEPOINT after_header; INSERT ...; ROLLBACK TO SAVEPOINT after_header; COMMIT;

Rollback to a savepoint undoes work after that marker while keeping the surrounding transaction active. RELEASE SAVEPOINT removes the marker.

A savepoint is not a commit, backup or independent nested transaction. The earlier work remains uncommitted until the outer transaction commits.

AUTO_INCREMENT

AUTO_INCREMENT generates numeric identifiers:

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

Generated values are convenient surrogate keys. They are not guaranteed to be gap-free. Rolled-back inserts, failed statements, server behavior and concurrent work can leave gaps.

Do not use an auto-increment sequence where consecutive numbering is a legal requirement. Such numbering needs a separately designed transactional process.

MyISAM and Other Engines

MyISAM is a nontransactional engine with table-level locking and no InnoDB-equivalent rollback or foreign-key enforcement. It remains relevant for understanding legacy tables but is unsuitable for most new transactional application data.

MEMORY stores data primarily in memory and loses it on restart; it has specialized limits and locking behavior. CSV exposes comma-separated storage with limited features. ARCHIVE targets compressed archival patterns.

Engine selection should follow documented requirements, failure behavior, concurrency and maintenance needs rather than a simplistic speed claim.

UPDATE

UPDATE changes rows that satisfy a predicate:

UPDATE customer SET status = 'INACTIVE' WHERE customer_id = 42;

Without WHERE, every row is targeted. First run an equivalent SELECT when forming a high-impact update. In application code, use transactions and inspect the affected-row count.

Assignments conceptually use the old row values according to statement semantics and every resulting row must satisfy constraints.

INSERT

Always name target columns:

INSERT INTO customer (email, full_name, joined_on) VALUES ('anita@example.com', 'Anita Rao', '2026-02-10');

Omitted columns receive permitted defaults, generated values or NULL. The statement fails when a required column has no supplied value or default.

Multirow INSERT reduces round trips:

INSERT INTO category (category_name) VALUES ('Books'), ('Music'), ('Games');

Constraints are checked for inserted rows and transaction behavior controls visibility and recovery.

GRANT OPTION

GRANT OPTION allows a recipient to grant certain privileges to other accounts. It is a delegation capability and should be restricted.

An account that only queries data rarely needs the power to authorize new readers. Delegated privileges expand the paths that administrators must audit and revoke.

Use centralized roles and controlled administration instead of distributing GRANT OPTION for convenience.

Atomicity

Atomicity means the transaction takes effect as a whole or has no effect. If a transfer fails after the debit, rollback must prevent the database from retaining only half the transfer.

Atomicity applies to transactional operations on suitable storage engines. It does not automatically undo an email already sent, a message delivered to an external service or a file written outside the database.

Coordinating external effects requires patterns such as transactional outboxes, idempotent consumers and compensating operations.

Integer Types

MySQL supplies TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT in signed and unsigned forms. The type determines storage and range.

An identifier is not automatically a measurement. Arithmetic on customer_id has little business meaning even though it is stored as an integer.

UNSIGNED expands the nonnegative range, but foreign-key columns must use compatible types and signedness. Display width syntax historically associated with integers does not limit the numeric range and should not be mistaken for validation.

DEFAULT

A default supplies a value when an INSERT omits the column:

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

A default does not generally replace an explicitly supplied NULL when NULL is allowed. It also does not validate meaning. A default status of ACTIVE is appropriate only if that is the correct state for every new row that omits status.

Defaults can use permitted constants or expressions according to the MySQL version and type.

Durability

Durability means that after successful commit, changes survive failures covered by the DBMS configuration and storage model.

InnoDB uses redo logging and coordinated flushing to recover committed changes. Durability strength depends on server settings, storage reliability, replication mode and backup strategy.

Durability is not the same as backup. A committed accidental deletion is durable too. Backups, point-in-time recovery, replication and tested restore procedures address broader loss scenarios.

GRANT and REVOKE

GRANT assigns privileges:

GRANT SELECT, INSERT, UPDATE ON sales.* TO 'sales_app'@'10.%';

REVOKE removes privileges:

REVOKE DELETE ON sales.* FROM 'sales_app'@'10.%';

Privileges can apply at global, database, table, column, routine and other scopes. Verify effective privileges rather than assuming one statement removed permissions inherited through another grant or role.

ALTER TABLE

ALTER TABLE changes an existing definition:

ALTER TABLE customer ADD COLUMN phone VARCHAR(30) NULL;

ALTER TABLE customer ADD CONSTRAINT uq_customer_phone UNIQUE (phone);

It can add, modify, rename or drop columns and constraints. The exact syntax and whether an operation is instant, in-place or table-copying depend on the MySQL version, engine and change.

Before adding a stricter constraint, inspect and repair existing rows that violate it. Schema migration should include compatibility, deployment order, locking and rollback planning.

Character Data

CHAR(n) is fixed length, while VARCHAR(n) stores variable-length character data up to a limit. CHAR can suit genuinely fixed codes; VARCHAR suits names, email addresses and varying labels.

TEXT types store larger textual values. They have different indexing, default and storage considerations depending on MySQL version.

The declared length is interpreted with the character set and storage depends on encoded bytes. Choose limits from domain rules, not arbitrary defaults such as VARCHAR(255) for every field.

Character Sets and Collations

A character set defines how characters are encoded. utf8mb4 supports the full Unicode range and is the usual modern MySQL choice.

A collation defines comparison and ordering rules. It can control case sensitivity, accent sensitivity and linguistic ordering. Under a case-insensitive collation, two differently cased strings may compare equal and conflict with a UNIQUE constraint.

Character set and collation can be set at server, database, table or column levels. Mixed settings can force conversion and cause surprising comparison results.

Exact and Approximate Numbers

DECIMAL(p, s) stores exact fixed-point values. p is total precision and s is digits after the decimal point.

price DECIMAL(12, 2) NOT NULL

DECIMAL is appropriate for money and quantities requiring exact decimal arithmetic.

FLOAT and DOUBLE store approximate binary floating values. They suit scientific measurements where range and performance matter more than exact decimal representation. Equality comparisons and repeated financial calculations can surprise when approximate types are used.

A CHECK constraint can enforce range:

CHECK (price >= 0)

Binary and Large Objects

BINARY and VARBINARY store byte strings. BLOB types store larger binary values. They are appropriate for hashes, encoded identifiers, compressed data or file content.

Storing large files in a database gives transactional and authorization integration but increases database size, backup cost and transfer load. Another design stores files in object storage and records controlled references and metadata in the database.

Binary values do not use character collation. Do not place arbitrary bytes in a text column.

Date and Time Types

DATE stores calendar dates, TIME stores time values or durations within its supported semantics, DATETIME stores a date and time without automatic time-zone conversion and TIMESTAMP commonly represents an instant with session time-zone conversion behavior.

YEAR stores a year. Choose it only when a year alone is the fact.

Store temporal values using temporal types rather than formatted strings. This enables validation, ordering, arithmetic and date functions. Decide explicitly whether a value is a local civil time or a global instant and handle application time zones consistently.

NULL

NULL represents missing, unknown or inapplicable information. It is not zero, an empty string or the text “NULL.”

Comparisons with NULL use three-valued logic. Use IS NULL and IS NOT NULL rather than = NULL.

NOT NULL states that every row must provide a known applicable value for the column. Use it whenever absence is not meaningful. Excessive nullable columns can signal that several entity types have been combined in one table.

JSON and Specialized Types

MySQL JSON validates JSON documents and provides path operations. It is useful for sparse or evolving subordinate attributes, but core identifiers, relationships and frequently constrained properties usually belong in ordinary typed columns.

ENUM restricts a value to a declared list and SET permits a declared combination. These types can be convenient but couple allowed values to schema changes. A reference table is often better when values have labels, lifecycle, ordering or additional properties.