TUGAS BESAR SISTEM BASIS DATA

IMPLEMENTASI ATOMICITY, CONSISTENCY,

ISOLATION DAN DURABILTY PADA MICROSOFT

SQL SERVER

Disusun oleh :

1. Sukma Aji Triatmojo 113070037

2. Andhy Winastono 113070057

3. Yogy Fresta Rahmawan 113070121

JURUSAN TEKNIK INFORMATIKA

INSTITUT TEKNOLOGI TELKOM

2009

1

ABSTRAKSI

Seperti pada sistem operasi, transaksi-transaksi yang terjadi pada basisdata dijalankan secara konkuren, bukan serial. Meskipun pemrosesan serial menjamin setiap transaksi tetap bersifat ACID (atomicity, consistency, isolation, durability), kesenjangan antara response time CPU dan waktu pengiriman transaksi menjadikan konkurensi sebagai solusi terbaik dalam pemrosesan transaksi. Dengan metode ini, sejumlah transaksi bisa diproses bersamaan karena sistem operasi mampu mengerjakan instruksi-instruksi secara multiprogramming. Akan tetapi, hal ini menimbulkan masalah penjadwalan jika urutan pemrosesan transaksi diserahkan pada sistem operasi. Oleh karena itu, diperlukan komponen pengendali ACID pada software basisdata agar sifat ACID untuk setiap transaksi tetap terjamin. Pada tulisan ini akan dibahas mengenasi pengertian ACID pada suatu transaksi. Kemudian bagaimana penerapannya pada DBMS (Database Management System) khususnya pada Microsoft SQL Server.

Kata kunci : transaksi, ACID, SQL Server

2

DAFTAR ISI

Judul……………………………………………………………………………….1

Abstraksi…………………………………………………………………………..2

Daftar Isi…………………………………………………………………………..3

Bab I Pendahuluan………………………………………………………………...4

Bab II Pembahasan………………………………………………………………..6

II.1 Implementasi Atomicity Pada SQL Server…………………………….6

II.2 Implementasi Concurrency Pada SQL Server………………………….7

II.3 Implementasi Isolation Pada SQL Server……………………………..10

II.4 Implementasi Durability Pada SQL Server……………………………14

Bab III Kesimpulan………………………………………………………………15

III.1 Kesimpulan……………………………………………………………15

3

BAB I

PENDAHULUAN

Makin banyak pengguna yang mengakses basisdata berarti makin besar

peluang terjadinya sejumlah transaksi yang dikirim pada saat bersamaan. Terdapat

dua pilihan bagi sistem operasi untu memprosesnya yaitu secara serial

(bersesuaian dengan urutan terkitim transaksi) atau konkuren (beberapa transaksi

sekaligus dikerjakan dalam waktu yang sama). Sifat konkuren dalam basis data ini

dapat dianalogikan dengan pengaturan tugas secara multiprogramming bagi

sistem operasi. Meski sebenarnya prosesor hanya bisa mengerjakan satu tugas

dalam satu waktu, perbedaan yang cukup signifikan antara kecepatan prosesor dan

kecepatan manusia menimbulkan kesan bahwa prosesor bisa mengerjakan banyak

tugas sekaligus. Secara umum akses ke basis data dapat dilaksanakan dengan dua

operasi yaitu :

a. Read(X) : yaitu proses mengirimkan data X dari basisdata ke buffer

local milik transaksi yang mengeksekusi operasi read (secara fisik

terjadi di ROM, Read Only Memory)

b. Write(X) : yaitu proses menyimpan data X dari buffer local milik

transaksi yang mengeksekusi operasi write ke basisdata (harddisk).

Transaksi dalam basisdata didefinisikan sebagai sejumlah instruksi atau operasi

yang membentuk sebuah satuan kerja lojik. Misalnya dalam contoh kasus klasik

perbankan : sebuah transaksi transfer uang sejumlah satu juta rupiah dari rekening

A ke rekening B. Transaksi ini terdiri dari urutan operasi :

read (A)

A A – 1000000

write (A)

read (B)

B B + 1000000

write (B)

Jika keenam operasi (instruksi) di atas tidak dikerjakan secara keseluruhan dan

berurutan maka transaksi T1 dianggap gagal dan harus diulangi karena setiap

transaksi basisdata harus bersifat ACID (Atomicity, Consistency, Isolation, dan

4

Durability). Atomicity berarti keenam operasi dianggap sebagai satu kesatuan,

jika hanya satu atau lima operasi yang dieksekusi maka dianggap bukan sebagai

satu kesatuan transaksi. Consistency berarti bahwa setelah transaksi dieksekusi

