Oracle Lock Management

زمانی که در یک بانک اطلاعاتی کاربران متعددی مشغول خواندن و نوشتن هستند، ممکن است دو کاربر به صورت همزمان قصد اصلاح یک شی را داشته باشند، و بطور بدیهی در صورت عدم کنترل این دسترسی، ممکن است داده ای از بین رفته و یا داده های ان شی ناسازگار شوند. برای مدیریت و کنترل این دسترسی همزمان، اوراکل از مکانیزمی به نام lock استفاده می کند.

توجه اول: در محیط تک کاربره، نیازی به قفل(lock) نخواهیم داشت.

در اوراکل مدیریت قفلها در دو سطح کلی Exclusive و Share انجام می شود که در حالت Exclusive، تنها یک کاربر حق دسترسی به یک شی یا قسمتی از آن را برای انجام تغییرات خواهد داشت و شی از حالت اشتراک خارج خواهد شد ولی در حالت share، هدف از قفل گذاری یک شی، دسترسی و بررسی محتویات و نیز جلوگیری از دستکاری آن شی توسط کاربران دیگر می باشد پس ممکن است چندین کاربر به طور همزمان، شی ایی را در حالت share قفل کرده باشند که در صورت انجام این کار، هیچ کاربری حق نوشتن بر روی شی مورد نظر را نخواهد داشت.

توجه دوم: برای انجام پرس وجو، نیازی به اخذ قفل نخواهد بود.

قفلها در اوراکل انواع مختلفی دارند و ویوهای متعددی در این زمینه وجود دارد که می تواند برای کشف نوع و کیفیت قفلها مفید باشند یکی از ویوهایی که در این زمینه بسیار پرکاربرد است، ویوی v$lock می باشد که در ادامه بعضی از مفاهیم locking را بر اساس فیلدهای این ویو، توضیح خواهیم داد. در ابتدا فیلدهای این ویو را می بینیم:

SQL> desc v$lock;

Name    Type        Nullable

——- ———– ——–

ADDR    RAW(8)      Y                        

KADDR   RAW(8)      Y                        

SID     NUMBER      Y                        

TYPE    VARCHAR2(2) Y                        

ID1     NUMBER      Y                        

ID2     NUMBER      Y                        

LMODE   NUMBER      Y                        

REQUEST NUMBER      Y                        

CTIME   NUMBER      Y                        

BLOCK   NUMBER      Y                        

CON_ID  NUMBER      Y   

ستون Type در این ویو به اختصار به نوع قفلها اشاره دارد که دو نوع مهم آن، در ادامه اورده شده است:

نوع اول) TX: این نوع از قفلها به دستورات  DMLای اشاره دارد و در سطح رکورد قابل انجام می باشد. در این حالت، خواندن بدون وقفه انجام می شود و همچنین نویسنده هم منتظر خواننده نمی شود و این دو از هم منفک هستند. همچنین در صورتی که دو نویسنده قصد دسترسی به یک رکورد را داشته باشند(برای نوشتن)، یکی از ان دو، قربانی خواهد شد و تا زمان انجام rollback یا commit، باید در حالت انتظار قرار بگیرد.

نوع دوم) TM: به قفل در سطح جدول اشاره دارد به طور مثال وقتی که فردی شروع به update یک رکورد به خصوص می کند، جدول آن رکورد نباید حذف و یا اصلاح ساختاری(alter) شود که قفل از نوع TM، این هدف را تامین می کند نتیجه آنکه، با اجرای هر یک از دستورات DMLای، به ناچار باید این نوع از قفل هم بر روی جدول اعمال شود.

نکته: علاوه بر انواع آورده شده، نوعهای دیگری از قبیل UL که user-defined lock هستند و با پکیج DBMS_LOCK تعریف می شوند هم وجود دارند البته lockهای داخلی اوراکل هم تنوع قابل توجهی دارند که در فیلد type قابل مشاهده می باشند.

توجه سوم: قفلها تا زمانی که rollback و یا commitای رخ ندهد، ازاد نخواهد شد. البته دستورات DDLای هم با خود commit را به همراه دارند.

در ویوی v$lock ستونی با عنوان LMODE وجود دارد که می تواند از 0 تا 6 مقدار بگیرد هر چه این مقدار بالاتر باشد، بیانگر قوی تر بودن سطح locking می باشد هر کدام از اعداد معانی که در ادامه خواهند آمد را بیان می کنند:

0 – none
1 – null (NULL)
 2 – row-S (RS)
 3 – row-X (RX)
 4 – share (S)
 5 – S/Row-X (SRX)
 6 – exclusive (X)

توجه چهارم: اوراکل به طور پیش فرض، تا جایی که ممکن است، پایین ترین سطح قفل را به یک کار اختصاص می دهد.

ستون CTIME در این ویو، مدت زمانی که قفل در اختیار گرفته شده و یا مدت زمان انتظار را بر اساس ثانیه نشان می دهد.

ستون مهم دیگری که در این ویو وجود دارد، request می باشد که نشان می دهد چه افرادی چه قفلی را تقاضا کرده اند و در حالت انتظار قرار دارند به عبارتی دیگر، blocking sessionها را نشان خواهد داد. عدد 0 در این ستون نشان می دهد که فرد درخواستی ندارد :

select distinct l.SID,l.TYPE,l.LMODE,l.REQUEST,l.CTIME from v$lock l where l.SID in(‘2844’,’1898’) and l.TYPE !=’AE’;

SID TYPE LMODE REQUEST CTIME
1898 TM 3 0 59
1898 TX 6 0 59
2844 TM 0 5 55
2844 TM 3 0 55

توجه پنجم: زمانی که چند کاربر درخواست قفل یک شی را داشته باشند، به ترتیب اولی مالک قفل خواهد شد و بقیه در یک صف(enqueue) قرار می گیرند و در صورت ازاد شدن شی، با الگوریتم FIFO، شی مورد نظرشان را قفل خواهند کرد(قفل بر اساس ترتیب درخواست ها، تخصیص داده می شود).

ستون ID1 و ID2 دو مورد کاربرد اساسی دارند اول اینکه اگر نوع قفل برابر با TM باشد، ID1 شماره شی را نشان می دهد که با یک پیوند ساده با ستون dba_objects.object_id خواهیم فهمید که این شماره، به چه جدولی اشاره دارد مقصود انکه، با استفاده از این فیلد، شی ایی که قفل شده است، مشخص خواهد شد.

همچنین اگر بین دو رکورد شرط زیر برقرار باشد:

Id1=id1 and id2=id2

بیانگر ان است که یکی از این دو blocker و دیگری waiter می باشند البته رکوردی که فیلد request آن بزرگتر از صفر باشد، waiter خواهد بود.

فیلد block دو مقدار به خود می گیرد که 0 بیانگر آن است که کاربر مورد نظر، فرد دیگری را بلاک نکرده است و عدد 1، عکس آن را نشان می دهد. پس در بیانی کاملتر، برای یافتن blocking session باید شرایط زیر برقرار باشد:

FROM v$lock l1, v$lock l2

WHERE

   l1.block = 1 AND

   l2.request > 0 AND

   l1.id1 = l2.id1 AND

   l1.id2 = l2.id2

توجه ششم: زمانی که کاربر اول خواستار مالکیت شی ای باشد که کاربر دوم آن را قفل کرده است و همچنین کاربر دوم هم بخواهد به شی تصاحب شده به دست کاربر اول دسترسی پیدا کند، این اتفاق سبب بن بست خواهد شد و در این صورت، کاربر اول با خطای زیر مواجه خواهد شد:

ORA-00060: deadlock detected while waiting for resource

در ادامه سطوح مختلف قفلها که در فیلد LMOD به آنها اشاره شد، به تفصیل مورد بررسی قرار می گیرد.

1.ROW SHARE (RS): این نوع از قفلها، بسیار ارزان و کم هزینه می باشند و به غیر از قفل Exclusive، از دیگر قفلها ممانعت نخواهند کرد و در نتیجه امکان انجام عملیات DMLای برای همگان ممکن خواهد بود ولی عملیات DDLای که نیازمند قفل Exclusive می باشند، امکان اجرا نخواهند داشت. این قفل با استفاده از دو دستور زیر، قابل تحصیل می باشد:

Lock table USEF_TBL in row share mode;

SELECT … FROM table …    FOR UPDATE OF …;

مثال زیر، ممانعت این قفل از عملیات DDLای بر روی جدول usef_tbl را نشان می دهد:

–session 1:

SQL> Lock table USEF_TBL in row share mode;

select distinct  (select distinct round(l.ctime/60,2) from v$lock l where l.SID=lb.SESSION_ID and l.LMODE=lb.LOCKED_MODE and l.TYPE not in (‘AE’)) MIN, b.OWNER,b.OBJECT_NAME,lb.SESSION_ID, decode(lb.LOCKED_MODE,0,’none’,1,’null’,2,’ROW SHARE (RS)’,3,’ROW EXCLUSIVE (RX)’,4,’SHARE(S)’,5,’SHARE ROW EXCLUSIVE(SRX)’,6,’EXCLUSIVE(X)’) “LOCK_MODE” from v$locked_object  lb ,dba_objects b where  b.OBJECT_ID=lb.OBJECT_ID and b.OBJECT_NAME=’USEF_TBL’ ;

MIN OWNER OBJECT_NAME SESSION_ID LOCK_MODE
0.35 USEF USEF_TBL 1707 ROW SHARE (RS)

–session 2:

SQL> drop table USEF_TBL ;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

البته در این حالت، عملیات DMLای برای sessionهای دیگر به راحتی قابل انجام می باشد:

–session 2:

SQL> delete usef_tbl;

3 rows deleted

نکته: شماره این قفل در LOCKED_MODE برابر با 2 می باشد.

2.ROW EXCLUSIVE (RX): زمانی که عملیات delete، insert و update انجام می پذیرد، این نوع قفل اخذ می شود به طور دقیق تر، با اجرای دستورات زیر، این نوع از قفل، بدست می اید:

INSERT INTO table … ;

UPDATE table … ;

DELETE table … ;

همچنین می توان با استفاده از دستور زیر، این نوع از قفل را بدست آورد:

LOCK TABLE table IN ROW EXCLUSIVE MODE;

استفاده از قفل Row Exclusive سبب می شود تا session دیگری نتواند جدول مربوطه را به صورتهای Exclusive، share و یا share row Exclusive قفل کند.

مثال:

–session 1:

SQL> delete usef_tbl;

 3 rows deleted

INST SID STATUS USERNAME OWNER OBJECT_NAME OBJECT_TYPE LOCKED_MODE
hrmdb 196 / 48559 INACTIVE USEF USEF USEF_TBL TABLE Row-Exclusive (RX)

 

–session 2:

SQL> drop table usef_tbl;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

نکته: شماره این قفل در LOCKED_MODE برابر با 3 می باشد.

3.SHARE TABLE (S): در صورت بدست اوردن این نوع از قفل توسط sessionای خاص، افراد دیگر می توانند آن جدول را بخوانند و همچنین در صورتی که sessionهای دیگر از این نوع قفل استفاده نکرده باشند(بطور همزمان)، عملیات DMLای برای session اول ممکن خواهد بود پس با داشتن این نوع از قفل توسط یک session، نمی توان مطمئن بود که ان session امکان انجام عملیات DMLای را بر روی جدول مورد نظر دارا می باشد زیرا که ممکن است session دیگری هم آن جدول را در این حالت قفل کرده باشد. در صورت استفاده از قفل share، امکان استفاده همزمان از قفلهای ROW SHARE و SHARE توسط sessionهای دیگر هم ممکن خواهد بود. این قفل با استفاده از دستور زیر قابل دستیابی می باشد:

Lock table usef_tbl in share mode;

مثال زیر نشان می دهد که کاربر شماره یک، جدول usef_tbl را در حالت share قفل کرده است و سبب شده تا کاربر دیگر نتواند بلافاصله عملیات DMLای را بر روی این جدول انجام دهد:

–session 1:

SQL> Lock table usef_tbl in share mode;

MIN OWNER OBJECT_NAME SESSION_ID LOCK_MODE
0.42 USEF USEF_TBL 1708 SHARE(S)

–session 2:

SQL> insert into usef_tbl values(4,’javad’);

Wait…

مورد کاربرد:در صورتی که یک تراکنش قصد داشته باشد اطلاعات جدولی را از ابتدا تا انتهای کارش به یک شکل بخواند طوری که تراکنش دیگری امکان تغییر آن را نداشته باشد، این قفل موثر خواهد بود.

