Transactions and Advanced SQL Interview Questions for SQL
26 Transactions and Advanced SQL questions with answers for sql roles — 6 easy, 14 medium, 6 hard.
About Transactions and Advanced SQL
Learn transactions, ACID properties, isolation levels, read anomalies, locking, MVCC and deadlocks. Work with views, materialised views, stored procedures, functions, triggers and upserts, and understand SQL injection prevention, large batch updates and archiving strategies used in real production systems. These topics matter most for developer roles.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
A transaction is a group of SQL statements executed as a single unit of work that either fully succeeds or fully fails. It starts with BEGIN or START TRANSACTION and ends with COMMIT to save changes or ROLLBACK to undo them. For a fund transfer, debiting one account and crediting another must happen together, so both statements run in one transaction.
Atomicity: all statements in a transaction succeed or none do. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not interfere and behave as if run in some order, depending on isolation level. Durability: once committed, changes survive crashes because they are written to logs. Banking and payment systems depend on ACID.
A view is a saved SELECT query that behaves like a virtual table. It simplifies complex joins for users, hides sensitive columns for security, and provides a stable interface when underlying tables change. A normal view does not store data, so it runs the query each time. Simple views can be updatable, while views with aggregates or joins usually are not.
A stored procedure is a named set of SQL statements saved in the database and executed with CALL or EXEC, optionally with input and output parameters. It encapsulates business logic, reduces network round trips, can be reused by many applications and allows permission to execute without direct table access. Overusing procedures can make logic harder to version and test compared with application code.
05
Write a transaction to transfer money between two accounts.
BEGIN;
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1 AND balance >= 5000;
UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 2;
COMMIT;
If the first update affects zero rows or any error occurs, the application issues ROLLBACK so neither change is saved.
06
How do you make sure your SQL changes do not break anything?
Situation: in my internship I had to change a report query used by three teams. Task: modify it safely. Action: I tested it on a copy of data, compared row counts and totals with the old version, asked a senior for review, and documented the change. Result: the update went live without issues, and the comparison approach became part of my routine for every change.
07
What is the difference between a stored procedure and a function?
A function must return a value or table and can be used inside SELECT or WHERE, but in most databases cannot change data or manage transactions. A stored procedure is executed independently, can return multiple result sets or output parameters, can modify data and control transactions. Use functions for reusable calculations and procedures for workflows such as month-end processing.
Isolation levels balance consistency and concurrency. Read Uncommitted allows dirty reads. Read Committed, the default in PostgreSQL and SQL Server, prevents dirty reads but allows non-repeatable reads. Repeatable Read, the default in MySQL InnoDB, prevents non-repeatable reads, and InnoDB also prevents most phantoms with gap locks. Serializable prevents all anomalies but reduces concurrency. Choose based on business correctness needs.
09
What are dirty reads, non-repeatable reads and phantom reads?
A dirty read sees uncommitted changes from another transaction that may roll back. A non-repeatable read gets different values when reading the same row twice because another transaction updated and committed it. A phantom read gets a different set of rows for the same query because another transaction inserted or deleted matching rows. Higher isolation levels prevent more of these.
10
What is a deadlock and how do databases handle it?
A deadlock occurs when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks account A and wants B, while T2 locks B and wants A. The database detects the cycle and kills one transaction as the victim, rolling it back with an error. Applications should retry the victim and access resources in a consistent order.
A trigger is code that runs automatically before or after INSERT, UPDATE or DELETE on a table. It is used for auditing changes, enforcing complex rules and maintaining derived data. Triggers are hidden from application developers, can slow writes and make debugging hard, especially when they cascade. Use them sparingly, document them well, and prefer constraints or application logic when possible.
A materialised view stores the result of a query physically, unlike a normal view. Reads are fast because data is precomputed, which suits heavy aggregations for dashboards. The trade-off is staleness, so it must be refreshed on schedule, on commit or incrementally depending on the database. PostgreSQL uses REFRESH MATERIALIZED VIEW, and SQL Server offers indexed views with similar benefits.
13
Write an upsert that inserts a row or updates it if it exists.
PostgreSQL:
INSERT INTO inventory (sku, qty)
VALUES ('A100', 10)
ON CONFLICT (sku)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
MySQL:
INSERT INTO inventory (sku, qty) VALUES ('A100', 10)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);
SQL Server and others use MERGE. A unique key on sku is required.
14
Create a simple stored procedure that returns orders for a customer.
MySQL:
DELIMITER //
CREATE PROCEDURE get_orders(IN p_customer INT)
BEGIN
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = p_customer
ORDER BY order_date DESC;
END //
DELIMITER ;
CALL get_orders(101);
Parameters prevent SQL injection compared with building query strings in code.
Never build SQL by concatenating user input. Use parameterised queries or prepared statements, where input is sent separately from the SQL text, or ORMs that do this automatically. Validate and whitelist inputs such as sort column names, give application users least privilege, and avoid dynamic SQL in procedures unless parameters are bound. Input like ' OR 1=1 -- then cannot change the query logic.
16
Two users book the last seat at the same time and both succeed. How do you fix it?
This is a race condition. Wrap the check and booking in one transaction and lock the seat row with SELECT ... FOR UPDATE so the second transaction waits and then sees it is taken. Alternatively use an atomic update such as UPDATE seats SET booked_by = :user WHERE seat_id = :id AND booked_by IS NULL and check affected rows, plus a unique constraint as a final safety net.
17
You need to update 50 million rows in production. How do you do it safely?
Avoid one huge transaction that locks the table and fills the log. Test on a copy first, take a backup, and update in batches of a few thousand rows using a key range, committing each batch and pausing briefly to reduce load. Run during low traffic, monitor replication lag and locks, keep a rollback script, and verify counts after completion.
18
A business user needs a dashboard on a complex 6-table join that runs slowly. Would you use a view or a materialised view?
A normal view only simplifies the query; it still runs the full join every time, so performance does not improve. If the data can be a few minutes or hours old, a materialised view or summary table refreshed on schedule gives fast reads. If real-time data is required, tune the underlying query with indexes or build an incrementally maintained table.
19
Tell me about a production incident involving a database and how you handled it.
Situation: during a sale, order placement started failing with lock timeouts. Task: restore service quickly. Action: I identified a long-running reporting query holding locks on the orders table, coordinated with the team to kill it, and moved that report to the read replica. Result: orders recovered within ten minutes, and we added query timeouts and a rule that reports never run on the primary.
20
How do you explain a technical database issue to a non-technical manager?
Situation: my manager asked why a report was late every Monday. Task: explain and get approval for a fix. Action: I compared the database to a busy shop counter where reports and customers queue together, showed the timing data, and proposed a separate reporting copy. Result: he understood the trade-off in five minutes, approved the read replica, and Monday reports became on time.
Multi-Version Concurrency Control keeps multiple versions of rows, so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL stores old row versions in the table and cleans them with VACUUM, while MySQL InnoDB uses undo logs. MVCC enables high concurrency in read-heavy systems, but long-running transactions keep old versions alive and cause bloat.
22
What is the difference between optimistic and pessimistic locking?
Pessimistic locking locks rows when read, using SELECT ... FOR UPDATE, so other transactions wait; it suits high-conflict cases such as seat booking. Optimistic locking does not lock but checks at update time that the row has not changed, usually with a version column: UPDATE ... SET version = version + 1 WHERE id = 1 AND version = 5. If zero rows update, the application retries. It suits low-conflict workloads.
PostgreSQL:
CREATE FUNCTION log_salary() RETURNS trigger AS $$
BEGIN
IF NEW.salary <> OLD.salary THEN
INSERT INTO salary_audit(emp_id, old_sal, new_sal, changed_at)
VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END IF;
RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_salary AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_salary();
24
Your application logs frequent deadlock errors at peak hours. How do you investigate and reduce them?
Capture deadlock graphs or logs to see which statements and resources are involved. Common fixes are accessing tables and rows in the same order in all code paths, keeping transactions short, adding indexes so updates lock fewer rows, avoiding user interaction inside transactions, and using lower isolation where safe. Add retry logic for deadlock victims since some deadlocks cannot be fully eliminated.
25
A long-running report transaction is causing table bloat and blocking. What do you do?
Long transactions hold snapshots and locks, preventing cleanup of old row versions and blocking DDL or writers. Move the report to a read replica, break it into smaller batches, use read committed snapshot where supported, and avoid leaving transactions open in client tools. Set statement or idle-in-transaction timeouts, and monitor sessions with long transaction durations.
26
How would you design data archiving for a table growing by 10 million rows a month?
Partition the table by month so old partitions can be detached or moved to an archive table or cheaper storage without heavy deletes. Define a retention policy with business and compliance teams, for example 24 months online. Keep archived data queryable through a separate schema or warehouse, update indexes and statistics, and automate the process with monitoring and alerts.
Frequently asked questions
How important is Transactions and Advanced SQL in SQL interviews?
Learn transactions, ACID properties, isolation levels, read anomalies, locking, MVCC and deadlocks. Work with views, materialised views, stored procedures, functions, triggers and upserts, and understand SQL injection prevention, large batch updates and archiving strategies used in real production systems. These topics matter most for developer roles.
How should I use the Transactions and Advanced SQL sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.