Practice: transactions and concurrency

Optional

55 min · Advanced

A review of this module. Skip it if you feel confident; the next module does not need it.

Fix the mistakes

The slips that cost marks in this module. Spot each one, then check the fix.

Most transaction bugs come from four habits: committing without checking what each statement did, writing values computed from an earlier read, choosing an isolation level without knowing what it allows, and locking rows in a different order in different places. Each one has a short, reliable fix.

Mistake 1: committing without checking each step

The affected row count is the number of rows a statement changed, which ROW_COUNT() returns. An UPDATE that matches no row changes 0 rows and is not an error, so the app must check the count before COMMIT.

A debit that finds no money changes zero rows, and MySQL does not complain. If the app commits anyway, half a payment is saved.

Example 1 · A wallet payment that checks every step

SQL

1DROP TABLE IF EXISTS wallets;2CREATE TABLE wallets (3  user_id INT           PRIMARY KEY,4  name    VARCHAR(40)   NOT NULL,5  balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)6);7INSERT INTO wallets (user_id, name, balance) VALUES8  (1, 'Sneha Patil', 650.00),9  (2, 'ShopLite',      0.00);10
11-- Sneha pays the shop Rs. 120: every statement's row count is checked12START TRANSACTION;13UPDATE wallets SET balance = balance - 120 WHERE user_id = 1 AND balance >= 120;14SELECT ROW_COUNT() AS debited;     -- must be 115UPDATE wallets SET balance = balance + 120 WHERE user_id = 2;16SELECT ROW_COUNT() AS credited;    -- must be 117COMMIT;                            -- the app commits only after two 1s18
19-- Sneha tries to pay Rs. 600: the debit finds no row with enough money20START TRANSACTION;21UPDATE wallets SET balance = balance - 600 WHERE user_id = 1 AND balance >= 600;22SELECT ROW_COUNT() AS debited;     -- 0: stop here23ROLLBACK;24
25SELECT user_id, name, balance FROM wallets ORDER BY user_id;

Output

+---------+
| debited |
+---------+
|       1 |
+---------+
+----------+
| credited |
+----------+
|        1 |
+----------+
+---------+
| debited |
+---------+
|       0 |
+---------+
+---------+-------------+---------+
| user_id | name        | balance |
+---------+-------------+---------+
|       1 | Sneha Patil |  530.00 |
|       2 | ShopLite    |  120.00 |
+---------+-------------+---------+

What this code does

The first payment gets row counts 1 and 1, so the app commits. The second debit asks for balance >= 600 while Sneha has 530.00, so it changes no row and the app rolls back before any credit.
The CHECK constraint is a second safety net: even a buggy debit without the guard could not make the balance negative.

A transaction is only as safe as the decision to commit it. MySQL does not roll back on its own when a statement fails. The fix is a rule for every multi-step change: after each statement, check the error and the affected row count. Commit only when every step did exactly what it should; otherwise roll back.

Put the business check inside the statement where you can: WHERE balance >= 120 makes the debit itself refuse to overdraw, so the row count tells you whether the money was there.

Find the bug

Find the unchecked step

Click the line that lets half a payment be saved.

The debit finds no wallet with enough money.

Round 1 of 2

Mistake 2: read, compute in the app, write back

A guarded in-place update changes a value with arithmetic inside the UPDATE and a safety check in WHERE, such as SET stock = stock - 5 WHERE stock >= 5. The app then checks the row count.

Reading a value, computing in the app and writing it back loses an update when two users act together.

Example 2 · A flash sale with a guarded in-place update

SQL

1-- Step 1 · Session A: buyer 1 wants 5 iPhones (stock 8)2START TRANSACTION;3UPDATE products SET stock = stock - 54WHERE product_id = 3 AND stock >= 5;5SELECT ROW_COUNT() AS a_bought;6
7-- Step 2 · Session B: buyer 2 wants 5 at the same moment8START TRANSACTION;9UPDATE products SET stock = stock - 510WHERE product_id = 3 AND stock >= 5;11SELECT ROW_COUNT() AS b_bought;12
13-- Step 3 · Session A14COMMIT;15
16-- Step 4 · Session B: b_bought was 0, so roll back17ROLLBACK;18SELECT product_id, name, stock FROM products WHERE product_id = 3;

