استفاده از قابلیت Lock-Free Reservations در اوراکل ۲۳c برای جلوگیری از بلاک شدن session ها + فیلم

مقدمه

بلاک(Block) کردن session ها در پایگاه داده های با منطق RDBMS براساس قابلیت ACID، ابزاری برای جلوگیری از ایجاد داده های خراب است. اما همیشه هم این بلاک شدن session ها خوب نیست و صرفا عملیاتی بیهوده خواهد بود.
به همین منظور اوراکل قابلیت جدیدی را در پایگاه داده ۲۳c معرفی کرده به نام Lock-Free Reservations که بر روی ستون هایی از نوع Number تعریف می شود و می تواند از بلاک شدن های بی مورد جلوگیری کند.

طرح مسئله و بررسی راهکار ارائه شده

فرض کنید جدولی داریم که اطلاعات پایه کتاب ها را نگهداری می کند و یک ستون دارد برابر با مقدار فروش رفته از کتاب، مقدار این ستون با هر فروش باید بروز شود( از ابتدا تا به این لحظه چه تعداد فروش رفته است ). در ادامه دستورات ساخت محیط تست را اجرا می کنیم و سپس در حالت بدون استفاده از قابلیت Lock-Free Reservations و با استفاده از این قابلیت سناریو های مورد نظر را اجرا می کنیم تا نتایج را مشاهده کنیم.

CREATE TABLE books

(

    book_name          VARCHAR2 (20) PRIMARY KEY,

    book_sold_count    NUMBER

);

INSERT INTO books (book_name, book_sold_count)

     VALUES (‘sql’, 0) ,

       (  ‘access’,   ۰ )  ,

       (  ‘oracle’ ,   ۰  )  ,

       (  ‘postgresql’  ,   ۰  );

COMMIT;

 

حالا جدول جزئیات فروش را ایجاد می کنیم . در این جدول مشخص می شود در هر مرتبه فروش، چه تعداد و از چه کتابی فروش رفته است.

CREATE TABLE book_sales

(

    id             NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    book           VARCHAR2(20),

    count_sales    NUMBER,

    CONSTRAINT sales_products_fk FOREIGN KEY (book)

        REFERENCES books (book_name)

 

);

 

 

حال برای اینکه عملیات بروز رسانی تعداد فروش در جدول پایه books به صورت خودکار انجام شود، trigger زیر را بر روی جدول book_sales ایجاد می کنیم.

create or replace trigger book_sale

after insert on book_sales

for each row

begin

  update books p

  set    p.book_sold_count = p.book_sold_count + :new.count_sales

  where  p.book_name = :new.book;

end;

برای اینکه مطمئن شویم که trigger به درستی کار می کند، رکوردهای زیر را وارد جدول جزئیات فروش می کنیم.

insert into book_sales (book, count_sales) values (‘sql’, 10);

insert into book_sales (book, count_sales) values (‘oracle’, 5);

commit;

select * from books;

book_name    book_sold_count

———-   ———-

sql                    ۱۰

access              ۰

oracle              ۵

postgresql       ۰

حالا در ۲ session مختلف عملیات insert را انجام میدیم

 

Session 1

insert into book_sales (book, count_sales) values (‘oracle’, 12);

Session 2

insert into book_sales (book, count_sales) values (‘oracle’, 15);

در این حالت مشاهده خواهید کرد  که به خاطر اینکه دستور در session1 پایدار نشده ( نه commit و نه rollback )  insert در session 2 اجرا نمی شود ( منتظر می ماند ) دراین حالت یا باید دستور commit و یا  rollback را اجرا کرد.

برای مشاهده فیلم  کلیک کنید

استفاده قابلیت Lock-Free Reservations

ابتدا این قابلیت را فعال می کنیم

alter table if exists books modify (book_sold_count reservable);

با فعال کردن این قابلیت، مشاهده می کنید که جدول با نام SYS_RESERVJRNL_79468  ساخنه می شود.

CREATE TABLE SOLTANI.SYS_RESERVJRNL_79468

(

  ORA_SAGA_ID$              RAW(16),

  ORA_TXN_ID$               RAW(8),

  ORA_STATUS$               CHAR(12 BYTE),

  ORA_STMT_TYPE$            CHAR(16 BYTE),

  BOOK_NAME                 VARCHAR2(10 BYTE)   NOT NULL,

  BOOK_SOLD_COUNT_OP        CHAR(7 BYTE),

  BOOK_SOLD_COUNT_RESERVED  NUMBER

 

)

 

 

در این جدول تغییرات در هر session به صورت جداگانه نگهداری میشه سپس بعد از هر coomit و یا rollback رکوردهای مربوطه از این جدول حذف می شوند.
سناریوی قبل را در این حالت اجرا می کنیم.

 

Session 1

insert into book_sales (book, count_sales) values (‘oracle’, 12);

Session 2

insert into book_sales (book, count_sales) values (‘oracle’, 15);

همانطور که مشاهده می کنید دستور در session 2  هم اجرا می شود و در این حالت session بلاک نمی شود. بعد از commit در session ها رکوردهای مذکور از جدول SYS_RESERVJRNL_79468  حذف می شوند.

شرایط استفاده از قابلیت Lock-Free Reservations:

• جدولی که این قابلیت بر روی آن فعال می شود باید دارای PK باشد.
• ستونی که بر روی آن این قابلیت فعال می شود باید از نوع number باشد.
• در هنگام update کردن ستون مذکور، صرفا باید با استفاده از عملگر +  و یا – مقدار را تغییر داد. نمی توان مقدار جدید را مستقیم اعمال کرد ( فقط از همین عملگر ها باید استفاده کرد)
• در دستور update باید از شرطی استفاده کرد که در آن PK استفاده شده باشد.
• اگر رکوردی در جدول journal باشد، رکوردی از جدول اصلی را نمی توان Delete کرد.
• جدول را تا موقعی که این قابلیت فعال باشه نمی توان drop و یا truncate کرد. ( قبلش باید این قابلیت غیر فعال بشه).