data yang tersimpan di basisdata harus tetap valid. Dalam contoh ini berarti

jumlah saldo A dan B sebelum dan sesudah transaksi harus sama. Isolation berarti

bahwa setiap transaksi dianggap sebagai satu unit terpisah dari transaksi yang

lainnya, sehingga nilai data yang dieksekusi oleh T1 terisolasi dari operasi-operasi

transaksi lainnya. Durability berarti data hasil modifikasi T1 tetap akan tersimpan

meskipun terjadi kerusakan sistem. Data yang dimaksud adalah data yang berhasil

disimpan di harddisk, bukan di memory primer semacam RAM.

5

BAB II

PEMBAHASAN

II.1 Implementasi Atomicity Pada SQL Server

Atomicity menyatakan bahwa modifikasi database harus mengikuti aturan

“semua atau tidak sama sekali”. Dengan adanya fungsi ini, DBMS menjamin

bahwa data yang disimpan akan lengkap pada saat pemrosesan. Sebagai contoh

apabila terdapat proses yang sedang berjalan namun terdapat kegagalan proses

karena kondisi yang tidak diinginkan seperti mati lampu, maka dengan atomicity

ini seluruh data yang telah diproses akan dibatalkan. Jadi, dapat disimpulkan

bahwa apabila terjadi sedikit kegagalan maka akan dibatalkan semuanya untuk

menjaga kesempurnaan data. Contoh transaksi sederhana dalam SQL server

adalah statement modifikasi data.

BEGIN TRAN

UPDATE authors

SET au_fname = 'John'

WHEREau_id = '172-32-1176'

UPDATE authors

SET au_fname = 'Marg'

WHEREau_id = '213-46-8915'

COMMIT TRAN

Pertama SQL server menuliskan kedalam log file terhadap apa yang akan

dikerjakan. Kemudian SQL server mengerjakan statement update dan akhirnya

SQL server menuliskan ke log bahwa pernyataan update telah selesai dilakukan.

Jika yang terjadi ternyata system mengalami kegagalan setelah update pertama,

6

maka tak ada pernyataan update yang dilakukan atau dianggap ekseskusi tak

pernah dilakukan.

II.2 Implementasi Consistency Pada SQL Server

Jika dalam waktu yang sama, misal t1, terkirim dua transaksi T1 dan T2

yang berusaha memodifikasi data yang sama maka software basisdata harus

mengatur urutan eksekusi operasinya agar sifat ACID tetap terjamin. Pengambilan

keputusan tersebut tidak bisa diserahkan kepada sistem operasi, karena akan

muncul banyak kemungkinan penjadwalan, termasuk penjadwalan yang akan

menyebabkan basisdata tidak konsisten. Misalnya jika saldo rekening B

bertambah satu juta rupiah sebelum saldo rekening A berkurang satu juta rupiah.

Dan inilah yang menjadi tugas bagi komponen pengendali konsistensi pada basis

data. Terdapat dua macam metode kendali konkurensi, yaitu :

a. Konsistensi pesimistik : yaitu sinkronisasi eksekusi transaksi

dilakukan mulai dari awal siklus eksekusi transaksi. Baris-baris data

dikunci sebelum dimodifikasi, sehingga tidak ada transaksi lain yang

bisa mengaksesnya, kecuali setelah kunci dibebaskan. Mekanisme

yang digunakan meliputi tiga hal yaitu locking, timestamp, dan hybrid

(gabungan keduanya). Metode pesimistik menjamin perubahan data

bisa dilaksanakan dengan aman. Kekurangan metode ini terletak pada

munculnya delay jika transaksi berjalan lambat atau data hasil

modifikasi transaksinya tidak segera disimpan ke dalam harddisk

(commit). Fase pengendalian metode pesimistik dapat diilustrasikan

sebagai berikut :

Validate Read Compute Write

b. Konsistensi optimistik : yaitu sinkronisasi transaksi ditunda

hingga transaksi selesai dieksekusi. Proses validasi data hanya

dilakukan ketika transaksi hendak menyimpan data secara permanen

ke harddisk. Mekanisme yang digunakan meliputi locking, dan

7

timestamp. Dengan metode optimistik sejumlah transaksi tetap bisa

dieksekusi bersamaan tanpa harus saling menunggu. Kekurangan

metode ini terletak pada kemungkinan terjadinya tumpang tindih data

karena tidak ada jaminan bahwa data yang dimodifikasi adalah data

asli, belum diubah oleh transaksi lainnya. Fase pengendalian metode

optimisti dapat diilustrasikan sebagai berikut :

Read Compute Validate Write

Basisdata SQL server menggunakan mekanisme locking dan row

