Transactions, Locking & Concurrency Control
ধরা যাক, আপনি বন্ধুকে ৫০০ টাকা পাঠাচ্ছেন। আপনার অ্যাকাউন্ট থেকে টাকা কাটল, কিন্তু বন্ধুর অ্যাকাউন্টে ঢোকার আগেই কারেন্ট চলে গেল বা সার্ভার ক্র্যাশ করল। এখন আপনার টাকাও গেল, বন্ধুর কাছেও পৌঁছাল না। এটা কি মেনে নেওয়া যায়? কখনোই না!
এই ধরনের ক্রিটিক্যাল সমস্যা সমাধান করার জন্যই ডেটাবেসে Transaction এবং Locking ব্যবহার করা হয়। এটি ফিনটেক বা ই-কমার্স অ্যাপের হৃদপিণ্ড।
১১.১ Transaction কী? (Simple Explanation)
Transaction হলো এক বা একাধিক SQL কুয়েরির একটি গ্রুপ, যা একটি সিঙ্গেল ইউনিট হিসেবে কাজ করে। এর নিয়ম হলো: "হয় সব হবে, না হয় কিছুই হবে না" (All or Nothing)।
📌 উদাহরণ (টাকা ট্রান্সফার):
- রহিমের ব্যালেন্স থেকে ৫০০ টাকা কমানো।
- করিমের ব্যালেন্সে ৫০০ টাকা যোগ করা।
এই দুটো কাজই সফল হতে হবে। যদি ২ নম্বর ধাপে এরর হয়, তবে ১ নম্বর ধাপটিও বাতিল (Rollback) হয়ে আগের অবস্থায় ফিরে যেতে হবে।
১১.২ ACID Properties (ইন্টারভিউয়ের বাইবেল)
প্রতিটি ট্রানজেকশন ৪টি ধর্ম মেনে চলে, সংক্ষেপে ACID:
A - Atomicity (অবিভাজ্যতা)
ট্রানজেকশন অর্ধেক হবে না। ১০টা স্টেপ থাকলে ১০টাই সফল হতে হবে, একটা ফেইল করলে সব রোলব্যাক হবে।
C - Consistency (নির্ভুলতা)
ট্রানজেকশনের আগে এবং পরে ডেটাবেসের সব রুলস (Constraints) ঠিক থাকতে হবে। (যেমন: ব্যালেন্স কখনো নেগেটিভ হতে পারবে না)।
I - Isolation (বিচ্ছিন্নতা)
একটি ট্রানজেকশন চলার সময় অন্য কেউ তার মাঝখানের অবস্থা দেখতে পারবে না। (আমি টাকা পাঠাচ্ছি, মাঝপথে কেউ দেখবে না যে আমার টাকা কমেছে কিন্তু আপনার বাড়েনি)।
D - Durability (স্থায়িত্ব)
একবার COMMIT হলে, সার্ভার পুড়ে গেলেও ডেটা হারাবে না (ডিস্কে সেভ থাকবে)।
১১.৩ ট্রানজেকশন কমান্ডস
START TRANSACTION; -- ট্রানজেকশন শুরু
-- কুয়েরি ১
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- কুয়েরি ২
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- সব ঠিক থাকলে সেভ করোযদি ভুল হয়:
ROLLBACK; -- আগের অবস্থায় ফিরে যাও১১.৪ Locking: Row Lock vs Table Lock
একাধিক ইউজার যখন একই সময়ে ডেটাবেস ব্যবহার করে, তখন কনফ্লিক্ট এড়াতে লক ব্যবহার করা হয়।
✅ ১. Row Lock (InnoDB - The Hero)
শুধুমাত্র যেই রো (Row) নিয়ে কাজ হচ্ছে, সেটি লক হয়। বাকি টেবিল খোলা থাকে।
সুবিধা: হাই কনকারেন্সি (একসাথে হাজার হাজার ইউজার কাজ করতে পারে)।
উদাহরণ: আমি আমার প্রোফাইল এডিট করছি, আপনি আপনারটা করছেন। কেউ কাউকে বাধা দেবে না।
❌ ২. Table Lock (MyISAM - Avoid This)
একজন ইউজার কিছু লিখলে পুরো টেবিল লক হয়ে যায়।
সমস্যা: একজন টাকা তুললে অন্য কেউ ব্যালেন্স চেকও করতে পারে না যতক্ষণ না প্রথমজন শেষ করে। এটি স্লো।
১১.৫ Shared Lock vs Exclusive Lock
🔓 Shared Lock (LOCK IN SHARE MODE)
লজিক: "আমি পড়ছি, তুমিও পড়তে পারো, কিন্তু কেউ লিখতে পারবে না।"
ব্যবহার: রিপোর্ট জেনারেট করার সময় যাতে কেউ ডেটা চেঞ্জ না করে।
🔒 Exclusive Lock (FOR UPDATE)
লজিক: "আমি কাজ করছি, কেউ এই রো-তে হাত দেবে না (পড়বেও না, লিখবেও না)।"
ব্যবহার: স্টক আপডেট বা টাকা ট্রান্সফারের সময়।
-- আইডি ১০ এর রো লক করা হলো, যাতে অন্য কেউ এই মুহূর্তে আপডেট না করতে পারে
SELECT * FROM products WHERE id = 10 FOR UPDATE;১১.৬ Deadlock (ডেডলক) — ট্রাফিক জ্যাম
⚠️ ডেডলক কী?
দুজন ইউজার একে অপরের লকের জন্য অপেক্ষা করছে, ফলে কেউ এগোতে পারছে না।
দৃশ্যপট:
- User A: টেবিল ১ লক করল, এখন টেবিল ২ চায়।
- User B: টেবিল ২ লক করল, এখন টেবিল ১ চায়।
- ফলাফল: কেউ ছাড়ছে না, অনন্তকাল অপেক্ষা (Deadlock)।
১১.৭ Deadlock সমাধান (Senior Strategies)
ইন্টারভিউতে জিজ্ঞেস করবে, "কিভাবে ডেডলক সলভ করবেন?"
- ✓ Always Update in Same Order: সব ট্রানজেকশনে টেবিল বা রো একই সিরিয়ালে আপডেট করুন।
- → সঠিক: সবসময় ছোট আইডি আগে আপডেট, তারপর বড় আইডি। User A: Update ID 1 -> Update ID 2 | User B: Update ID 1 (অপেক্ষা করবে) → ... এতে সাইকেল তৈরি হবে না।
- ✓ Short Transaction: ট্রানজেকশন যত ছোট হবে, লকিং টাইম তত কম হবে, ডেডলক রিস্ক কমবে।
- ✓ Indexes: WHERE ক্লজে ইনডেক্স না থাকলে MySQL অনেক বেশি রো লক করে ফেলে (Scan), যা ডেডলক বাড়ায়। ইনডেক্স ব্যবহার করুন।
- ✓ Retry Logic: অ্যাপ্লিকেশনে try-catch ব্লক রাখুন। ডেডলক এরর পেলে ৫০ মিলি-সেকেন্ড পর আবার ট্রাই করুন।
১১.৮ Isolation Levels (The Deep Dive)
MySQL-এ ৪ ধরণের আইসোলেশন লেভেল আছে। এটি ঠিক করে এক ট্রানজেকশন অন্য ট্রানজেকশনকে কতটা ডিস্টার্ব করবে।
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Performance |
|---|---|---|---|---|
| READ UNCOMMITTED | ✅ (হয়) | ✅ | ✅ | 🚀 Fastest (Unsafe) |
| READ COMMITTED | ❌ (হয় না) | ✅ | ✅ | ⚡ Fast (Standard) |
| REPEATABLE READ (Default) | ❌ | ❌ | ❌ (InnoDB Magic) | ⚖️ Balanced |
| SERIALIZABLE | ❌ | ❌ | ❌ | 🐢 Slowest (Safest) |
পরিভাষা (Problems):
- Dirty Read: আপনি এমন ডেটা পড়লেন যা এখনো COMMIT হয়নি (অন্য কেউ হয়তো রোলব্যাক করবে)।
- Non-repeatable Read: একই ট্রানজেকশনে দুইবার কুয়েরি করলে দুই রকম রেজাল্ট আসে (মাঝখানে কেউ আপডেট করেছে)।
- Phantom Read: একই কুয়েরিতে হঠাৎ নতুন রো উদয় হওয়া (মাঝখানে কেউ ইনসার্ট করেছে)।
📌 Senior Note: MySQL InnoDB ডিফল্টভাবে REPEATABLE READ ব্যবহার করে এবং Next-Key Locking টেকনিক দিয়ে ফ্যান্টম রিড আটকায়।
১১.৯ Pessimistic vs Optimistic Locking (Real World Logic)
🔒 Pessimistic Locking (হতাশাবাদী)
বিশ্বাস: "সবাই আমার ডেটা নষ্ট করতে চায়, তাই আমি লক করে কাজ করব।"
পদ্ধতি: ডেটাবেসের FOR UPDATE লক ব্যবহার করে।
ব্যবহার: ই-কমার্স ইনভেন্টরি, ব্যাংকিং।
BEGIN;
SELECT quantity FROM products WHERE id=1 FOR UPDATE;
-- স্টক চেক করে আপডেট
UPDATE products SET quantity = quantity - 1 WHERE id=1;
COMMIT;😊 Optimistic Locking (আশাবাদী)
বিশ্বাস: "কনফ্লিক্ট সাধারণত হবে না, তাই লক করার দরকার নেই। সেভ করার সময় শুধু চেক করব কেউ চেঞ্জ করেছে কি না।"
পদ্ধতি: version কলাম ব্যবহার করে।
ব্যবহার: ইউজার প্রোফাইল এডিট, কমেন্ট এডিট।
-- পড়ার সময় version = 5 ছিল
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 10 AND version = 5;
-- যদি মাঝখানে কেউ আপডেট করে version 6 করে ফেলে, তবে এই কুয়েরি ফেইল করবে (0 row affected)।১১.১০ Real-World Example: Order Processing
একজন সিনিয়র ইঞ্জিনিয়ার হিসেবে আপনি এভাবে কোড লিখবেন:
START TRANSACTION;
-- ১. ইনভেন্টরি লক করা (যাতে নেগেটিভ স্টক না হয়)
SELECT stock FROM products WHERE id = 101 FOR UPDATE;
-- ২. স্টক কমানো
UPDATE products SET stock = stock - 1 WHERE id = 101;
-- ৩. অর্ডার এন্ট্রি
INSERT INTO orders (user_id, product_id, amount) VALUES (1, 101, 500);
-- ৪. পেমেন্ট রেকর্ড
INSERT INTO payments (order_id, status) VALUES (LAST_INSERT_ID(), 'success');
COMMIT;যেকোনো ধাপে ফেইল করলে ROLLBACK হয়ে স্টক আগের জায়গায় ফিরে যাবে।
🎯 অধ্যায় ১১ এর সারাংশ (Summary)
এই অধ্যায়ে আমরা শিখলাম:
- ✓Transaction: ACID প্রপার্টি এবং COMMIT/ROLLBACK এর গুরুত্ব।
- ✓Locking: Row Lock (ভালো) বনাম Table Lock (খারাপ)।
- ✓Deadlock: কেন হয় এবং সমাধানের উপায় (Same Order Update)।
- ✓Isolation Levels: Dirty Read, Phantom Read এর পার্থক্য।
- ✓Strategy: কখন Pessimistic (Bank) আর কখন Optimistic (Profile) লক ব্যবহার করবেন।
✨ এখন আপনি ডেটাবেসের কনকারেন্সি এবং সুরক্ষা নিশ্চিত করতে সক্ষম। পরবর্তী অধ্যায়ে আমরা শিখব MySQL Storage Engines, Partitioning, Replication এবং Backup/Restore—যেখানে আমরা ডেটাবেসের ইঞ্জিনের ভেতরে উঁকি দেব এবং প্রোডাকশন গ্রেড স্কেলিং কৌশল শিখব।
প্রস্তুত তো? 🚀