Oracle Locking, Blocking ও Deadlock: ORA-00060-এর একটি হাতে-কলমে গাইড

"অ্যাপ্লিকেশন জমে গেছে।" আপনি লগইন করেন, আর সবকিছু সুস্থ দেখায় - CPU অলস, প্রচুর মেমরি, কোনো I/O ঝড় নেই। ডেটাবেজ ধীর নয়; সে অপেক্ষা করছে। একটি session এমন একটি lock ধরে আছে যা আরেকটি session-এর দরকার, আর তার পেছনে একটি সারি তৈরি হয়েছে। Locking, blocking আর ভয়ংকর সেই deadlock (ORA-00060) Oracle-এর সবচেয়ে ভুল-বোঝা সমস্যাগুলোর একটি, কারণ এগুলো performance সমস্যার মতো দেখায় অথচ আসলে concurrency সমস্যা। এই গাইড ব্যাখ্যা করে Oracle locking আসলে কীভাবে কাজ করে, কয়েক সেকেন্ডে সারির মাথায় থাকা session-টি কীভাবে খুঁজে পাবেন, একটি deadlock trace কীভাবে পড়বেন, আর যে অ্যাপ্লিকেশন প্যাটার্নগুলো এটি ঘটায় সেগুলো কীভাবে ঠিক করবেন।

১. Oracle Locking কীভাবে কাজ করে (যে অংশটা মানুষ ভুল বোঝে)

Oracle-এর concurrency মডেল তার মুকুটমণি, আর তা দাঁড়িয়ে আছে এমন দুটি নিয়মের ওপর যা অন্য ডেটাবেজ থেকে আসা মানুষদের অবাক করে:

• Reader কখনও writer-কে block করে না, আর writer কখনও reader-কে block করে না। একটি query undo-র মাধ্যমে একটি সামঞ্জস্যপূর্ণ snapshot দেখে (read consistency-র পেছনের কৌশল), তাই ডেটা বদলাচ্ছে এমন কোনো session-এর জন্য তাকে কখনও অপেক্ষা করতে হয় না।
• Writer কেবল একই row-তেই writer-কে block করে। আপনি যখন একটি row UPDATE করেন, Oracle commit বা rollback না করা পর্যন্ত শুধু সেই row-তেই একটি row-level lock বসায়। অন্য একটি session যা আলাদা row update করছে তা স্বাধীনভাবে এগোয়; একই row update করছে এমন session অপেক্ষা করে।

কোনো বিশাল তালিকা রক্ষণাবেক্ষণকারী lock manager নেই, আর কিছু ডেটাবেজ যেমন চাপে পড়ে row lock-কে table lock-এ escalate করে, Oracle তা করে না। Lock-গুলো ডেটা ব্লকেই সংরক্ষিত থাকে, তাই 'lock memory' ফুরিয়ে না গিয়েই আপনি লাখ লাখ row lock করতে পারেন। এই ডিজাইনের মানে হলো, blocking যখন ঘটে তখন তা প্রায় সবসময়ই একটি নির্দিষ্ট row আর একটি নির্দিষ্ট ধীর transaction নিয়ে - ডেটাবেজ 'ওভারলোডেড' হওয়া নিয়ে নয়।

২. Blocking: স্বাভাবিক, যতক্ষণ না তা নয়

Blocking নিজে থেকে সমস্যা নয়। যদি session A একটি row update করে আর session B একই row চায়, B অপেক্ষা করে - ঠিকভাবেই - যতক্ষণ না A commit করে। এটাই ডেটা integrity-র কাজ। এটি সমস্যা হয় কেবল তখনই যখন A অত্যধিক সময় ধরে lock ধরে রাখে আর তার পেছনে একটি সারি জমে ওঠে।

ক্লাসিক কারণটি হলো এমন একটি transaction যা user think-time জুড়ে খোলা থাকে: একটি অ্যাপ্লিকেশন একটি row FOR UPDATE select করে, তারপর ব্যবহারকারী 'Save' ক্লিক করার অপেক্ষায় থাকে - আর ব্যবহারকারী দুপুরের খাবারে চলে যান। সেই row-এর প্রয়োজন থাকা প্রতিটি অন্য session এখন একটি কফি ব্রেকের পেছনে আটকে। আপনি যে wait event দেখবেন তা হলো enq: TX - row lock contention।