نکته 1: تفاوت دو نوع قفل SHARE و EXCLUSIVE(که در ادامه خواهد آمد)، در آن است که در حالت EXCLUSIVE، امکان تحصیل هیچ شکل دیگری از قفلها توسط sessionهای دیگر ممکن نخواهد بود در صورتی که در حالت SHARE، افراد دیگر هم می توانند این جدول را در حالت share و یا row share قفل کنند.

نکته 2: شماره این قفل در LOCKED_MODE برابر با 4 می باشد.

4. SHARE ROW EXCLUSIVE (SRX): قفل SRX که به SSX هم شناخته می شود، از قفل share table هم محدودتر می باشد به طوری که تنها یک نفر در آن واحد می تواند از این قفل بر روی یک جدول استفاده کند البته استفاده از این قفل، مانع تحصیل قفل ROW SHARE(SR) برای دیگر کاربران دیگر نخواهد شد. کاربران مجاز، تنها می توانند جدولی که در این حالت قفل شده را بخوانند ولی حق هیچگونه تغییری را نخواهند داشت.

این قفل با استفاده از دستور زیر قابل دستیابی می باشد:

Lock table usef_tbl in share row exclusive mode;

5. EXCLUSIVE TABLE (X): قفلیست ضد سرقت!! و تنها یک فرد درآن واحد می تواند از این قفل استفاده کند(بر روی یک شی). با داشتن این نوع از قفل، از حق خواندن کسی جلوگیری نمی شود و دیگران می توانند از این جدول پرس و جو بگیرند ولی جز مالک قفل، هیچ کس حق انجام عملیات DMLای را ندارد.

نکته: شماره این قفل در LOCKED_MODE برابر با 6 می باشد.

تفاوت SRX با S و X:

قفل SRX رابطه بسیار نزدیکی با دو قفل Exclusive و Share دارد ولی در عین حال، وجه تمایزی هم بین این سه وجود دارد. تفاوت SRX با قفل S در ان است که اگر کسی SRX را بدست آورد، همیشه حق انجام دستورات DMLای را خواهد داشت در حالی که این اتفاق در مورد قفل S، به صورت مشروط قابل انجام خواهد بود( به شرط انکه فرد دیگری نباید این قفل را در اختیار بگیرد). همچنین تفاوت قفل SRX با X زمانی مشهود می شود که فردی جدولی را به صورت SRX قفل کرده باشد و فرد دیگری هم، این جدول را به صورت row share در اختیار بگیرد، در این حالت، فرد اول نخواهد تواست دستورات DDLای از قبیل حذف جدول را اجرا کند این در صورتی است که هیچ قفلی در حالت X قابل دستیابی نیست و بالطبع، مالک X، از انجام هر کاری مجاز خواهد بود.

DDL DML  
مشروط مشروط S
مشروط مجاز SRX
مجاز مجاز X

 

مثال : همانطور که در این مثال خواهیم دید، مالک SRX همیشه حق انجام عملیات DMLای را دارد ولی اجرای DDL برای آن مشروط خواهد بود.

–session 1:

Lock table usef_tbl in SHARE ROW EXCLUSIVE mode;

–session 2:

Lock table usef_tbl in  ROW SHARE mode;

–session 1:

drop table usef_tbl;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> delete usef_tbl;

3 rows deleted

بررسی foreign key locking

زمانی که عملیات DMLای بر روی جدول parent انجام می شود، چه قفلی بر روی جدول child اعمال می شود؟ جواب این سوال را در ادامه خواهیم گرفت:

–insert

insert into parent values (33,’usef’);

select type, id1, id2, DECODE (   l.LMODE, 0, ‘None’, 1, ‘NoLock’, 2, ‘Row-Share (RS)’,    3, ‘Row-EXCLUSIVE’, 4, ‘SHARE-Table(S)’, 5, ‘SHARE-Row-EXCLUSIVE (SSX)’,    6, ‘EXCLUSIVE TABLE(X)’, ‘[Nothing]’)  locked_mode, request,(select object_name from dba_objects where object_id=l.ID1 and l.TYPE=’TM’) object from v$lock l where sid = (select sid from v$mystat where rownum = 1) and l.TYPE!=’AE’;

TYPE ID1 ID2 LOCKED_MODE REQUEST OBJECT
TM 427998 0 Row-EXCLUSIVE 0 PARENT
TM 428000 0 Row-Share (RS) 0 CHILD
TX 655363 5393997 EXCLUSIVE TABLE(X) 0  

