Transactions, views, indexes, procedures and backups

Transactions and isolation

ACID means atomicity, consistency, isolation and durability. Business invariants still require suitable constraints and application logic. InnoDB commonly defaults to REPEATABLE READ; verify the session setting. Concurrent writers can block or deadlock; applications need bounded retry logic for retryable failures.

SELECT @@transaction_isolation;
START TRANSACTION;
UPDATE courses SET fee=fee+500 WHERE course_id=1;
SAVEPOINT revised;
UPDATE courses SET fee=fee+500 WHERE course_id=2;
ROLLBACK TO SAVEPOINT revised;
ROLLBACK;

The final rollback undoes the whole transaction. Savepoints are not durable backups. Long open transactions can hold locks and increase cleanup work.

Views and performance

CREATE VIEW enrollment_report AS
SELECT s.student_id,s.full_name,c.title,e.score
FROM enrollments e JOIN students s USING(student_id)
JOIN courses c USING(course_id);
SELECT * FROM enrollment_report;
EXPLAIN SELECT * FROM students WHERE email='asha@example.com';

A normal view stores a query, not a frozen copy of its rows. Indexes can reduce reads but increase write/storage cost. Composite-index column order matters; examine actual access patterns. EXPLAIN ANALYZE executes the query and reports runtime behaviour, so use care with expensive workloads.

Stored procedure lab

DELIMITER //
CREATE PROCEDURE StudentsInCity(IN requested_city VARCHAR(100))
BEGIN
  SELECT student_id,full_name FROM students WHERE city=requested_city;
END //
DELIMITER ;
CALL StudentsInCity('Pune');
DROP PROCEDURE StudentsInCity;

DELIMITER is a mysql client instruction, not server SQL. Procedures centralize logic, while triggers fire on table events and may hide side effects; document them and avoid unexpected recursive/business behaviour. A CTE gives a named intermediate query using WITH and is not a permanent table.

Accounts and backup

Create application users separately from administrators and grant only necessary privileges on the application schema. Use generated passwords supplied interactively or through approved secret handling, never committed source files.

mysqldump -h DB_ENDPOINT -u backup_user -p --single-transaction --no-tablespaces academy > academy.sql
# Restore into a separately prepared, empty recovery schema:
mysql -h DB_ENDPOINT -u restore_user -p academy_restore < academy.sql

--single-transaction helps obtain a consistent InnoDB snapshot; nontransactional tables and concurrent DDL require additional planning. Include routines/triggers/events deliberately when needed and account for privileges and GTID settings. Encrypt and restrict backup files.

Verification and assignment

Restore and compare row counts plus several known values. Record restore time. A successful dump command without a restore test is insufficient evidence of recoverability. Remove the temporary recovery schema after validation, not the source.

Official references

MySQL backup methods MySQL transactions

Interview and revision check

Why is a read replica not enough as a backup?

Replicas can propagate accidental changes/deletions. Independent retained recovery points and tested restoration are still needed.