Lesson 9 / الدرس 9

Two writes that must both happen / كتابتان يجب أن تقعا معًا

Some operations are only correct as a whole. A transaction is how you tell the database that a group of statements is one thing — so that a crash between them leaves no trace instead of leaving half.

بعض العمليات لا تكون صحيحة إلا ككلٍّ. والمعاملة هي كيف تخبر قاعدة البيانات أن مجموعة عبارات شيء واحد — فيترك انهيارٌ بينها لا أثرًا بدل أن يترك نصفًا.

Taking a payment is two writes: record the payment, mark the booking paid. Between them the process can be killed, the database can restart, the network can drop — and then you have money recorded against a booking that still says unpaid, or a booking marked paid with nothing to show for it. Neither is a state anybody designed.

START TRANSACTION;

  INSERT INTO payments (booking_id, amount, taken_at)
  VALUES (91, 1450.00, NOW());

  UPDATE bookings SET status = 'paid' WHERE id = 91;

COMMIT;

-- Until COMMIT runs, no other connection can see either change.
-- After it runs, both are visible together. There is no moment at
-- which one exists without the other, from anybody else's view.

-- And if the second statement fails:
ROLLBACK;   -- the INSERT is undone too. Nothing happened.

The last line is the part worth internalising. ROLLBACK does not delete the row — it means the row never existed for anyone but you, and an id consumed by an auto-increment is the only trace it leaves. A crash before COMMIT has the same effect as a rollback, without anybody having to write one.

السطر الأخير هو ما يستحق الاستبطان. فـROLLBACK لا تحذف الصف — بل تعني أن الصف لم يوجد قط لأحد سواك، والمعرّفُ الذي استهلكه عدّاد تصاعدي هو الأثر الوحيد الذي تتركه. والانهيار قبل COMMIT له أثر التراجع نفسه، دون أن يضطر أحد إلى كتابته.

The four letters people quote

	What it promises
Atomic	All of the statements, or none of them. Never some
Consistent	Constraints hold at the end — the rules from chapter one are not suspended inside a transaction
Isolated	Other connections do not see your half-finished work. How completely is a setting, and the next lesson but one
Durable	Once COMMIT returns, the change survives the power going out

Atomic is the one you reach for daily; durable is the one you rely on without thinking. Isolated is the interesting one, because it is not a yes or no — it is a dial, and where it is set decides which of the problems in the next lesson can happen to you.

Keeping them short

An open transaction holds locks. Everything it has touched is at least partly unavailable to everyone else until it ends, so a long one is a queue forming behind it.
Never call another service from inside one. A payment gateway that takes four seconds is a transaction that holds its locks for four seconds — and when the gateway hangs, your database stops rather than just that request.
Do the reading and the deciding first. Work out what needs to change, then open the transaction, write, and close it. The transaction is for the writes, not for the thinking.

تأكد أن كودك يتراجع حين يرمي. فالإطار يفعل ذلك لك عادةً؛ أما START TRANSACTION مكتوبة يدويًا بلا try حولها فتترك المعاملة مفتوحة حين يخفق شيء، حاجزةً الأقفال حتى يُغلق الاتصال أو يستسلم الخادم لها.

Check yourself / اختبر نفسك

1. What does ROLLBACK actually do to a row inserted inside the transaction?

2. Why should a payment gateway never be called from inside a transaction?

3. What happens if you run ALTER TABLE inside a transaction in MySQL?