TCL - Concurrent Transaction

Oracle database merupakan RDBMS yang multi user, sehingga memungkinkan untuk paralel transactions (berinteraksi secara bersamaan) ini disebut Concurrent Transactions.

Misalnya seperti, coba buka dua windows sqlplus kemudian login dengan user yang sama lalu jalankan perintah berikut:

No	User 1	User 2
1	select * from department where department_id in (10, 20)	select * from department where department_id in (10, 20)
2	Lakukan Update department_id = 10 set department_name = 'Administrator'	Lakukan Update department_id = 20 set department_name = 'Market'
3	select * from department where department_id in (10, 20)	select * from department where department_id in (10, 20)
4	commit	commit
5	select * from department where department_id in (10, 20)	select * from department where department_id in (10, 20)

Berikut hasilnya:

Session 1

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME
from DEPARTMENTS
where DEPARTMENT_ID in (10, 20);

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
        10 Admin
        20 Marketing

SQL> update DEPARTMENTS set DEPARTMENT_NAME = 'Administrator'
where DEPARTMENT_ID = 10;

1 row updated.

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME
from DEPARTMENTS
where DEPARTMENT_ID in (10, 20);

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
        10 Administrator
        20 Marketing

SQL> commit;

Commit complete.

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME
from DEPARTMENTS
where DEPARTMENT_ID in (10, 20);

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
        10 Administrator
        20 Market

Session 2

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME
from DEPARTMENTS
where DEPARTMENT_ID in (10, 20);

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
        10 Admin
        20 Marketing

SQL> update DEPARTMENTS set DEPARTMENT_NAME = 'Market'
where DEPARTMENT_ID = 20;

1 row updated.

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME
from DEPARTMENTS
where DEPARTMENT_ID in (10, 20);

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
        10 Admin
        20 Market

SQL> commit;

Commit complete.

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME
from DEPARTMENTS
where DEPARTMENT_ID in (10, 20);

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
        10 Administrator
        20 Market

Jadi kesimpulannya, selama session belum di commit maka oracle akan membuat isolate transaction artinya tidak akan berpengaruh ke session lain.

Back to top ↑