همان‌طور که می‌دانید، وقتی در یک تراکنش رکوردی حذف یا بروزرسانی می‌شود، گاهی لازم است تغییر انجام‌شده به حالت قبلی بازگردد (rollback). برای مثال، کاربری رکوردی را بروزرسانی می‌کند و بلافاصله متوجه می‌شود که باید این تغییر را لغو کند. در اکثر دیتابیس‌های رابطه‌ای مدرن که ACID را به‌طور کامل پشتیبانی می‌کنند، این امکان وجود دارد، اما مکانیزم اجرایی آن در هر دیتابیس می‌تواند متفاوت باشد.

در این مقاله قصد داریم راهکارهای دیتابیسهای اوراکل و پستگرس را برای مدیریت تراکنش‌ها با نگرش بازگردانی تغییرات با یکدیگر مقایسه کنیم. با توجه به طولانی شدن مطلب، تمرکز این متن صرفاً بر جنبه بازگردانی تغییرات است و سایر جنبه‌ها مانند مکانیزم‌های Locking در مقاله‌ای جداگانه بررسی خواهند شد.

راهکار اوراکل برای بازگردانی تغییرات

در دیتابیس اوراکل، در زمان اجرای دستور DELETE یا UPDATE ابتدا نسخه‌ی فعلی رکورد (قبل از تغییر یا حذف) در Undo ذخیره می‌شود و سپس تغییر در جدول اعمال می‌شود. بنابراین، به جدول هیچ رکورد جدیدی اضافه نمی‌شود.  به عبارتی دیگر، نسخه قدیمی رکوردها در دیتافایل(های) مجزایی نگهداری می‌شوند و در دیتافایل(های) مربوط به جدول اصلی ذخیره نخواهند شد. اگر کاربر تصمیم بگیرد عملیات را ROLLBACK کند، تغییرات از طریق فضای Undo بازیابی می‌شوند و رکوردها به حالت اولیه برمیگردند.

همچنین اوراکل با بهره‌گیری از داده‌های موجود در Undo Tablespace می تواند Read Consistency را تضمین کند و کوئری‌های همزمان کاربران را بدون تأثیر از تراکنش‌های جاری پاسخ دهد.

مثال زیر را ببینید:

SQL> select * from vahid.tb;
  ID NAME      LAST_NAME     
---- -------   ------------- 
   1 Vahid     Yousefzadeh   

میخواهیم id را از 1  به 2 تغییر دهیم:

--session 1
SQL> update vahid.tb set id=2 where id=1;
1 row updated.

هنوز تغییر مورد نظر commit نشده بنابرین صرفا در این Session قابل رویت است:

--session 1:
SQL> select * from vahid.tb;
ID NAME     LAST_NAME      
--- -------  -------------- 
  2 Vahid    Yousefzadeh   

همچنین sessionهای دیگر می توانند از طریق undo، مقدار قبلی را مشاهده کنند تا زمانی که کاربر مورد نظر، تراکنش را commit و یا rollback کند:

--session 2
SQL> select * from vahid.tb;
        ID NAME                 LAST_NAME
---------- -------------------- --------------------
         1 Vahid                Yousefzadeh

این رکورد از undo tablespace که فایل(یا فایلهای) مجزایی دارد، خوانده شده است. برای اثبات این مسئله کافیست تا trace را برای هر دو session فعال کنیم. قبل از فعال کردن trace، بهتر است buffer cache را flush کنیم تا اوراکل مجبور شود اطلاعات را از دیسک به حافظه منتقل کند:

SQL> alter system flush BUFFER_CACHE;
System altered.

همچنین باید شماره دیتابیلهای مربوط به undo tablespace و users را مشخص کنیم(جدول اصلی در tbs ذخیره می شود):

#user tablespace
SQL> select file# from v$datafile where name like '%o1_mf_tbs%';
     FILE#
----------
        16

#undo tablespace
SQL> select file# from v$datafile where name like '%undotb000.dbf';
     FILE#
----------
        17