Sample output

-- Step 1 · Session A
+----------+
| a_bought |
+----------+
|        1 |
+----------+

-- Step 2 · Session B (waited until step 3)
+----------+
| b_bought |
+----------+
|        0 |
+----------+

-- Step 4 · Session B
+------------+-----------+-------+
| product_id | name      | stock |
+------------+-----------+-------+
|          3 | iPhone 15 |     3 |
+------------+-----------+-------+

What this code does

B's UPDATE waits for A's row lock, then checks stock >= 5 against the new stock, 3, and changes nothing. The app tells buyer 2 that only 3 are left.
No read, no computed value, no lost update: the database checks and changes the stock in one locked step.

Isolation levels do not prevent this at MySQL's default level. Two fixes cover almost every case. For a counter or a balance, update in place with a guard: SET stock = stock - 5 WHERE product_id = 3 AND stock >= 5, then check the row count. When the decision needs several reads, lock first with SELECT ... FOR UPDATE inside the transaction.

For long edits on a screen, where holding a lock is not possible, use a version column and check it when saving.

Fix the error

Replace the app-computed write

Two sessions both read stock 120 and both write 115. Pick the safe second line.

Sell 5 power banks (product 7).

SELECT stock FROM products WHERE product_id = 7;

UPDATE products SET stock = 115 WHERE product_id = 7;

Error

No error is shown: both sales succeed,
but the stock drops by 5 instead of 10.

Mistake 3: choosing the wrong isolation level

An isolation level is a setting, chosen per session or per transaction, that decides what a transaction's reads can see of other transactions' work.

A report reads several numbers one after another. At the wrong level it can mix values from before and after another transaction.

Example 3 · A report at READ COMMITTED mixes two moments

SQL

1-- Setup, once, in either session:2DROP TABLE IF EXISTS bank_accounts;3CREATE TABLE bank_accounts (4  acc_no  INT           PRIMARY KEY,5  holder  VARCHAR(40)   NOT NULL,6  balance DECIMAL(10,2) NOT NULL,7  CONSTRAINT balance_not_negative CHECK (balance >= 0)8);9INSERT INTO bank_accounts (acc_no, holder, balance) VALUES10  (101, 'Asha Rao',   5000.00),11  (102, 'Ravi Kumar', 1200.00);12
13-- Step 1 · Session A: the report reads account 10114SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;15START TRANSACTION;16SELECT balance AS bal_101 FROM bank_accounts WHERE acc_no = 101;17
18-- Step 2 · Session B: a transfer of Rs. 1000 from 101 to 10219START TRANSACTION;20UPDATE bank_accounts SET balance = balance - 1000 WHERE acc_no = 101;21UPDATE bank_accounts SET balance = balance + 1000 WHERE acc_no = 102;22COMMIT;23
24-- Step 3 · Session A: the report reads account 10225SELECT balance AS bal_102 FROM bank_accounts WHERE acc_no = 102;26COMMIT;

Sample output

-- Step 1 · Session A
+---------+
| bal_101 |
+---------+
| 5000.00 |
+---------+

-- Step 3 · Session A
+---------+
| bal_102 |
+---------+
| 2200.00 |
+---------+

What this code does

The report adds 5000.00 (before the transfer) and 2200.00 (after it) and prints Rs. 7,200 in the bank, Rs. 1,000 that never existed.
At the default REPEATABLE READ, both reads come from one snapshot and the total is the true Rs. 6,200. Practice 5 asks you to show it.

A report that reads several numbers at READ COMMITTED can print totals that never existed together.

A simple guide for MySQL: REPEATABLE READ (default): most work. Reports in one transaction see one consistent snapshot; READ COMMITTED: each statement should see the latest data, and fewer lock waits matter more than repeatable reads; SERIALIZABLE: short, critical transactions where you cannot lock the exact rows yourself; expect waits and deadlocks; READ UNCOMMITTED: almost never; it shows changes that may be rolled back.

Mistake 4: locking in a different order in different places

A fixed lock order means every transaction locks rows in the same order, for example by ascending id. Then no circle of waiting can form.

One cart locks product 7 then 6, another locks 6 then 7. Under load, they will deadlock.

Example 4 · Two carts that lock products in id order

