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

برای مدیریت و کنترل دسترسی همزمان، اوراکل از مکانیزمی به نام 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ای، به ناچار باید این نوع از قفل هم بر روی جدول اعمال شود.

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

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

ستون LMODE

در ویوی 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

از دیگر ستونهای مهم ویوی v$lock، ستون 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’;

توجه پنجم: زمانی که چند کاربر درخواست قفل یک شی را داشته باشند، به ترتیب اولی مالک قفل خواهد شد و بقیه در یک صف(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’ ;

–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

–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;

–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، از انجام هر کاری مجاز خواهد بود.

مثال : همانطور که در این مثال خواهیم دید، مالک 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’;

–delete

delete parent where id=33;

–update

update parent set id=95 where id=33;

نکته 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 ;

select * from USEF_TBL where owed=’AABoVzAAEAABiz1AAE’;

ویوهای مفید

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

DBA_BLOCKERS: شماره sid بلاکرها را نشان می دهد.

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

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

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

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

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