Melihat Tabel Yang Sedang di-Lock (Oracle)

Oleh : Achmad Faisol

Locking (mekanisme penguncian obyek) sangat diperlukan untuk transaksi maupun stock opname. Misal user ali ingin memeriksa stok barang di gudang saat ini, maka ali harus melakukan locking agar user lain tidak melakukan transaksi (merubah, menambah atau menghapus) di table barang. Hal ini untuk menjaga konsistensi data.

Permasalahan yang ada yaitu bila ali melakukan locking table barang, lalu ada user lain akan melakukan transaksi, maka user tersebut harus menunggu sampai ali melepas lockingnya. Secara default, Oracle tidak memberi pesan bahwa terjadi locking di table yang sedang diakses dan tampilan program di user tersebut dalam kondisi menunggu.

Pada dasarnya, DBA bisa melihat obyek apa saja yang sedang di-lock. Lalu, bagaimana agar semua user bisa melihat juga? DBA harus kreatif dengan cara membuat sendiri view yang bisa dilihat oleh semua user apakah table yang akan diakses sedang di-lock atau tidak. Bila table yang akan diakses sedang dilock, maka user diminta tidak melakukan transaksi. Untuk pemrograman visual, bisa ditampilkan pesan (message atau alert).

Berikut ini akan penulis sajikan cara membuat view yang bisa diakses oleh semua user untuk mengetahui apakah sebuah table sedang di-lock atau tidak.

1. Buat view untuk melihat table yang sedang di-lock dan grant semua user (public) agar bisa melihatnya.

SQL> connect sys/inix2010 as sysdba
SQL> create or replace view v_locked_table as
select d.owner as yg_punya_tabel,
d.object_name as nama_tabel,
s.username as yg_ngelock
from dba_objects d,v$lock l, v$session s
where d.object_id=l.id1 and l.sid = s.sid;

SQL> create public synonym v$locked_table
for v_locked_table;

SQL> grant select on v$locked_table to public;

2. Login sebagai scott, buat tabel barang lalu grant ke semua user agar bisa melakukan transaksi ke tabel barang.

SQL> connect scott/tiger
SQL> create table barang (
kode_barang varchar2(3),
nama_barang varchar2(25),
jumlah_barang number(3),
harga_satuan number
);

SQL> insert into barang values
('B01','Beras Rajalele',100,5000);
SQL> insert into barang values
('B02','Beras Cap Burung Pelikan',100,5500);
SQL> insert into barang values
('G01','Gula Pasir Biasa',10,4500);
SQL> insert into barang values
('G02','Gula Pasir Gulaku',10,7000);
SQL> commit;

SQL> grant all on barang to public;

3. Buat 2 orang operator untuk melakukan transaksi ke tabel barang, yaitu ali dan budi

SQL> connect system/inix2010

SQL> create user ali identified by ali2010;
SQL> grant create session to ali;

SQL> create user budi identified by budi2010;
SQL> grant create session to budi;

4. Ali sedang stock opname tabel barang dicocokkan dengan kondisi di gudang. Ali login dan melakukan lock tabel barang agar user lain tidak melakukan transaksi di tabel barang. Sebelum melakukan lock, Ali harus melihat dulu apakah tabel barang sedang di-lock oleh user lain atau tidak. Buka SQL window I.

SQL> connect ali/ali2010
SQL> select * from v$locked_table
where nama_tabel ='BARANG';

no rows selected (tabel barang tidak sedang di-lock)

SQL> lock table scott.barang in share mode;
Table(s) Locked.

=> ali melihat seluruh isi tabel barang dan melakukan lock. User lain tetap bisa melihat tabel scott.barang tapi tidak bisa melakukan transaksi (insert, delete, update).

5. Budi ingin melakukan transaksi di tabel barang, misal menambah item. Budi harus melihat dulu apakah tabel barang sedang di-lock atau tidak. Buka SQL Window II.

SQL> connect budi/budi2010
SQL> column YG_PUNYA_TABEL format a20
SQL> column NAMA_TABEL format a20
SQL> column YG_NGELOCK format a20

SQL> select * from v$locked_table
where nama_tabel='BARANG';

YG_PUNYA_TABEL NAMA_ TABEL YG_NGELOCK
-------------------- -------------------- -------------
SCOTT BARANG ALI

Terlihat bahwa tabel scott.barang sedang di-lock oleh Ali. Oleh karena itu budi harus menunggu sampai tabel tersebut tidak di-lock. Untuk pemrograman visual bisa ditampilkan sebuah pesan (message atau alert) kepada user budi bahwa tabel barang sedang di-lock.

Apabila budi tetap ingin melakukan transaksi, maka budi harus menunggu sampai ali melepas lock dengan perintah rollback. Adapun tampilan program di user budi tergantung user interface yang digunakan. Jika menggunakan SQL* Plus, posisi kursor menunggu (tidak muncul prompt SQL>). Apabila menggunakan pemrograman visual misalnya VB.Net, maka tampilan tergantung programmer.