৩. কয়েক সেকেন্ডে Blocker খুঁজে বের করা

হাতে V$LOCK ডিকোড করার কথা ভুলে যান। আধুনিক Oracle সরাসরি বলে দেয় কে কাকে block করছে:

-- Who is blocked, and who is blocking them?
SELECT s.sid, s.serial#, s.username, s.status,
       s.blocking_session        AS blocked_by_sid,
       s.event, s.seconds_in_wait,
       s.sql_id
FROM   v$session s
WHERE  s.blocking_session IS NOT NULL
ORDER  BY s.seconds_in_wait DESC;

-- Full blocking tree (who is the root blocker?)
SELECT LEVEL, s.sid, s.username, s.event, s.sql_id
FROM   v$session s
START WITH s.blocking_session IS NULL
       AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER  SIBLINGS BY s.sid;

BLOCKING_SESSION সরাসরি দোষীর দিকে আঙুল তোলে। tree query-টি ROOT blocker খুঁজে বের করে - chain তৈরি হলে যা গুরুত্বপূর্ণ, কারণ মাঝখানের একটি session kill করলে কিছুই হয় না; আপনাকে মাথাটা পরিষ্কার করতে হবে।

-- See exactly what the blocker is (or isn't) doing:
SELECT sid, status, last_call_et AS idle_seconds, sql_id, machine, program
FROM   v$session WHERE sid = :root_blocker;
-- status=INACTIVE + large last_call_et = a transaction left open by the app

৪. একটি Block পরিষ্কার করা - আর Kill কেন শেষ উপায়

তাৎক্ষণিক সমাধান প্রায়ই blocker-কে সরিয়ে দেওয়া, কিন্তু তা ভেবেচিন্তে করুন।

-- If the blocker is a genuine runaway or abandoned transaction:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Kill সেই session-এর transaction-কে rollback করতে বাধ্য করে, যা নিজেই সময় নিতে পারে এবং ব্যবহারকারীর কাজ বাতিল করে দেয়। যেখানে সম্ভব, commit/close করার জন্য অ্যাপ্লিকেশন বা ব্যবহারকারীর সঙ্গে যোগাযোগ করাই ভালো। আসল সমাধান কখনও kill নয় - সেই প্যাটার্নটি থামানোই আসল সমাধান যা দীর্ঘক্ষণ ধরে রাখা lock তৈরি করেছে, আর তা থাকে অ্যাপ্লিকেশনে (সেকশন ৬)। আপনি যদি সপ্তাহে সপ্তাহে blocker kill করে থাকেন, তবে আপনার একটি অ্যাপ্লিকেশন ডিজাইন সমস্যা অপারেশনের ছদ্মবেশ পরে আছে।

৫. Deadlock আর ORA-00060

একটি deadlock হলো বিশেষ, পারস্পরিক একটি block। Session A row ১ ধরে আছে আর row ২ চায়; session B row ২ ধরে আছে আর row ১ চায়। কেউই এগোতে পারে না, আর কেউই কখনও ছাড়বে না - সত্যিকারের একটি অচলাবস্থা।

Oracle এটি স্বয়ংক্রিয়ভাবে সামলায়। তার deadlock-detection কোড কয়েক সেকেন্ডের মধ্যে cycle-টি খেয়াল করে এবং একটি session-এর একটি statement rollback করে (পুরো transaction নয়) তা ভেঙে দেয়, সেই session তখন পায়:

ORA-00060: deadlock detected while waiting for resource

এটি গুরুত্বপূর্ণ এবং ব্যাপকভাবে ভুল-বোঝা: একটি deadlock আপনার ডেটাবেজকে হ্যাং করায় না। Oracle একটি statement বিসর্জন দিয়ে কয়েক সেকেন্ডে এটি মিটিয়ে ফেলে। শিকার session error পায়; অন্যটি এগোয়। তাই ORA-00060 কোনো মারাত্মক ঘটনা নয় - এটি Oracle আপনাকে এমন একটি অ্যাপ্লিকেশন থেকে রক্ষা করছে যা অসম্ভব কিছু চেয়েছিল। কিন্তু এটি অবশ্যই এই সংকেত যে আপনার অ্যাপ্লিকেশনে একটি lock-ordering বাগ আছে।

৬. Deadlock Trace পড়া

প্রতিটি ORA-00060 একটি trace ফাইল লেখে (আর একটি alert-log এন্ট্রি যা সেটির দিকে নির্দেশ করে)। এতে একটি 'Deadlock graph' থাকে যা জড়িত হুবহু row, object আর SQL-এর নাম বলে দেয় - কারণটি ঠিক করতে যে প্রমাণ আপনার দরকার।

-- Find recent deadlock traces
SELECT name, value FROM v$diag_info WHERE name='Diag Trace';
-- look in that directory for *ora*.trc files around the alert-log timestamp

Trace-এ, deadlock graph দুটি session দেখায়, প্রত্যেকে কী HOLDS আর কী WAITS-FOR করছে, সঙ্গে SQL আর ROWID। দশবারের নয়বার এটি একই গল্প ফাঁস করে: দুটি code path একই দুটি টেবিল (বা row) বিপরীত ক্রমে update করছে। ওটাই বাগ - আর তা ঠিক করা যায়।

৭. আসল সমাধানগুলো (সবই অ্যাপ্লিকেশনে)

আপনি প্যারামিটার দিয়ে deadlock থেকে বেরোতে পারবেন না; সমাধানগুলো ডিজাইন পরিবর্তন:

• সামঞ্জস্যপূর্ণ access order। এক নম্বর নিরাময়। যদি প্রতিটি transaction child-এর আগে parent, আর table B-র আগে table A update করে, তবে cyclic deadlock অসম্ভব হয়ে যায়। কোডে একটি প্রামাণিক ক্রম প্রয়োগ করুন।
• ছোট transaction। কাজের যৌক্তিক এককটি শেষ হওয়ামাত্র commit করুন। কখনও user interaction বা external call জুড়ে lock ধরে রাখবেন না। 'FOR UPDATE তারপর ব্যবহারকারীর জন্য অপেক্ষা' প্যাটার্নটির মৃত্যু জরুরি।
• SELECT ... FOR UPDATE NOWAIT / SKIP LOCKED। অনির্দিষ্টকাল block হওয়ার বদলে দ্রুত fail করুন (NOWAIT) অথবা ইতিমধ্যে lock করা row বাদ দিন (SKIP LOCKED, queue-processing worker-এর জন্য আদর্শ)। এটি নীরব blocking-কে একটি সামলানো অবস্থায় বদলে দেয়।
• কাজের আকার ঠিক রাখুন। এক transaction-এ দশ লাখ row update করলে পুরো সময়টা দশ লাখ lock ধরে থাকে; ছোট ছোট commit-এ ব্যাচ করা (যেখানে ব্যবসার নিয়মে অনুমতি দেয়) lock-এর আয়ু কমিয়ে দেয়।
• Unindexed foreign key থেকে সাবধান। একটি foreign key কলামে index না থাকলে parent key বদলানোর সময় Oracle পুরো child টেবিলে একটি share lock নেয় - অপ্রত্যাশিত blocking-এর একটি কুখ্যাত, সহজে-ঠিক-হওয়া উৎস।

৮. একটি বাস্তব কেস: রাতের Deadlock ঝড়

একটি ম্যানুফ্যাকচারিং ERP প্রতি রাতে ১টা থেকে ২টার মধ্যে একগুচ্ছ ORA-00060 error লগ করত। অ্যাপ্লিকেশন টিম একটি ডেটাবেজ বাগ ধরে নিয়ে আমাদের 'lock limit বাড়াতে' বলল (যা বলে কিছু নেই)। Deadlock trace এক পড়াতেই আসল গল্পটা বলে দিল: দুটি batch job - inventory revaluation আর order settlement - দুটোই ITEMS আর LEDGER টেবিল update করত, কিন্তু বিপরীত ক্রমে। যখন তারা ওভারল্যাপ করত, deadlock হতো, Oracle একটি statement kill করত, job আবার চেষ্টা করত, আর গোলমালটা বারবার হতো।

সমাধানে কোনো ডেটাবেজ প্যারামিটার ছোঁয়া হয়নি। আমরা দুটি job-কেই LEDGER-এর আগে ITEMS update করার জন্য (একটি সামঞ্জস্যপূর্ণ ক্রম) সারিবদ্ধ করলাম, আর একটি অতিরিক্ত-বড় transaction ছোট করলাম যা মিনিটের পর মিনিট lock ধরে রাখছিল। রাতের deadlock পুরোপুরি থেমে গেল। পথে যে unindexed foreign key পেলাম সেটিও তার index পেল, যা একটি আলাদা দিনের-বেলার blocking সমস্যা সরিয়ে দিল। বেশিরভাগ concurrency সমস্যার মতোই, ডেটাবেজ নিখুঁতভাবে আচরণ করছিল - সে বিশ্বস্তভাবে এমন একটি অ্যাপ্লিকেশন চালাচ্ছিল যা দুটি job-কে ভুল ক্রমে একই lock ধরতে বলেছিল।

সাধারণ জিজ্ঞাসা (FAQ)

Oracle-এ blocking আর deadlock-এর পার্থক্য কী?

Blocking একমুখী ও সাময়িক: session B এমন একটি row-এর জন্য অপেক্ষা করে যা session A lock করে রেখেছে, আর A commit করলেই B এগোয়। একটি deadlock (ORA-00060) পারস্পরিক: B-র যা দরকার তা A ধরে আছে আর A-র যা দরকার তা B ধরে আছে, তাই কেউই এগোতে পারে না। Oracle deadlock শনাক্ত করে এবং একটি statement rollback করে সেটি স্বয়ংক্রিয়ভাবে ভেঙে দেয়; সাধারণ blocking-এ সে হস্তক্ষেপ করে না।

Deadlock কি Oracle ডেটাবেজকে ক্র্যাশ বা হ্যাং করায়?

না। Oracle-এর deadlock detection কয়েক সেকেন্ডের মধ্যে cycle-টি খেয়াল করে এবং একটি session-এর একটি statement rollback করে তা মিটিয়ে ফেলে, সেই session ORA-00060 পায়। ডেটাবেজ চলতেই থাকে আর অন্য session এগোয়। Deadlock একটি অ্যাপ্লিকেশন lock-ordering বাগের সংকেত, ডেটাবেজ ব্যর্থতা নয়।

কোন session অন্যদের block করছে তা কীভাবে খুঁজে পাব?

V$SESSION-এ BLOCKING_SESSION IS NOT NULL দিয়ে query করুন - ওই কলামটি সরাসরি blocker-এর নাম বলে দেয়। chain তৈরি হলে root blocker খুঁজতে একটি CONNECT BY tree ব্যবহার করুন, আর blocker-এর STATUS ও LAST_CALL_ET দেখুন: বড় idle সময়সহ একটি INACTIVE session সাধারণত অ্যাপ্লিকেশনের খোলা রেখে যাওয়া একটি transaction।

ORA-00060 deadlock কীসে ঘটে?

প্রায় সবসময়ই অ্যাপ্লিকেশন ডিজাইন: দুটি code path একই টেবিল বা row বিপরীত ক্রমে update করছে, অথবা লম্বা transaction user think-time জুড়ে lock ধরে রাখছে। Deadlock trace ফাইল হুবহু SQL ও row-গুলোর নাম বলে দেয়। সমাধানগুলো অ্যাপ্লিকেশনে - সামঞ্জস্যপূর্ণ access order, ছোট transaction, আর NOWAIT/SKIP LOCKED - ডেটাবেজ প্যারামিটারে নয়।

Blocking ও deadlock কীভাবে প্রতিরোধ করব?

Transaction ছোট রাখুন আর দ্রুত commit করুন; কখনও user interaction জুড়ে lock ধরে রাখবেন না; টেবিল ও row update করার একটি সামঞ্জস্যপূর্ণ ক্রম প্রয়োগ করুন; অপেক্ষার বদলে দ্রুত fail করতে SELECT ... FOR UPDATE NOWAIT বা SKIP LOCKED ব্যবহার করুন; আর parent key বদলালে whole-table share lock এড়াতে foreign key কলামে index দিন।