/* Day 14: Transactions/concurrency
Tim Pierson, Dartmouth CS61, Spring 2026
*/


-- SLIDE 9
CREATE SCHEMA IF NOT EXISTS test_schema;
USE test_schema;
CREATE TABLE Accounts (
	AccountID INT AUTO_INCREMENT PRIMARY KEY,
    UserName VARCHAR(50),
    Balance DECIMAL(10,2)
);

INSERT INTO Accounts VALUES
    (1, 'Alice', 1000.00),
    (2, 'Bob',    500.00),
    (3, 'Carol', 1500.00),
    (4, 'Dan',    200.00);
    
SELECT * FROM Accounts;

-- SLIDE 10
-- Transfer $100 from Alice to Bob
-- first debit Alice
SET @amount = 100;
UPDATE Accounts
	SET Balance = Balance - @amount
    WHERE AccountID = 1;
    
-- then credit Bob
UPDATE Accounts
	SET Balance = Balance + @amount
    WHERE AccountID = 2;
    
SELECT * FROM Accounts;

-- SLIDE 13
-- reset Alice and Bob accounts
UPDATE Accounts
	SET Balance = 1000
    WHERE AccountID = 1;
    
UPDATE Accounts
	SET Balance = 500
    WHERE AccountID = 2;
    
-- Solve problem using transactions
START TRANSACTION;
SET @amount = 100;
UPDATE Accounts
	SET Balance = Balance - @amount
    WHERE AccountID = 1;
    
-- then credit Bob
UPDATE Accounts
	SET Balance = Balance + @amount
    WHERE AccountID = 2;

-- Write to disk
COMMIT;
    
SELECT * FROM Accounts;

-- SLIDE 13
-- add check constraint on balance
ALTER TABLE Accounts ADD CONSTRAINT CHECK (Balance >=0);

-- now try to send more than Alice has to Bob
START TRANSACTION;
SET @amount = 1050;
UPDATE Accounts
	SET Balance = Balance - @amount
    WHERE AccountID = 1;
    
-- then credit Bob
UPDATE Accounts
	SET Balance = Balance + @amount
    WHERE AccountID = 2;

-- Write to disk
COMMIT;
    
SELECT * FROM Accounts;

-- SLIDE 17
-- T1
UPDATE Apply
	SET Decision = 'Y'
    WHERE StudentID IN
		(SELECT StudentID FROM Student WHERE GPA > 3.9);
        
-- T2
UPDATE Student
	SET GPA = 1.1 * GPA
    WHERE HighSchoolSize > 2500;
    
-- SLIDE 18
INSERT INTO Archive
	SELECT * FROM Apply WHERE Decision = 'N';
DELETE FROM Apply WHERE Decision = 'N';
SELECT COUNT(*) FROM Apply;
SELECT COUNT(*) FROM Archive;

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- SLIDE 20
-- extended example from MySQL Docs section 15.7.2.2
CREATE TABLE Customers (a INT, b CHAR(20), INDEX(a));

-- Do two transactions and commit
START TRANSACTION;
INSERT INTO Customers VALUES(10, 'Heikki');
INSERT INTO Customers VALUES(11, 'Evlis');
COMMIT;
SELECT * FROM Customers;

START TRANSACTION;
INSERT INTO Customers VALUES(15, 'John');
INSERT INTO Customers VALUES(20, 'Paul');
DELETE FROM Customers WHERE b = 'Heikki';

-- Now we undo those last 2 inserts and the delete
ROLLBACK;
SELECT * FROM Customers;


    
-- SLIDE 36
SELECT @@transaction_isolation;


-- SLIDE 69
-- make coupons table and insert a row
CREATE TABLE Coupons (
  Code VARCHAR(20) PRIMARY KEY,
  Discount DECIMAL(5,2),
  MaxUses INT,
  TimesUsed INT DEFAULT 0
);
INSERT INTO Coupons VALUES
  ('LAUNCH50', 50, 100, 0);

-- original code
START TRANSACTION;
SELECT TimesUsed, MaxUses
  FROM Coupons WHERE Code='LAUNCH50';
-- app checks: TimesUsed < MaxUses?
INSERT INTO Redemptions VALUES
  (?, 'LAUNCH50', NOW());
UPDATE Coupons
  SET TimesUsed = TimesUsed + 1
  WHERE Code='LAUNCH50';
COMMIT;

-- updated code
START TRANSACTION;
SELECT TimesUsed, MaxUses
  FROM Coupons FOR UPDATE; -- added FOR UPDATE to lock 

INSERT INTO Redemptions 
	VALUES ('LAUNCH50', NOW());
    
UPDATE Coupons
  SET TimesUsed = TimesUsed + 1
  WHERE Code='LAUNCH50' AND TimesUsed < MaxUses; -- added useage check;
COMMIT;