versioning. Lock berarti bahwa sebelum sebuah session diizinkan untuk

menyimpan data hasil modifikasi (insert, update atau delete), session tersebut

harus mengunci data terlebih dahulu. Data yang dikunci bisa berupa satu baris

data, beberapa baris data, atau bahkan keseluruhan tabel. Ketika sejumlah

transaksi membutuhkan data yang sama, maka transaksi yang pertama kali

meminta akan mendapatkan lock, sedangkan transaksi lainnya harus menunggu

hingga transaksi pertama selesai. Mekanisme antrian terjadi secara otomatis, tanpa

perlu interaksi administrator. Semua lock dilepas pada akhir siklus eksekusi

transaksi. Transaksi dianggap selesai ketika dilakukan commit atau rollback. Jika

transaksi mengalami kegagalan semua lock yang diperoleh transaksi tersebut

akan dibebaskan. Secara default SQL server melakukan locking pada level baris

data. Misalnya ada dua transaksi konkuren, T1 dan T2 yang mengakses baris data

yang sama, maka transaksi bisa memilih di antara lima modus lock, yaitu :

a. Exclusive : query masih diperbolehkan sedangkan aktivitas lainnya

dilarang. Jika T1 sedang mengunci baris data X secara eksklusif. T2

diwajibkan untuk mengantri sampai T1 melepaskan kunci. Lock jenis

ini terutama dibutuhkan keyika hendak menghapus (drop) tabel.

b. Row share : jika T1 sedang mengunci tabel dalam modus ini. T2

juga diperbolehkan mengakses tabel tersebut asal bukan akses

eksklusif.

c. Row exclusive : hamper sama dengan row share, tetapi T2 juga

tidak boleh mengunci secara share. Lock jenis ini secara otomatis

8

diberikan kepada transaksi yang hendak melakukan insert, update atau

delete.

d. Share : T1 dan T2 bisa dieksekusi secara konkutren, tetapi keduanya

tidak boleh memodifikasi tabel yang sedang dikunci. Lock jenis ini

digunakan terutama untuk membuat indeks pada tabel.

e. Share row exclusive : jika T1 menggunakannya untuk melakukan

query data pada tabel. T2 juga diperbolehkan untuk melakukan hal

yang sama, tetapi T2 dilarang memodifikasi data atau mengunci dalam

modus share.

Lock juga bisa diperoleh secara manual misalnya dengan sintaks :

LOCK TABLE [schema.] (table/view) IN moduslock MODE

[NOWAIT]

Perintah tersebut diperlakukan sama seperti permintaan lock lainnya, yaitu harus

masuk dalam antrian jika sebelumnya ada transaksi yang memegang atau meminta

lock. Akan tetapi jika tidak ingin menunggu, argument NOWAIT bisa digunakan.

Misalnya ada dua transaksi, T1 dengan perintah :

BEGIN TRAN

UPDATE employee

SET salary = salary + 20000

WHERE employee_id=107;

UPDATE employee

SET salary = salary * 1.1

WHERE employee_id=108;

COMMIT TRAN

Masing-masing transaksi di atas akan memperoleh lock

a. Row exclusive pada baris data (row) yang akan dimodifikasi

9

b. Share pada tabel yang berisi row.

II.3 Implementasi Isolation Pada SQL Server

Isolation dibutuhkan ketika ada dua atau lebih transaksi yang mengeksekusi

resourse yang sama agar tidak saling mempengaruhi atau dengan kata lain

mengisolasi/memproteksi konsistensi data ketika ada multiple transaksi. Metode

pengisolasian yang dilakukan tergantung dari isolation level yang diatur di sql

server. Sebelun kita membahas level-level dalam isolasi,ada beberapa masalah

yang sering terjadi dengan data ketika terjadi suatu multiple transaksi,yaitu :

a. Dirty Reads  : suatu kondisi dimana suatu transaksi membaca data yang

telah ditulis oleh transaksi lain, namun belum di commit. Hal ini

menyebabkan kemungkinan terjadinya perubahan data yang belum pasti

diakibatkan oleh tansaksi lain yang belum di commit.

b. Non-repeatable reads : suatu kondisi dimana ketika ada transaksi

yang mengakses suatu data dua kali, dan diantara kedua akses itu ada

transaksi lain yang mengakses data tersebut. Hal ini menyabakan transaksi

pertama membaca dua nilai yang berbeda untuk data yang sama.

c. Phantom reads : suatu kondisi dimana ketika ada transaksi yang

tengah mengakases suatu data lebih dari satu kali, ketika itu ada transaksi

kedua yang melakukan insert atau delete rows diantara proses transaksi

pertama. Hal ini dapat menyebabkan adanya munculnya (insert) atau

hilangnya (delete) suatu baris dari sisi transkasi pertama.

d. Lost updates : suatu kondisi dimana ketika ada dua transaksi yang

memodifikasi data yang sama pada waktu yang hampir bersamaan, dan

transaksi lengkap yang pertama hilang.

Sedangkan level isolasi pada sql sever digolongkan menjadi lima yaitu :

a. Read uncommited isolation

10

Level ini memungkinkan suatu transaksi mengakes suatu data dari suatu

transaksi lainnya yang belum di commit. Ketika kita mengaktifkan metode ini

pada sql server,memungkinkan terjadinya suatu transaksi membaca modifikasi

yang uncommited. Nilai dari data dapat berubah dan baris dapat menghilang

(delete) atau bertambah (insert). Pada level ini dapat memungkinkan

terjadinya dirty reads, non-repeatabe reads dan phantom reads.

b. Read commited isolation

Ini adalah level default sql server. Pada metode ini database tidak

mengizinkan suatu transaksi untuk membaca suatu data dari tabel yang berasal

dari transaksi yang belum di commit. Metode ini mencegah terjadinya dirty

reads, namun tidak bisa untuk mencegah terjadinya non-repeatable reads dan

phantom reads. Level ini memiliki kebergantungan dengan level lainnya,

yaitu commited snapshot. Ketika commit snapshot diset off (metode default)

maka sql server akan melakukan suatu mekanisme penguncian (shared locks)

terhadap data yang mencegah transaksi lain untuk melakukan modifikasi rows

ketika operasi lainnya tengah menjalankan operasi pembacaan atau operasi

lainnya. Penguncian shared locks dibuka sebelum pembacaan data selanjutnya

dilakukan. Misal row locks dibuka sebelum row selanjutnya diproses. Page

locks dibuka ketika page-page selanjutnya tengah dibaca dan table locks

dibuka setelah statement selesai. Sedangkan jika commit snapshot on maka

tidak dilakukan mekanisme locks untuk melindungi data dari transaksi

lainnya.

c. Repeatable read isolation

Pada level ini statement tidak dapat membaca data yang telah dimodifikasi

tapi belum di commit oleh transaksi lainnya dan tidak ada transaksi lain dapat

memodifikasi data yang tengah dibaca oleh tranasaksi lain,sampai transaksi

lainnya tersebut selesai. Level ini dapat mencegah terjadinya dirty reads dan

non-repetable reads. Mekanisme shared lock dilakukan disemua data pada

setiap statement di transaksi tersebut sampai transaksi tersebut selesai. Hal ini

mencegah sebuah transaksi untuk melakukan perubahan row yang sedang

dibaca oleh transanksi lainnya. Karena shared lock dilakukan pada akhir

11

transaksi, maka concurrency pada level ini lebih rendah daripada read

commited isolation.

d. Snapshot isolation

Pada level ini transaksi hanya dapat melakukan perubahan data yang kita

commit sebelum dimulainya transaksi. Perubahan data yang dilakukan oleh

transaksi lain setelah transaksi yang pertama dieksekusi, tidak dapat dilihat

pada ekeskusi statement pada transaksi yang pertama. Transaksi snapshot

tidak meminta penguncian suatu data kecuali ketika database di recover. Pada

level ini ketika melakukan pembacaan data tidak melakukan block pada

transaksi lain ketika melakukan penulisan data. Begitu juga proses penulisan

data tidak tidak melakukan blocking pada transaksi yang melakukan snapshot

ketika membaca data. Ketika dilakukan proses roll-back dalam recovery data,

transaksi snapshot akan meminta penguncian suatu data jika data yang

diakses oleh tansaksi lain tersebut melakukan roll-back. Dengan kata lain

transaksi yang melakukan snapshot terblock sampai transaksi lainnya

melakuakn roll-back. Lock terbuka setelah dilakukan grant.

Sebuah transaksi tidak bisa menerapkan level ini jika di awal transaksi ini

menerapkan isolation level yang lain. Jika sebuah transaksi menerapkan

snapshot isolation level di awal, kita dapat merubahnya menjadi isolation

level lainnya dan setelah itu dapat kembali lagi ke snapshot.

e. Serializable isolation

Pada level ini melakukan penguncian untuk mencegah suatu transaksi dari

proses insert atau delete selama data tersebut tengah dibaca oleh proses lain.

Dengan kata lain :

1. Sebuah statement tidak dapat membaca data yang tengah dirubah

oleh transaksi lain namun belum di commit

2. Tidak ada transaksi yang dapat merubah data yang tengah dibaca

oleh transaksi lain, sampai transaksi lain tersebut selesai dilakukan

12

3. Transaksi lain tidak dapat menambahkan baris baru (insert) dengan

nilai key yang dapat dibaca oleh statement pada transaksi yang

sedang berlangsung sampai transaksi tersebut selesai

Range lock dilakukan pada range nilai key yang cocok dengan search

condition pada setiap statement yang tengah dieksekusi pada transaksi ini.

Range hold berlangsung sampai transaksi tersebut selesai. Level ini dapat

mengatasi keempat masalah diatas. Namun concurency dari level ini rendah,

oleh karena itu biasanya level ini digunakan hanya pada saat tertentu saja.

Untuk contoh penggunaan isolation level dapat dilakukan dengan sintaks

Level sendiri terdiri atas :

a. Read uncommitted

b. Read committed

c. Repeatable read

d. Snapshot

e. Serializable

Contoh pemakaiannya, misal pada Repeatable read

13

Hanya satu isolation level yang dapat bekerja pada waktu yang sama. Namun

ada pengecualian bahwa kita dapat merubah isolation level dari satu level ke level

lain selama transaksi. Pengecualian ini berlaku jika kita memakai isolation level

lain, kemudian kita akan berlaih ke snapshot level. Begitu juga sebaliknya, jika

kita memulainya dengan snapshot isolation level kita dapat merubahnya ke level

lain. Jika kita melakukan hal ini akan menyebabkan roll-back. Jika kita

melakukan perubahan level, selama proses transaksi yang sama, maka data yang

tengah dibaca akan di lindungi sesuai dengan sifat level yang baru. Sebagai

contoh jika kita merubah level dari read committed ke serializable, maka shared

lock setelah perubahan akan tetap ada sampai akhir transaksi.

II.4 Implementasi Durability Pada SQL Server

Durability dalam SQL Server mengacu pada permanensi pada kegagalan

system. Sekali sebuah transaksi di commit, maka dia tetap dalam status telah

dicommit. Transaksi lainnya yang tidak memodifikasi data dari transakasi pertama

seharusnya tidak mempengaruhi data dari transaksi pertama. SQL server menjaga

durability dengan penulisan ke transaction log.

Durability inilah yang kemudian menjadi dasar database recovery. SQL

server mengimplementasikan durability dari transaksi dengan write-ahead

transaction log. Setiap transaksi ditulis ke transaction log prior untuk selanjutnya

ditulis ke dalam file.

Model – model recovery yang terdapat dalam SQL server antara lain :

a. Simple recovery model

Simple recovery model cocok untuk database yang membutuhkan

setiap transaksi harus atomic tetapi tidak perlu durable. Simple

recovery model mengijinkan SQL server untuk memotong atau

mengosongkan transaction log. Transaction log akan menjaga sebuah

transaksi sampai transaksi tersebut ditulis ke dalam file, tetapi setelah

itu, transaction log dapat digunakan oleh transaksi lainnya.

14

b. Full Recovery model

Full recovery model merupakan model recovery yang paling

handal. Semua jenis transaksi secara penuh dilog ke dalam log

transaksi. Bahkan fungsi – fungsi system seperti pembuatan index juga

di log. Keuntungan utama dari model ini adalah setiap transaksi yang

dicommit ke dalam database dapat diperbaiki pada titik dimana

kerusakan terjadi.

c. Bulk logged recovery model

Bulked recovery model mirip dengan full recovery model kecuali

beberapa operasi yang meminimalkan logged :

- Operasi Import Bulk (BCP,BULK INSERT, dan INSERT. .

. SELECT * FROM OPENROWSET(BULK . . .))

- Operasi SELECT INTO

- Operasi WRITETEXT dan UPDATETEXT BLOB

- CREATE INDEX

- ALTER INDEX REBUILD atau DBCC DBREINDEX

- DROP INDEX

Karena model recovery ini meminimalkan operasi log tersebut ,

maka model recovery ini dapat berjalan sangat cepat. Model ini

berguna hanya ketika database melihat sejumlah operasi bulk-logged

yang besar dan jika model ini dipandang penting untuk meningkatkan

performasi.

15

BAB III

KESIMPULAN

III.1 Kesimpulan

SQL server merupakan salah satu DBMS yang aman dan tangguh. SQL

server mampu mengakomodasi kebutuhan transaksi akan ACID (atomicity,

concurrency, isolation, durability) melalui berbagai mekanisme sintaks yang

terdapat di dalamnya. Salah satu buktinya adalah saat mengendalikan konsistensi

dengan metode optimistik dengan locking dan row-versioning.

16