–delete

delete parent where id=33;

TYPE ID1 ID2 LOCKED_MODE REQUEST OBJECT
TM 427998 0 Row-EXCLUSIVE 0 PARENT
TM 428000 0 Row-EXCLUSIVE 0 CHILD
TX 196612 345317 EXCLUSIVE TABLE(X) 0  

–update

update parent set id=95 where id=33;

TYPE ID1 ID2 LOCKED_MODE REQUEST OBJECT
TM 427998 0 Row-EXCLUSIVE 0 PARENT
TX 655373 5394470 EXCLUSIVE TABLE(X) 0  
TM 428000 0 Row-EXCLUSIVE 0 CHILD

 

نکته 1: با اعمال قفل بر روی یک ویو، در عمل جدول مرجع آن ویو قفل خواهد شد:

Session 1:

create view vw_usef_tbl as select * from usef_tbl;

Lock table vw_usef_tbl in SHARE mode;

Session 2:

delete usef_tbl where id=1;

wait….

نکته 2: امکان قفل دستی جدولی که در بانک دیگری موجود است، با استفاده از dblink وجود دارد:

Lock table usef_tbl_source@dblinkname in SHARE mode;

نکته 3: با استفاده از عبارت nowait و یا wait، می توان در مورد میزان انتطار دستور lock table و یا select … for update نظر داد(در صورت قفل بودن شی مورد نظر). به طور مثال، دستور زیر در صورتی که جدول usef_tbl قفل باشد، بلافاصله از حالت اجرا خارج می شود و به حالت انتظار نخواهد رفت:

Lock table usef_tbl in exclusive mode nowait;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

دستور زیر هم به مدت 5 ثانیه منتظر خواهد ماند و در صورتی که در این مدت زمان، جدول مورد نظر آزاد نشود، اجرای دستور متوقف می شود:

Lock table usef_tbl in exclusive mode wait 5;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

همچنین اند دستورات زیر:

select * from usef_tbl for update nowait;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

select * from usef_tbl for update wait 5;

ORA-30006: resource busy; acquire with WAIT timeout expired

نکته 4: برای بررسی اینکه کدام یک از رکوردها قفل شده اشت، می توانیم از پرس و جوی زیر کمک بگیریم و با جایگذاری sid، رکورد مورد نظر را مشخص خواهیم کرد:

select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) from v$session s, dba_objects do where sid=1333 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_CREATE(1,ROW_
USEF_TBL 427379 4 404725 4 AABoVzAAEAABiz1AAE

 

select * from USEF_TBL where owed=’AABoVzAAEAABiz1AAE’;

ID NAME ROWID
2 hadi AABoVzAAEAABiz1AAE

ویوهای مفید

DBA_WAITERS: چه افرادی منتظر ازاد شدن چه نوع قفلی هستند.

DBA_BLOCKERS: چه افرادی سبب در انتظار blocking session شده اند.

DBA_LOCKS: مشابه v$lock می باشد البته کمی خواناتر.

DBA_DML_LOCKS: همه قفلهای DMLای که مورد نیاز هستند و یا در اختیار تراکنشی قرار دارند را نشان می دهد.

DBA_DDL_LOCKS: قفلهای DDL مورد نیاز و یا تصرف شده را نشان می دهد.

V$LOCKED_OBJECT: شامل لیست تقریبا کاملی از اطلاعات، در مورد مالک قفل، شی قفل شده و … می باشد.

 

جدول زیر خلاصه ای از آنچه که گفته شد را نشان می دهد:

دستور sql نوع قفل

قفلهای مجاز

RS RX S SRX X
SELECT…FROM table… none Y Y Y Y Y
INSERT INTO table … RX Y Y N N N
UPDATE table … RX Y* Y* N N N
DELETE FROM table … RX Y* Y* N N N
SELECT … FROM table FOR UPDATE OF … RS Y* Y* Y* Y* N
LOCK TABLE table IN ROW SHARE MODE RS Y Y Y Y N
LOCK TABLE table IN ROW EXCLUSIVE MODE RX Y Y N N N
LOCK TABLE table IN SHARE MODE S Y N Y N N
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE SRX Y N N N N
LOCK TABLE table IN EXCLUSIVE MODE X N N N N N

 

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد.