Session 5.4 – DCL and Transaction Control

Introduction to DCL

Data Control Language (DCL) is a subset of SQL used to control access to data in a database. DCL commands allow database administrators to manage user permissions and security.

Main DCL Commands

GRANT: Gives users access privileges to database objects
REVOKE: Removes access privileges from users

Note: DCL commands are crucial for database security and access control in multi-user environments.

User Management

MySQL allows you to create and manage multiple user accounts with different privilege levels.

Creating Users

-- Create a user with password
CREATE USER 'john'@'localhost' IDENTIFIED BY 'secure_password';

-- Create user accessible from any host
CREATE USER 'jane'@'%' IDENTIFIED BY 'secure_password';

-- Create user from specific IP
CREATE USER 'admin'@'192.168.1.100' IDENTIFIED BY 'admin_password';

-- Create user with password expiration
CREATE USER 'temp_user'@'localhost'
IDENTIFIED BY 'password'
PASSWORD EXPIRE INTERVAL 90 DAY;
                        

Modifying Users

-- Change user password
ALTER USER 'john'@'localhost' IDENTIFIED BY 'new_password';

-- Rename user
RENAME USER 'john'@'localhost' TO 'john_doe'@'localhost';

-- Lock user account
ALTER USER 'john'@'localhost' ACCOUNT LOCK;

-- Unlock user account
ALTER USER 'john'@'localhost' ACCOUNT UNLOCK;
                        

Deleting Users

-- Drop a user
DROP USER 'john'@'localhost';

-- Drop multiple users
DROP USER 'john'@'localhost', 'jane'@'%';

-- Drop user if exists
DROP USER IF EXISTS 'temp_user'@'localhost';
                        

Viewing Users

-- Show all users
SELECT user, host FROM mysql.user;

-- Show current user
SELECT USER(), CURRENT_USER();

-- Show user details
SELECT * FROM mysql.user WHERE user = 'john';
                        

GRANT & REVOKE Commands

GRANT and REVOKE commands manage user privileges on database objects.

GRANT Privileges

-- Grant all privileges on a database
GRANT ALL PRIVILEGES ON mydb.* TO 'john'@'localhost';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE ON mydb.users
TO 'john'@'localhost';

-- Grant privileges on specific table
GRANT SELECT, INSERT ON mydb.products
TO 'sales_user'@'localhost';

-- Grant privileges on all databases
GRANT SELECT ON *.* TO 'readonly'@'localhost';

-- Grant with GRANT OPTION (user can grant to others)
GRANT ALL PRIVILEGES ON mydb.*
TO 'admin'@'localhost'
WITH GRANT OPTION;
                        

Common Privileges

SELECT: Read data
INSERT: Add new records
UPDATE: Modify records
DELETE: Remove records
CREATE: Create tables/databases
DROP: Delete tables/databases
ALTER: Modify table structure
INDEX: Create/drop indexes

Administrative Privileges

CREATE USER: Create new users
GRANT OPTION: Grant privileges
RELOAD: Reload privileges
SHUTDOWN: Shutdown server
PROCESS: View all processes
SUPER: Administrative tasks
REPLICATION: Replication privileges
ALL PRIVILEGES: All privileges

REVOKE Privileges

-- Revoke specific privileges
REVOKE INSERT, UPDATE ON mydb.users
FROM 'john'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON mydb.*
FROM 'john'@'localhost';

-- Revoke GRANT OPTION
REVOKE GRANT OPTION ON mydb.*
FROM 'admin'@'localhost';
                        

Flush Privileges

-- Reload privilege tables (after manual changes to mysql.user)
FLUSH PRIVILEGES;
                        

Note: FLUSH PRIVILEGES is needed only when modifying privilege tables directly. GRANT and REVOKE automatically update privileges.

Show Privileges

-- Show current user privileges
SHOW GRANTS;

-- Show privileges for specific user
SHOW GRANTS FOR 'john'@'localhost';
                        

Database Transactions

A transaction is a sequence of one or more SQL operations treated as a single unit of work. Transactions ensure data consistency and integrity.

What is a Transaction?

A transaction groups multiple SQL statements into a single unit that either succeeds completely or fails completely. This prevents partial updates that could leave the database in an inconsistent state.

Starting Transactions

-- Start a transaction (Method 1)
START TRANSACTION;

-- Start a transaction (Method 2)
BEGIN;

-- Start transaction with READ ONLY
START TRANSACTION READ ONLY;

-- Start transaction with READ WRITE
START TRANSACTION READ WRITE;
                        

COMMIT - Save Changes

-- Complete transaction example
START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

-- If everything is successful, commit changes
COMMIT;
                        

ROLLBACK - Undo Changes

-- Transaction with error handling
START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

-- Check if balance is sufficient
SELECT balance FROM accounts WHERE account_id = 1;

-- If something goes wrong, rollback
ROLLBACK;
                        

Complete Transaction Example

-- Bank transfer transaction
START TRANSACTION;

-- Deduct from sender
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101 AND balance >= 500;

-- Check if update affected a row
SET @rows_affected = ROW_COUNT();

-- If sender has sufficient balance, credit receiver
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 102;

-- Record transaction
INSERT INTO transactions (from_account, to_account, amount, transaction_date)
VALUES (101, 102, 500, NOW());

-- Commit if all successful
COMMIT;

-- Use ROLLBACK in case of error
-- ROLLBACK;
                        

Autocommit Mode

By default, MySQL runs in autocommit mode, meaning each statement is automatically committed.

-- Check autocommit status
SELECT @@autocommit;

-- Disable autocommit
SET autocommit = 0;

-- Enable autocommit
SET autocommit = 1;
                        

ACID Properties

ACID is an acronym that describes four key properties that guarantee database transactions are processed reliably.

Atomicity

All or Nothing: A transaction is treated as a single unit. Either all operations succeed or all fail.

-- Either both updates happen or neither
START TRANSACTION;
UPDATE account SET balance = balance - 100;
UPDATE account SET balance = balance + 100;
COMMIT;
                                

Consistency

Valid State: A transaction brings the database from one valid state to another, maintaining all defined rules and constraints.

Example: Balance constraints, foreign key relationships, and check constraints must remain valid.

Isolation

Concurrent Execution: Concurrent transactions execute independently without interference. Changes made in one transaction are not visible to other transactions until committed.

Prevents issues like dirty reads, non-repeatable reads, and phantom reads.

Durability

Permanent Changes: Once a transaction is committed, changes are permanent and survive system failures, crashes, or power outages.

Committed data is written to permanent storage and can be recovered.

Transaction Isolation Levels

Isolation levels control the degree to which transactions are isolated from each other. Different levels balance between consistency and performance.

Setting Isolation Level

-- Set isolation level for current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set isolation level for next transaction only
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- View current isolation level
SELECT @@transaction_isolation;
                        

READ UNCOMMITTED

Lowest Isolation: Transactions can read uncommitted changes from other transactions (dirty reads).

Allows: Dirty reads, non-repeatable reads, phantom reads
Best for: Performance-critical scenarios where accuracy is less important

READ COMMITTED

Default in many DBMS: Only committed changes are visible to other transactions.

Prevents: Dirty reads
Allows: Non-repeatable reads, phantom reads
Good balance between performance and consistency

REPEATABLE READ

MySQL Default: Ensures same query returns same results within a transaction.

Prevents: Dirty reads, non-repeatable reads
Allows: Phantom reads (in some systems)
Suitable for most applications

SERIALIZABLE

Highest Isolation: Complete isolation - transactions execute as if they were sequential.

Prevents: All concurrency issues
Slowest performance due to locking
Use when data consistency is critical

Savepoints

Savepoints allow you to create checkpoint within a transaction that you can roll back to without rolling back the entire transaction.

Using Savepoints

-- Transaction with savepoints
START TRANSACTION;

-- First operation
INSERT INTO orders (user_id, total_amount) VALUES (1, 100);
SAVEPOINT order_created;

-- Second operation
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (LAST_INSERT_ID(), 5, 2);
SAVEPOINT items_added;

-- Third operation
UPDATE products SET stock_quantity = stock_quantity - 2
WHERE product_id = 5;

-- If something goes wrong with stock update, rollback to items_added
ROLLBACK TO SAVEPOINT items_added;

-- Or rollback to order_created
ROLLBACK TO SAVEPOINT order_created;

-- Release savepoint (no longer needed)
RELEASE SAVEPOINT order_created;

-- Commit the transaction
COMMIT;
                        

Practical Example

-- Complex order processing with savepoints
START TRANSACTION;

-- Create order
INSERT INTO orders (user_id, order_date, status)
VALUES (101, NOW(), 'pending');
SET @order_id = LAST_INSERT_ID();
SAVEPOINT order_saved;

-- Add items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES
    (@order_id, 1, 2, 50.00),
    (@order_id, 2, 1, 30.00);
SAVEPOINT items_saved;

-- Update inventory
UPDATE products SET stock_quantity = stock_quantity - 2
WHERE product_id = 1 AND stock_quantity >= 2;

UPDATE products SET stock_quantity = stock_quantity - 1
WHERE product_id = 2 AND stock_quantity >= 1;

-- If inventory update fails, rollback to items_saved
-- ROLLBACK TO SAVEPOINT items_saved;

-- Apply discount
UPDATE orders
SET discount = 10, total_amount = 120
WHERE order_id = @order_id;

-- Commit everything
COMMIT;
                        

Tip: Savepoints are particularly useful in stored procedures and complex applications where you want fine-grained control over transaction rollback.

Session Summary

Key Points

DCL commands (GRANT, REVOKE) control database access and security
User management includes CREATE USER, ALTER USER, and DROP USER
GRANT assigns privileges to users; REVOKE removes them
Transactions group SQL operations into atomic units
COMMIT saves changes; ROLLBACK undoes them
ACID properties ensure transaction reliability: Atomicity, Consistency, Isolation, Durability
Isolation levels control concurrent transaction behavior
Savepoints enable partial rollback within transactions
Proper privilege management is crucial for database security
Transactions prevent data inconsistencies in concurrent environments

Next Session Preview

In the next session, we will explore PHP-MySQL connectivity, learning how to connect PHP applications to MySQL databases and execute queries programmatically.