SQL

1-- Step 1 · Session A: cart 1 holds products 7 and 6: lock in id order2START TRANSACTION;3SELECT product_id, stock FROM products4WHERE product_id IN (6, 7) ORDER BY product_id FOR UPDATE;5
6-- Step 2 · Session B: cart 2 holds products 6 and 7: the same order7START TRANSACTION;8SELECT product_id, stock FROM products9WHERE product_id IN (6, 7) ORDER BY product_id FOR UPDATE;10
11-- Step 3 · Session A12UPDATE products SET stock = stock - 1 WHERE product_id = 7;13UPDATE products SET stock = stock - 1 WHERE product_id = 6;14COMMIT;15
16-- Step 4 · Session B17UPDATE products SET stock = stock - 2 WHERE product_id = 6;18UPDATE products SET stock = stock - 2 WHERE product_id = 7;19COMMIT;20SELECT product_id, name, stock FROM products WHERE product_id IN (6, 7) ORDER BY product_id;

Sample output

-- Step 1 · Session A
+------------+-------+
| product_id | stock |
+------------+-------+
|          6 |   120 |
|          7 |    60 |
+------------+-------+

-- Step 2 · Session B (waited until step 3)
+------------+-------+
| product_id | stock |
+------------+-------+
|          6 |   119 |
|          7 |    59 |
+------------+-------+

-- Step 4 · Session B
+------------+------------------------+-------+
| product_id | name                   | stock |
+------------+------------------------+-------+
|          6 | boAt Airdopes 141      |   117 |
|          7 | Mi Power Bank 20000mAh |    57 |
+------------+------------------------+-------+

What this code does

Both carts lock 6 before 7, whatever order the customer added them in. Cart 2 waits in step 2 and then reads the stock cart 1 left behind.
The final stock is 120 - 1 - 2 = 117 earbuds and 60 - 1 - 2 = 57 power banks: both checkouts counted, no deadlock.

The fix is a fixed order: sort the ids and lock them all at the start with one ... WHERE product_id IN (...) ORDER BY product_id FOR UPDATE. Keep the transaction short, and still retry the whole transaction on error 1213, because some deadlocks come from places you did not plan for.

Module quiz

Questions from the whole module. Your first answer is your score.

Problems

Graded easy to hard, using everything from this module.

Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.

Exercise 1· Easy

In one transaction, add 10 bonus marks to Gaurav Mishra's (student 7) Database Systems mark (course 2) and set a savepoint. Then by mistake add 10 to all his courses; undo only that, commit, and show his course_id, marks ordered by course.

Expected output

+-----------+-------+
| course_id | marks |
+-----------+-------+
|         1 |    64 |
|         2 |    48 |
+-----------+-------+

Exercise 2· Easy

Sell 3 iPhones (product 3, stock 8) with one guarded in-place UPDATE and show the row count as sold_3. Then try to sell 6 more the same way and show sold_6. Finish with the stock.

Expected output

+--------+
| sold_3 |
+--------+
|      1 |
+--------+
+--------+
| sold_6 |
+--------+
|      0 |
+--------+
+------------+-----------+-------+
| product_id | name      | stock |
+------------+-----------+-------+
|          3 | iPhone 15 |     5 |
+------------+-----------+-------+

Exercise 3· Medium

Create the wallets table from Example 1. Sneha pays the shop Rs. 650 (her whole balance). Write the safe version: guarded debit, debited row count, credit, credited row count, commit. Show both wallets.

Expected output

+---------+
| debited |
+---------+
|       1 |
+---------+
+----------+
| credited |
+----------+
|        1 |
+----------+
+---------+-------------+---------+
| user_id | name        | balance |
+---------+-------------+---------+
|       1 | Sneha Patil |    0.00 |
|       2 | ShopLite    |  650.00 |
+---------+-------------+---------+

Exercise 4· Medium

Predict, then run: Session B (default level) starts a transaction and sums the HR salaries (dept 3) as hr_total. Session A gives Rohan Das (emp_id 10) a raise of 10000. B sums again, commits and sums once more. What are B's three totals?

Sample output

-- Step 1 · Session B
+-----------+
| hr_total  |
+-----------+
| 150000.00 |
+-----------+