فایل trace نشان می دهد اوراکل برای پاسخ به کوئری فوق در Session 2 به سراغ دیتافایل undo tablespace هم رفته است(file#=17):

WAIT #139653680870088: nam='db file sequential read' ela=10 file#=17 block#=80 blocks=1 obj#=0 tim=27539759546
WAIT #139653680870088: nam='db file sequential read' ela=10 file#=17 block#=506 blocks=1 obj#=0 tim=27539759622

اما در session 1 که تغییرات را روی رکورد ایجاد کرده و تراکنشی در حال اجرا دارد، صرفا  به دیتافایل 16 رجوع شده است(file#=16):

select * from vahid.tb
END OF STMT
PARSE #140311865900152:c=102250,e=118238,p=65,cr=167,cu=0,mis=1,r=0,dep=0,og=1,plh=2878482057,tim=27466892306
EXEC #140311865900152:c=13,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2878482057,tim=27466892499
WAIT #140311865900152: nam='db file sequential read' ela=13 file#=16 block#=810 blocks=1 obj#=72703 tim=27466892582
WAIT #140311865900152: nam='db file scattered read' ela=14 file#=16 block#=811 blocks=5 obj#=72703 tim=27466892678

بررسی اندازه جدول و undo بعد از delete و update سنگین

در دیتابیس زمانی که حجم زیادی از رکوردها حذف و یا بروزرسانی میشوند، اندازه جدول تغییری نخواهد کرد اما undo tablespace به صورت موقت رشد می کند. البته فضای undo پس از پایان تراکنش برای بقیه تراکنشها قابل استفاده مجدد است و زمانی که تراکنش خاتمه پیدا می کند، محتویات مربوط به ان تراکنش در undo هم قابل استفاده مجدد هستند(مگر انکه تنظیماتی توسط DBA اعمال شود).

سناریوی زیر را ببینید.

اندازه فایل undo:

SQL> select bytes/1024/1024 size_MB from v$datafile where name like '%undotb000.dbf';
   SIZE_MB
----------
         1

اندازه جدول vahid.tb:

SQL> select bytes/1024/1024 size_MB from dba_segments where segment_name='TB';
   SIZE_MB
----------
       446

بروزرسانی رکوردهای جدول

اندازه جدول 446 مگابایت و اندازه دیتافایل undo هم 1 مگابایت هست. همه رکوردهای جدول را بروزرسانی می کنیم:

SQL> update vahid.tb set OWNER='VAHID';
2721736 rows updated.

حجم جدول و دیتافایل undo را مجددا بررسی می کنیم:

SQL> select bytes/1024/1024 size_MB from v$datafile where name like '%undotb000.dbf';
   SIZE_MB
----------
       438

SQL> select bytes/1024/1024 size_MB from dba_segments where segment_name='TB';
   SIZE_MB
----------
       446

همانطور که مشاهده می کنید، حجم undo و جدول به هم نزدیک شده اند البته حجم جدول رشدی نداشته است. در صورتی که نگران فضای مصرفی undo هستیم، می توانیم undo tablespace را حذف کنیم و undo tablespace دیگری را ایجاد کنیم(در صورتی که تراکنش فعالی روی undo tablespace نباشد، قابل حذف است).

حذف رکوردهای جدول

حال تراکنش در حال اجرا را commit می کنیم و اطلاعات این جدول را به طور کلی حذف می کنیم. البته قبل از اجرای دستور delete، تنظیماتی را اعمال می کنیم تا اوراکل از فضای 446مگابایت قبلی مربوط به undo مجددا استفاده کند و اطلاعات قبلی را در undo نگه ندارد:

SQL>  alter database datafile '/opt/oracle/oradata/FREE/FREEPDB1/undotb000.dbf' autoextend off;
Database altered.

SQL> delete vahid.tb;
2721736 rows deleted.

حجم جدول و دیتافایل undo را مجددا بررسی می کنیم:

SQL> select bytes/1024/1024 size_MB from v$datafile where name like '%undotb000.dbf';
   SIZE_MB
----------
       461

SQL> select bytes/1024/1024 size_MB from dba_segments where segment_name='TB';
   SIZE_MB
----------
       446

صرفا تغییر جزیی در حجم undo ایجاد شده است ولی حجم جدول همان است.

نکته مهمی که در این جا باید به ان توجه شود آن است که در صورت درج رکوردی در جدول vahid.tb، به صورت پیش فرض از همان فضای قبلی استفاده می شود. البته این فضا از طریق shrink کردن جدول هم به طور کامل قابل بازپس گیری است.

به طور مثال، tb1 که قبلا از روی جدول tb ایجاد شده بود را در مجددا در این جدول درج می کنیم:

SQL> insert into vahid.tb select * from vahid.tb1;
2721736 rows created.
حجم جدول:
SQL> select bytes/1024/1024 size_MB from dba_segments where segment_name='TB';
   SIZE_MB
----------
       454

همانطور که می بینید، حجم جدول تغییر جزیی داشته است این مسئله به این معنی است که از فضای خالی جدول vahid.tb استفاده شده است.

*یکی از نقاط ضعف استفاده از undo tablespace به زمانی برمی گردد که حجم بسیار قابل توجهی از رکوردها باید rollback شوند، در این حالت، برگرداندن تغییرات ممکن است ساعتها دیتابیس را درگیر کند و منابع سرور را مصرف کند.

*از طریق undo tablespace می توانیم از قابلیت flashback هم بهره مند شویم.

 

راهکار پستگرس برای بازگردانی تغییرات

در پستگرس، مدیریت تراکنش‌ها و تضمین Read Consistency با استفاده از مکانیزم MVCC (Multi-Version Concurrency Control) انجام می‌شود. به این صورت که وقتی یک دستور UPDATE اجرا می‌شود، پستگرس نسخه جدیدی از رکورد را ایجاد می‌کند و رکورد قدیمی همچنان در جدول باقی می‌ماند تا سایر تراکنش‌ها بتوانند snapshot خود را ببینند.

در زمان delete و update، نسخه‌های قدیمی در همان جدول حفظ می شوند و زمانی که هیچ تراکنشی به آن‌ها نیاز ندارد، از طریق قابلیت autovacuum پاک می‌شوند(در صورت غیرفعال نبود این قابلیت).

در نتیجه، اگر یک تراکنش بزرگ(با حجم update و delete قابل توجه) بدون commit باقی بماند، حجم جدول افزایش یافته (table bloat)  و کارایی افت می‌کند و یا حتی بعد از commit شدن تراکنش، اگر نسخه‌های قدیمی رکوردها(dead tuple) از جدول VACUUM نشوند، این چالش کماکان پابرجاست.

vahiddb=# select * from tb;
 id | name  |  last_name
----+-------+-------------
  1 | Vahid | Yousefzadeh
(1 row)

با توجه به انکه ویژگی های auto commit و autovacuum به طور پیش فرض در پستگرس فعال است، برای پیش بردن سناریو، این دو قابلیت را غیرفعال می کنیم:

vahiddb=# \set AUTOCOMMIT off
vahiddb=# ALTER TABLE tb SET (autovacuum_enabled = false);
ALTER TABLE

 بعد از اعمال این تغییرات، رکورد مورد نظر را در session 1 بروزرسانی می کنیم:

-session 1
vahiddb=# update tb set id=2 where id=1;
UPDATE 1
vahiddb=*# select * from tb;
 id | name  |  last_name
----+-------+-------------
  2 | Vahid | Yousefzadeh
(1 row)

این تغییر در session 2 قابل مشاهده نیست:

vahiddb=# select * from tb;
 id | name  |  last_name
----+-------+-------------
  1 | Vahid | Yousefzadeh
(1 row)

پستگرس برای همه جداول ستونهایی مخفی ای دارد که می توان از طریق انها اطلاعات بیشتری را در مورد رکوردهای جدول بدست اورد:

--session 1:
vahiddb=*# select ctid,xmin,xmax,id,name,last_name from tb;
 ctid  | xmin | xmax | id | name  |  last_name
-------+------+------+----+-------+-------------
 (0,2) |  546 |    0 |  2 | Vahid | Yousefzadeh
(1 row)

--session 2:
vahiddb=# select ctid,xmin,xmax,id,name,last_name from tb;
 ctid  | xmin | xmax | id | name  |  last_name
-------+------+------+----+-------+-------------
 (0,1) |  545 |  546 |  1 | Vahid | Yousefzadeh
(1 row)

ctid : نشان‌دهنده آدرس فیزیکی رکورد در صفحه و سطر است و درخروجی در دو session فوق متفاوت است چون session 1 تراکنش جدیدی را اجرا کرده که در حال اجرا است.

xmin : مقدار Transaction ID (XID)  تراکنشی که رکورد را ایجاد کرده است. در session 1 مقدار 540 یعنی رکورد توسط تراکنش 540 ایجاد شده، در حالی که رکورد session 2 توسط تراکنش 539 ایجاد شده است.

xmax : نشان‌دهنده Transaction ID  که رکورد را حذف یا بروزرسانی کرده است. مقدار 0 یعنی رکورد هنوز حذف نشده است، مقدار 540 در session 2 یعنی رکورد توسط تراکنش 540 بروزرسانی یا حذف شده است.

همانطور که می بینید، هر دو رکورد در جدول موجود هستند. تراکنش را commit می کنیم.

--session 1:
vahiddb=*# commit;
COMMIT
vahiddb=# select ctid,xmin,xmax,id,name,last_name from tb;
 ctid  | xmin | xmax | id | name  |  last_name
-------+------+------+----+-------+-------------
 (0,2) |  546 |    0 |  2 | Vahid | Yousefzadeh
(1 row)

--session 2:
vahiddb=# select ctid,xmin,xmax,id,name,last_name from tb;
 ctid  | xmin | xmax | id | name  |  last_name
-------+------+------+----+-------+-------------
 (0,2) |  546 |    0 |  2 | Vahid | Yousefzadeh

قبل از اینکه سراغ سناریو بعدی برویم، حجم جدول را می بینیم:

vahiddb=# \dt+ tb
                          List of relations
 Schema | Name | Type  |  Owner   | Persistence | Size  | Description
--------+------+-------+----------+-------------+-------+-------------
 public | tb   | table | postgres | permanent   | 16 kB |
(1 row)

اندازه جدول 16KB هست، حال فرض کنید قرار است روی این جدول ستون id مرتب بروز شود. با حلقه زیر، این id را 100  هزار بار بروزرسانی می کنیم:

vahiddb=# DO $$
vahiddb$# BEGIN
vahiddb$#     FOR i IN 1..100000 LOOP
vahiddb$#         UPDATE tb SET id = id + 1;
vahiddb$#     END LOOP;
vahiddb$# END
vahiddb$# $$;
DO

بعد از این صدهزار بار update، حجم جدول از 16KB به 5136KB افزایش پیدا کرده است:

vahiddb=# \dt+ tb
                           List of relations
 Schema | Name | Type  |  Owner   | Persistence |  Size   | Description
--------+------+-------+----------+-------------+---------+-------------
 public | tb   | table | postgres | permanent   | 5136 kB |
(1 row)

در صورتی که فقط یک رکورد دارد:

vahiddb=# select * from tb;
   id   | name  |  last_name
--------+-------+-------------
 100001 | Vahid | Yousefzadeh
(1 row)

همچنین CTID نشان می دهد که بعد از 100 هزار بار آپدیت، رکورد فعال(آخرین نسخه) به صفحه 636 و اسلات 149 رفته است:

vahiddb=# select ctid,xmin,xmax,id,name,last_name from tb;
   ctid    | xmin | xmax |   id   | name  |  last_name
-----------+------+------+--------+-------+-------------
 (636,149) |  558 |    0 | 100001 | Vahid | Yousefzadeh
(1 row)

بنابرین با هر بروزرسانی رکورد در محل جدیدی کپی می شود و نسخه قبلی dead tuple در نظر گرفته می شود، در نتیجه جدول دچارbloat  شده و نیازمند اجرای VACUUM است.

با توجه به اینکه autovaccum را غیرفعال کردیم، در این لحظه این عملیات را به طور دستی اجرا می کنیم:

vahiddb=# VACUUM FULL tb;
VACUUM
Time: 227.936 ms

* VACUUM FULL جدول را فیزیکی compact می‌کند و البته می تواند هزینه بالایی هم داشته باشد.

vahiddb=# \dt+ tb
                          List of relations
 Schema | Name | Type  |  Owner   | Persistence | Size  | Description
--------+------+-------+----------+-------------+-------+-------------
 public | tb   | table | postgres | permanent   | 16 kB |
(1 row)

بعد از VACUUM ، حجم جدول به 16KB کاهش پیدا کرده است. البته VACUUM با سرعت بسیار خوبی انجام شده است چرا که تراکنش دیگری روی جدول درح حال اجرا نبوده است.

در پایان باید در نظر داشته باشیم که در عمل، autovacuum معمولاً فعال است و Table bloat خیلی سریع اتفاق نمی‌افتد مگر آنکه تراکنش‌های بزرگ یا تنظیمات خاصی اعمال شود.

نتیجه‌گیری:
اوراکل برای بازگردانی تغییرات از فضای جداگانه‌ای به نام undo tablespace استفاده می‌کند و رکوردهای قبلی در جدول اصلی ذخیره نمی شوند. در مقابل، پستگرس با استفاده از MVCC نسخه‌های قدیمی رکوردها را در همان جدول نگه می‌دارد که منجر به افزایش حجم جدول (table bloat) می‌شود و نیازمند اجرای منظم vacuum برای آزادسازی فضا است. این تفاوت ساختاری در مدیریت تراکنش و بازگردانی تغییرات، اثر قابل توجهی بر رفتار و کارایی این دو دیتابیس دارد.