-- Step 3 · Session B
+-----------+
| hr_total  |
+-----------+
| 150000.00 |
+-----------+
+-----------+
| hr_total  |
+-----------+
| 160000.00 |
+-----------+

Exercise 5· Medium

Fix the report of Example 3: run it at the default REPEATABLE READ, starting with START TRANSACTION WITH CONSISTENT SNAPSHOT, with the same transfer in the middle. Show bal_101 and bal_102; their sum must be Rs. 6,200.

Sample output

-- Step 1 · Session A
+---------+
| bal_101 |
+---------+
| 5000.00 |
+---------+

-- Step 3 · Session A
+---------+
| bal_102 |
+---------+
| 1200.00 |
+---------+

Exercise 6· Medium

A cancel button can be clicked twice. Cancel order 14 (already CANCELLED) safely: in a transaction, run the guarded status UPDATE (AND status <> 'CANCELLED') and show ROW_COUNT() as cancelled_now. It is 0, so roll back instead of restocking. Show the order and the stock of its product (4).

Expected output

+---------------+
| cancelled_now |
+---------------+
|             0 |
+---------------+
+----------+-----------+
| order_id | status    |
+----------+-----------+
|       14 | CANCELLED |
+----------+-----------+
+------------+-----------------------+-------+
| product_id | name                  | stock |
+------------+-----------------------+-------+
|          4 | Lenovo IdeaPad Slim 3 |    12 |
+------------+-----------------------+-------+

Exercise 7· Hard

Flash sale with locks: buyer 1 (Session A) locks the iPhone row with FOR UPDATE, sees stock 8 and buys 5. Buyer 2 (Session B) also wants 5: its locking read waits, then shows 3, so it rolls back. Show the final stock.

Sample output

-- Step 1 · Session A
+-------+
| stock |
+-------+
|     8 |
+-------+

-- Step 2 · Session B (waited until step 3)
+-------+
| stock |
+-------+
|     3 |
+-------+

-- Step 4 · Session B
+------------+-----------+-------+
| product_id | name      | stock |
+------------+-----------+-------+
|          3 | iPhone 15 |     3 |
+------------+-----------+-------+

Exercise 8· Hard

Reproduce the cart deadlock and the retry. Cart 1 (A) lowers product 7 then 6 by 1; cart 2 (B) lowers product 6 then 7 by 2, interleaved, so B becomes the victim. A commits. Then B retries its whole transaction and commits. Show the final stock of both products.

Sample output

-- Step 3 · Session A (waited until step 4)

-- Step 4 · Session B
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

-- Step 6 · Session B
+------------+------------------------+-------+
| product_id | name                   | stock |
+------------+------------------------+-------+
|          6 | boAt Airdopes 141      |   117 |
|          7 | Mi Power Bank 20000mAh |    57 |
+------------+------------------------+-------+

Interview questions

Questions interviewers ask on this topic.

Finish up

Mark complete & continue

Next: Mini project: cinema seat booking

Practice: transactions and concurrency

Optional

55 min · Advanced

Need help? Ask a tutor

A review of this module. Skip it if you feel confident; the next module does not need it.

Fix the mistakes

The slips that cost marks in this module. Spot each one, then check the fix.

Mistake 1: committing without checking each step

A debit that finds no money changes zero rows, and MySQL does not complain. If the app commits anyway, half a payment is saved.

Example 1 · A wallet payment that checks every step

SQL

1DROP TABLE IF EXISTS wallets;2CREATE TABLE wallets (3  user_id INT           PRIMARY KEY,4  name    VARCHAR(40)   NOT NULL,5  balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)6);7INSERT INTO wallets (user_id, name, balance) VALUES8  (1, 'Sneha Patil', 650.00),9  (2, 'ShopLite',      0.00);10
11-- Sneha pays the shop Rs. 120: every statement's row count is checked12START TRANSACTION;13UPDATE wallets SET balance = balance - 120 WHERE user_id = 1 AND balance >= 120;14SELECT ROW_COUNT() AS debited;     -- must be 115UPDATE wallets SET balance = balance + 120 WHERE user_id = 2;16SELECT ROW_COUNT() AS credited;    -- must be 117COMMIT;                            -- the app commits only after two 1s18
19-- Sneha tries to pay Rs. 600: the debit finds no row with enough money20START TRANSACTION;21UPDATE wallets SET balance = balance - 600 WHERE user_id = 1 AND balance >= 600;22SELECT ROW_COUNT() AS debited;     -- 0: stop here23ROLLBACK;24
25SELECT user_id, name, balance FROM wallets ORDER BY user_id;

Output

+---------+
| debited |
+---------+
|       1 |
+---------+
+----------+
| credited |
+----------+
|        1 |
+----------+
+---------+
| debited |
+---------+
|       0 |
+---------+
+---------+-------------+---------+
| user_id | name        | balance |
+---------+-------------+---------+
|       1 | Sneha Patil |  530.00 |
|       2 | ShopLite    |  120.00 |
+---------+-------------+---------+

What this code does

The first payment gets row counts 1 and 1, so the app commits. The second debit asks for balance >= 600 while Sneha has 530.00, so it changes no row and the app rolls back before any credit.
The CHECK constraint is a second safety net: even a buggy debit without the guard could not make the balance negative.

Put the business check inside the statement where you can: WHERE balance >= 120 makes the debit itself refuse to overdraw, so the row count tells you whether the money was there.

Find the bug

Find the unchecked step

Click the line that lets half a payment be saved.

The debit finds no wallet with enough money.

Round 1 of 2

Mistake 2: read, compute in the app, write back

A guarded in-place update changes a value with arithmetic inside the UPDATE and a safety check in WHERE, such as SET stock = stock - 5 WHERE stock >= 5. The app then checks the row count.

Reading a value, computing in the app and writing it back loses an update when two users act together.

Example 2 · A flash sale with a guarded in-place update

SQL

1-- Step 1 · Session A: buyer 1 wants 5 iPhones (stock 8)2START TRANSACTION;3UPDATE products SET stock = stock - 54WHERE product_id = 3 AND stock >= 5;5SELECT ROW_COUNT() AS a_bought;6
7-- Step 2 · Session B: buyer 2 wants 5 at the same moment8START TRANSACTION;9UPDATE products SET stock = stock - 510WHERE product_id = 3 AND stock >= 5;11SELECT ROW_COUNT() AS b_bought;12
13-- Step 3 · Session A14COMMIT;15
16-- Step 4 · Session B: b_bought was 0, so roll back17ROLLBACK;18SELECT product_id, name, stock FROM products WHERE product_id = 3;

Sample output

-- Step 1 · Session A
+----------+
| a_bought |
+----------+
|        1 |
+----------+

-- Step 2 · Session B (waited until step 3)
+----------+
| b_bought |
+----------+
|        0 |
+----------+

-- Step 4 · Session B
+------------+-----------+-------+
| product_id | name      | stock |
+------------+-----------+-------+
|          3 | iPhone 15 |     3 |
+------------+-----------+-------+

What this code does

B's UPDATE waits for A's row lock, then checks stock >= 5 against the new stock, 3, and changes nothing. The app tells buyer 2 that only 3 are left.
No read, no computed value, no lost update: the database checks and changes the stock in one locked step.

For long edits on a screen, where holding a lock is not possible, use a version column and check it when saving.

Fix the error

Replace the app-computed write

Two sessions both read stock 120 and both write 115. Pick the safe second line.

Sell 5 power banks (product 7).

SELECT stock FROM products WHERE product_id = 7;

UPDATE products SET stock = 115 WHERE product_id = 7;

Error

No error is shown: both sales succeed,
but the stock drops by 5 instead of 10.

Mistake 3: choosing the wrong isolation level

An isolation level is a setting, chosen per session or per transaction, that decides what a transaction's reads can see of other transactions' work.

A report reads several numbers one after another. At the wrong level it can mix values from before and after another transaction.

Example 3 · A report at READ COMMITTED mixes two moments

SQL

1-- Setup, once, in either session:2DROP TABLE IF EXISTS bank_accounts;3CREATE TABLE bank_accounts (4  acc_no  INT           PRIMARY KEY,5  holder  VARCHAR(40)   NOT NULL,6  balance DECIMAL(10,2) NOT NULL,7  CONSTRAINT balance_not_negative CHECK (balance >= 0)8);9INSERT INTO bank_accounts (acc_no, holder, balance) VALUES10  (101, 'Asha Rao',   5000.00),11  (102, 'Ravi Kumar', 1200.00);12
13-- Step 1 · Session A: the report reads account 10114SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;15START TRANSACTION;16SELECT balance AS bal_101 FROM bank_accounts WHERE acc_no = 101;17
18-- Step 2 · Session B: a transfer of Rs. 1000 from 101 to 10219START TRANSACTION;20UPDATE bank_accounts SET balance = balance - 1000 WHERE acc_no = 101;21UPDATE bank_accounts SET balance = balance + 1000 WHERE acc_no = 102;22COMMIT;23
24-- Step 3 · Session A: the report reads account 10225SELECT balance AS bal_102 FROM bank_accounts WHERE acc_no = 102;26COMMIT;

Sample output

-- Step 1 · Session A
+---------+
| bal_101 |
+---------+
| 5000.00 |
+---------+

-- Step 3 · Session A
+---------+
| bal_102 |
+---------+
| 2200.00 |
+---------+

What this code does

The report adds 5000.00 (before the transfer) and 2200.00 (after it) and prints Rs. 7,200 in the bank, Rs. 1,000 that never existed.
At the default REPEATABLE READ, both reads come from one snapshot and the total is the true Rs. 6,200. Practice 5 asks you to show it.

A report that reads several numbers at READ COMMITTED can print totals that never existed together.

Mistake 4: locking in a different order in different places

A fixed lock order means every transaction locks rows in the same order, for example by ascending id. Then no circle of waiting can form.

One cart locks product 7 then 6, another locks 6 then 7. Under load, they will deadlock.

Example 4 · Two carts that lock products in id order

SQL

1-- Step 1 · Session A: cart 1 holds products 7 and 6: lock in id order2START TRANSACTION;3SELECT product_id, stock FROM products4WHERE product_id IN (6, 7) ORDER BY product_id FOR UPDATE;5
6-- Step 2 · Session B: cart 2 holds products 6 and 7: the same order7START TRANSACTION;8SELECT product_id, stock FROM products9WHERE product_id IN (6, 7) ORDER BY product_id FOR UPDATE;10
11-- Step 3 · Session A12UPDATE products SET stock = stock - 1 WHERE product_id = 7;13UPDATE products SET stock = stock - 1 WHERE product_id = 6;14COMMIT;15
16-- Step 4 · Session B17UPDATE products SET stock = stock - 2 WHERE product_id = 6;18UPDATE products SET stock = stock - 2 WHERE product_id = 7;19COMMIT;20SELECT product_id, name, stock FROM products WHERE product_id IN (6, 7) ORDER BY product_id;

Sample output

-- Step 1 · Session A
+------------+-------+
| product_id | stock |
+------------+-------+
|          6 |   120 |
|          7 |    60 |
+------------+-------+

-- Step 2 · Session B (waited until step 3)
+------------+-------+
| product_id | stock |
+------------+-------+
|          6 |   119 |
|          7 |    59 |
+------------+-------+

-- Step 4 · Session B
+------------+------------------------+-------+
| product_id | name                   | stock |
+------------+------------------------+-------+
|          6 | boAt Airdopes 141      |   117 |
|          7 | Mi Power Bank 20000mAh |    57 |
+------------+------------------------+-------+

What this code does

Both carts lock 6 before 7, whatever order the customer added them in. Cart 2 waits in step 2 and then reads the stock cart 1 left behind.
The final stock is 120 - 1 - 2 = 117 earbuds and 60 - 1 - 2 = 57 power banks: both checkouts counted, no deadlock.

Module quiz

Questions from the whole module. Your first answer is your score.

Pick an answer to see if it is right. 0/12 answered.

Question 1 of 12

Which statement about MySQL transactions is true?

Question 2 of 12

`wallets` has user 1 with balance 530.00. What does the SELECT return?

Python

UPDATE wallets SET balance = balance - 600 WHERE user_id = 1 AND balance >= 600;SELECT ROW_COUNT() AS debited;

Question 3 of 12

Product 3 has stock 8. Both sessions run this UPDATE, A first, and A commits before B continues. What are the two row counts?

Python

UPDATE products SET stock = stock - 5WHERE product_id = 3 AND stock >= 5;SELECT ROW_COUNT();

Question 4 of 12

Session B is at the default level. What does B's second SELECT return?

Python

1-- Step 1 · Session B2START TRANSACTION;3SELECT SUM(salary) AS hr_total FROM employees WHERE dept_id = 3;   -- 150000.004
5-- Step 2 · Session A (autocommit)6UPDATE employees SET salary = salary + 10000 WHERE emp_id = 10;   -- Rohan, HR7
8-- Step 3 · Session B9SELECT SUM(salary) AS hr_total FROM employees WHERE dept_id = 3;

Question 5 of 12

The same steps, but B is at READ COMMITTED. What does B's second SELECT return?

Question 6 of 12

Which line makes this ticket booking unsafe when two users book at once?

Python

START TRANSACTION;                                               -- line 1SELECT seats_left FROM train_quota WHERE train_no = 12627;       -- line 2-- app: new_value = seats_left - 2UPDATE train_quota SET seats_left = 8 WHERE train_no = 12627;    -- line 3COMMIT;                                                          -- line 4

Question 7 of 12

A finance report reads ten account balances one by one and prints their total. Which setting gives a total that matches one real moment?

Question 8 of 12

Cart 1 locks product 7 then 6; cart 2 locks product 6 then 7, interleaved. What is the most likely result?

Question 9 of 12

A checkout gets error 1213. What must the application retry?

Question 10 of 12

Which statement commits an open transaction on its own?

Question 11 of 12

Order 14 is already CANCELLED. A cancel feature runs this inside a transaction. What should the app do next?

Python

UPDATE orders SET status = 'CANCELLED'WHERE order_id = 14 AND status <> 'CANCELLED';SELECT ROW_COUNT();   -- returns 0

Question 12 of 12

A hotel booking page shows a room for up to 10 minutes while the guest fills in details. Which locking approach fits?

Problems

Graded easy to hard, using everything from this module.

Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.

Exercise 1· Easy

Expected output

+-----------+-------+
| course_id | marks |
+-----------+-------+
|         1 |    64 |
|         2 |    48 |
+-----------+-------+

Exercise 2· Easy

Sell 3 iPhones (product 3, stock 8) with one guarded in-place UPDATE and show the row count as sold_3. Then try to sell 6 more the same way and show sold_6. Finish with the stock.

Expected output

+--------+
| sold_3 |
+--------+
|      1 |
+--------+
+--------+
| sold_6 |
+--------+
|      0 |
+--------+
+------------+-----------+-------+
| product_id | name      | stock |
+------------+-----------+-------+
|          3 | iPhone 15 |     5 |
+------------+-----------+-------+

Exercise 3· Medium

Expected output

+---------+
| debited |
+---------+
|       1 |
+---------+
+----------+
| credited |
+----------+
|        1 |
+----------+
+---------+-------------+---------+
| user_id | name        | balance |
+---------+-------------+---------+
|       1 | Sneha Patil |    0.00 |
|       2 | ShopLite    |  650.00 |
+---------+-------------+---------+

Exercise 4· Medium

Sample output

-- Step 1 · Session B
+-----------+
| hr_total  |
+-----------+
| 150000.00 |
+-----------+

-- Step 3 · Session B
+-----------+
| hr_total  |
+-----------+
| 150000.00 |
+-----------+
+-----------+
| hr_total  |
+-----------+
| 160000.00 |
+-----------+

Exercise 5· Medium

Sample output

-- Step 1 · Session A
+---------+
| bal_101 |
+---------+
| 5000.00 |
+---------+

-- Step 3 · Session A
+---------+
| bal_102 |
+---------+
| 1200.00 |
+---------+

Exercise 6· Medium

Expected output

+---------------+
| cancelled_now |
+---------------+
|             0 |
+---------------+
+----------+-----------+
| order_id | status    |
+----------+-----------+
|       14 | CANCELLED |
+----------+-----------+
+------------+-----------------------+-------+
| product_id | name                  | stock |
+------------+-----------------------+-------+
|          4 | Lenovo IdeaPad Slim 3 |    12 |
+------------+-----------------------+-------+

Exercise 7· Hard

Sample output

-- Step 1 · Session A
+-------+
| stock |
+-------+
|     8 |
+-------+

-- Step 2 · Session B (waited until step 3)
+-------+
| stock |
+-------+
|     3 |
+-------+

-- Step 4 · Session B
+------------+-----------+-------+
| product_id | name      | stock |
+------------+-----------+-------+
|          3 | iPhone 15 |     3 |
+------------+-----------+-------+

Exercise 8· Hard

Sample output

-- Step 3 · Session A (waited until step 4)

-- Step 4 · Session B
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

-- Step 6 · Session B
+------------+------------------------+-------+
| product_id | name                   | stock |
+------------+------------------------+-------+
|          6 | boAt Airdopes 141      |   117 |
|          7 | Mi Power Bank 20000mAh |    57 |
+------------+------------------------+-------+

Interview questions

Questions interviewers ask on this topic.

7 questions. Answer out loud first, then compare.

Finish up

Mark complete & continue

Next: Mini project: cinema seat booking

Mistake 1: committing without checking each step

What this code does

Mistake 2: read, compute in the app, write back

What this code does

Mistake 3: choosing the wrong isolation level

What this code does

Mistake 4: locking in a different order in different places

What this code does

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

Mistake 1: committing without checking each step

What this code does

Mistake 2: read, compute in the app, write back

What this code does

Mistake 3: choosing the wrong isolation level

What this code does

Mistake 4: locking in a different order in different places

What this code does

Which statement about MySQL transactions is true?

wallets has user 1 with balance 530.00. What does the SELECT return?

Product 3 has stock 8. Both sessions run this UPDATE, A first, and A commits before B continues. What are the two row counts?

Session B is at the default level. What does B's second SELECT return?

The same steps, but B is at READ COMMITTED. What does B's second SELECT return?

Which line makes this ticket booking unsafe when two users book at once?

A finance report reads ten account balances one by one and prints their total. Which setting gives a total that matches one real moment?

Cart 1 locks product 7 then 6; cart 2 locks product 6 then 7, interleaved. What is the most likely result?

A checkout gets error 1213. What must the application retry?

Which statement commits an open transaction on its own?

Order 14 is already CANCELLED. A cancel feature runs this inside a transaction. What should the app do next?

A hotel booking page shows a room for up to 10 minutes while the guest fills in details. Which locking approach fits?

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

Q1Walk me through how you would write a money transfer in SQL so that it is correct under load.

Q2A product page sometimes oversells during sales. How do you find and fix the cause?

Q3Which isolation level would you use for a nightly report, and why?

Q4Is it safe to switch the whole application to SERIALIZABLE to avoid concurrency bugs?

Q5How do you make a "cancel order" button safe to click twice?

Q6What is the difference between a deadlock and a long lock wait for the application?

Q7Why should you never keep a transaction open while waiting for a user?

Which statement about MySQL transactions is true?

wallets has user 1 with balance 530.00. What does the SELECT return?

Product 3 has stock 8. Both sessions run this UPDATE, A first, and A commits before B continues. What are the two row counts?

Session B is at the default level. What does B's second SELECT return?

The same steps, but B is at READ COMMITTED. What does B's second SELECT return?

Which line makes this ticket booking unsafe when two users book at once?

A finance report reads ten account balances one by one and prints their total. Which setting gives a total that matches one real moment?

Cart 1 locks product 7 then 6; cart 2 locks product 6 then 7, interleaved. What is the most likely result?

A checkout gets error 1213. What must the application retry?

Which statement commits an open transaction on its own?

Order 14 is already CANCELLED. A cancel feature runs this inside a transaction. What should the app do next?

A hotel booking page shows a room for up to 10 minutes while the guest fills in details. Which locking approach fits?

Q1Walk me through how you would write a money transfer in SQL so that it is correct under load.

Q2A product page sometimes oversells during sales. How do you find and fix the cause?

Q3Which isolation level would you use for a nightly report, and why?

Q4Is it safe to switch the whole application to SERIALIZABLE to avoid concurrency bugs?

Q5How do you make a "cancel order" button safe to click twice?

Q6What is the difference between a deadlock and a long lock wait for the application?

Q7Why should you never keep a transaction open while waiting for a user?

`wallets` has user 1 with balance 530.00. What does the SELECT return?

`wallets` has user 1 with balance 530.00. What does the SELECT return?