Oracle Veritabanında Transaction Yönetimi Giriş

Oracle Veritabanında
İşlem(Transaction) Yönetimine
Giriş
Oracle Veritabanında Eş Zamanlılık(Concurrency), Tutarlılık(Consistency),
Kilitler, COMMIT ve ROLLBACK Süreçleri
CETURK Oracle Day etkinliği
Bahçesehir Üniversitesi
04/11/2010
H.Tonguç Yılmaz [email protected]
http://friendfeed.com/TongucY
http://tonguc.wordpress.com/about/
 H.Tonguç Yılmaz: Oracle veritabanı uzmanı - iyi bir
Fenerbahçe taraftarı - Metal müzik sever - Bol bol
DVD izler vs.
 Fethiye Lisesi, İTÜ Bilgisayar Müh., Bilgi MBA
 C Developer, Informix DBA-Hp Unix Admin, Oracle DBA,
Oracle ETL Developer, DW Development Team Leader
 1996 Tekstilbank, 2000 Turkcell, 2008 Turkcell Teknoloji
 10+ sene Oracle veritabanı tecrübesi, Oracle 8i,9i,10g
OCP, 2007 Oracle ACE, 2005 Oracle blogger
 2002 Turkcell Akademi Oracle veritabanı iç eğitmeni, 2002
TTech Paf koçu
 1999 OracleTurk moderatör, 2010 TROUG kurucu üye
Öncelikle, hangi tarafta olacağına karar vermelisin
Akıl’ın hislere/tahminlere/önyargılara
karşı mücadelesi..
Çalışma hayatında bu tarz
cümlelere dikkat :
• Bence ..
• İddia ederim ..
• Düşünüyorum ..
• Hissediyorum ..
• Tahmin ediyorum ..
- Çalışmalarını sayılarla ifade
edebileceğin sonuçlara dayandır,
tekrarlanabilir denemeler hazırla.
- Herşey bu dünyada ispat
edilebilir ve zarar görmemek için
de edilmelidir, çalıştığın kişileri bu
anlamda zorla.
Neden bu konu
‘ Transactions: Transactions are a fundamental feature of all databases – they are
part of what distinguishes a database from a file system. And yet, they are often
misunderstood and many developers do not even know that they are accidentally
not using them. ‘
‘ Locking & Concurrency: Different databases have different ways of doing things
(what works well in SQL Server may not work as well in Oracle) and understanding
how Oracle implements locking and concurrency control is absolutely vital to the
success of your application. ‘
Expert Oracle Chapter 4 & 5, Thomas Kyte - http://asktom.oracle.com
Balığı kendin tutabilirsin – Ücretsiz Dökümanlar
Balığı kendin tutabilirsin – Ücretsiz Ortamlar
• Oracle Database 10g Express Edition(Oracle XE) is a great starter database for
Developers working on PHP, Java, .NET, XML, and open source applications.
• Free to develop, deploy, and distribute
• Fast to download, simple to install and simple to administer.
• Entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base
http://oss.oracle.com/
• Oracle SQL Developer is a free graphical tool for database development.
• you can browse database objects,
• run SQL statements and SQL scripts,
• edit and debug PL/SQL statements.
http://sqldeveloper.oracle.com/
• Yeni başlayan Oracle Developer veya DBA için çok daha fazlası OTN başlangıç
portalinde:
http://www.oracle.com/technology/getting-started/index.html
Oracle kısa tarihçe
1978 Oracle V1; pazardaki ilk *satış* amaçlı ilişkisel SQL veritabanı
yönetim sistemi
….
1980 Oracle V3; Transactions
1984 Oracle V4; Read Consistency
1986 Oracle V5; Distributed Queries
1989 Oracle V6; Row Level Locking
…
7.3
1996
8.0
1997 Oracle 8
8.1.5
1999 Oracle 8i Release 1
8.1.6
1999 Oracle 8i Release 2
8.1.7
2000 Oracle 8i Release 3
9.1
2001 Oracle 9i Release 1
9.2
2002 Oracle 9i Release 2
10.1
2004 Oracle 10g Release 1
10.2
2005 Oracle 10g Release 2 ( XE ! )
11.1
2007 Oracle 11g Release 1
11.2
2009 Oracle 11g Release 2 ( XE ? )
Veritabanı İşlemleri(Transactions)
• Veritabanı işlemleri, bir bağlantıdan işletilen bir küme birbiri ile anlam kazanan veri işletme dili(Data Manuplation
Language-DML) cümlesidir.
• Alttaki SQL cümlelerinden biri ile başlar:
•
•
•
•
•
•
INSERT
UPDATE
DELETE
MERGE
SELECT FOR UPDATE
LOCK TABLE
• COMMIT veya ROLLBACK tamamlanır.
8
Veritabanı İşlemleri(Transactions) – Bitirme Yöntemleri
• COMMIT veya ROLLBACK
• Bağlantı kapama
• Normal bir şekilde kapatıldı ise kullanılan istemci ayarlarına
göre hareket edilir
• Anormal bir şekilde kapatıldı ise ROLLBACK işlemi
gerçekleştirilir
• Veri tanımlama dili(Data Definition Language-DDL:
CREATE, ALTER, DROP, TRUNCATE, RENAME
gibi) cümleleri kullanıldığında
• DML’leri takip eden DDL cümleleri veritabanı işlem
mantığınızı kırar, çünkü DDL çalıştırma aşamasının ilk adımı
örtülü bir COMMIT’dir. *
* Notlar kısmında DDL için sözde kod(pseudo) ve örneğe göz atınız.
9
Veritabanı İşlemleri(Transactions) – Basit Örnek
SQL> UPDATE hr.employees SET salary=salary;
107 rows updated.
SQL> SELECT XID, STATUS FROM V$TRANSACTION;
XID
STATUS
---------------- ---------------0800090033000000 ACTIVE
SQL> UPDATE hr.employees SET last_name=last_name;
107 rows updated.
SQL> SELECT XID, STATUS FROM V$TRANSACTION;
SQL> ROLLBACK;
Rollback complete.
XID
STATUS
---------------- ---------------0900050033000000 ACTIVE
SQL> SELECT XID FROM V$TRANSACTION;
no rows selected
10
ACID Özellikler
• Atomicity: Bir veritabanı işlemi bir bütün olarak ya
gerçekleşir ya da gerçekleşmez (A transaction either happens
completely, or none of it happens) *
• Consistency: Bir veritabanı işlemi veritabanını tutarlı bir noktadan diğer
bir tutarlı noktaya taşır. (A transaction takes the database from one
consistent state to the next)
• Isolation: Bir veritabanı işlemi COMMIT ile sonuçlanana kadar yaptıkları
diğer oturumlarda görünür değildir. (The effects of a transaction may
not be visible to other transactions until the transaction has committed)
• Durability: Başarı ile COMMIT edilmiş bilgisi alınan bir veritabanı işlemi
kalıcıdır, kaybedilemez. (Once the transaction is committed, it is
permanent)
* Notlar kısmında Oracle veritabanında Atomicity örneklerine göz atın.
Veritabanı İşlemleri(Transactions) Denetim Cümleleri
COMMIT: Veritabanı işlemini sonlandırır, değişiklikleri kalıcı kılar.
ROLLBACK: Veritabanı işlemini sonlandırır, değişiklikleri geri sarar.
SAVEPOINT: Bir veritabanı işlemi içinde işaretli noktalar yaratmak için
kullanılır, birden fazla işaret noktası yaratılabilinir
ROLLBACK TO <SAVEPOINT>: Belirtilen işaretli noktaya geri sarma için
kullanılır.
SET TRANSACTION: Birçok veritabanı işlem özelliğinin(sadece okuma gibi)
değiştirilmesi için kullanılabilir.
•
•
•
•
•
•
Elle geri sarma yönetimi(Manual Rollback Segment Management) kullanılan veritabanlarında
bir veritabanı işlemine belirli bir alanı kullanmaya zorlanmak için kullanılırdı.
Dağıtık(Distributed) Veritabanı İşlemleri(Transactions)
ve İki Aşamalı COMMIT(Two Phase Commit-2PC)
Onay
Uzaktaki sunucu 1
COMMIT;
?
?
İstemci
Uzaktaki sunucu 2
Onay
Ana Oracle
Veritabanı
Dağıtık işlem başarı ile tamamlandı.
Başarım(Performance) ve
Ölçeklenirlik(Scalability) Kavramları
“Performance is interested with avoiding unnecessary work
on limited system resources, where as Scalability is
interested with avoiding blocking others doing same
operations(serialization). These need different attentions,
especially second is very important for heavy loaded OLTP
systems. Sometimes you may sacrifice in one to gain in
other.” *
* Memory Management and Latching Improvements in Oracle9i and 10g Presentation, Tanel Põder
14
Eş zamanlılık(Concurrency) ve Kilit Kavramları
• Gerçek yaşamda tek kullanıcısı olan veritabanı uygulamaları
geliştirmiyoruz. Çok kullanıcılı sistemlerde aynı kaynaklara aynı
anda ulaşmını düzenleme önemli bir konudur.
• Eş zamanlılık, birçok kullanıcının veriye ulaşımının uyumudüzeni ile ilgilidir. Bu düzen Kilit kullanımı ile yönetilir, ama kilit
miktarı-seviyesi arttıkça veritabanı uygulamasının ölçeklenirliği
düşer, dolayısı ile düzen en az kilit ile korunmalıdır.
• Oracle veritabanı ile yüksek eş zamanlı veritabanı uygulamaları
geliştirebilirsiniz, siz koruma düzenini-kilitleri bilinçli arttırmadıkça Oracle
veritabanı özdevimli(automatic) olarak en düşük kilit koruma düzeyi ile
düzeni korur;
1- Okuyucular, aynı kaynaklar için diğer okuyucuları beklemez.
2- Yazıcılar da aynı kaynaklar için diğer okuyucuları beklemez.
3- Yazıcılar diğer yazıcıları sadece aynı *anda* aynı *satır*ları değiştirmek
istediklerinde beklerler.*
* Notlar kısmında özel durum örneklerine göz atın.
15
Kilit Kavramları Detaylar
• Yaratılan kilitler COMMIT veya ROLLBACK buyruklarından biri
veritabanı işlemi sonlanana dek canlı tutulur.
• DML kilitleri satırlar ve veriyi korurken, DDL kilitleri çizem(schema)
nesnelerini korur.
• Dışlayan(Exclusive) kilitler, ilgili kaynakların paylaşımı engelleyen kilit
tipidir. Paylaşılan(Shared) kilitler, ilgili kaynakların paylaşımına izin
veren kilit tipidir.
• Kilit dönüştürme(Conversion), daha alçak seviye koruma düzeyine
geçiş sorun yaratmaz iken daha yüksek koruma düzeyi talepleri için
bekleme yaşanabilir.
• Kilit artırımı(Escalation), Oracle veritananında kilit arttırımı yapılmaz
çünkü kilitler Oracle veritabanından kısıtlı bir kaynak değildir.
• Başka veritabanlarında durum; satır kilitleri artınca -> sayfa bazında kitle -> çizelgenin(table)
tamamını kitle
• Ölçeklenirlik için ölümcül bir davranış, çıkmaz kilit(Deadlock) oluşumuna yol açar
16
REDO ve UNDO kavramları
• DBA’in en önemli görevi kesinti oluşmasını engellemek, kesin oluşursa en
kısa sürede veritabanını çalışır hale geri getirmektir.
• REDO bilgisi geri dönüş(Recovery)için ciddi önem taşır. Veri kaybı
yaşanmaması gereken veritabanları için REDO dosyaları arşivlenmelidir.
• Her satır için o işlemin tekrar yapılabileceği bilgiler REDO, geri alınabileceği
bilgiler de UNDO olarak Oracle veritabanı tarafından yaratılır.
• UNDO içinde sadece tablo değil ilgili tüm nesnelere ait geri alma bilgileri
saklanır.
• UNDO üretimi de REDO üretilir ve korunur.
INSERT INTO t (x,y) VALUES (1,1);
UPDATE t SET x = x+1 WHERE x = 1;
DELETE FROM t WHERE x = 2;
1.DELETE sonrasında başarıyla COMMIT ettiğimiz durumu tartışalım.
2.DELETE sonrasında başarıyla ROLLBACK ettiğimiz durumu tartışalım.
3.UPDATE sonrasında veritabanı kontrolsüz(ABORT) kapanırsa ne olur
tartışalım.
17
REDO ve UNDO kavramları
Data Buffer
Cache
Redo Log
Buffer
Undo
18
Index
Table
Redo
ONLINE REDO ve ARCHIVED REDO
LGWR
3a
Y:/
1b
2b
3b
LGWR
ARCH
ARC
2a
ARC
1a
ARC
X:/
Z:/
19
LGWR
ONLINE REDO ve ARCHIVED REDO
LGWR
LGWR
Disk1
1a
3a
Disk2
1b
3b
Disk3
2a
Disk4
2b
4a
4b
LGWR
20
Disk5
ARCH
Disk6
ARCH
ARC
ARC
ARC
ARC
LGWR
COMMIT
• Oracle için, COMMIT hızlı bir işlemdir, çünkü o ana kadar
veritabanı gerekli ön hazırlığı iyimser bir şekilde yapmıştır.
• Bir veritabanı uygulama geliştiricisi COMMIT sürecini
anlamalıdır, ortalıkta birçok ‘efsane’ dolaşır - daha sık COMMIT
etmek, COMMIT süresine olumlu etkileMEZ.
# of rows
inserted
Time to INSERT
(secs)
Time to COMMIT
(secs)
9
.06
.00
1,512
99
.06
.00
11,908
999
.05
.00
115,924
9,999
.46
.00
1,103,524
99,999
16.36
.00
11,220,656
Redo Generated
(bytes)
ROLLBACK
• Oracle için, ROLLBACK ise daha yavaş ve pahallı bir işlemdir,
çünkü o ana kadar yapılan tüm hazırlığın cidden geri alınması
gerekir.
• Uygulamalarınızda ROLLBACK yazarken bir daha düşünün :
timeCOMMIT << timeROLLBACK
# of rows
inserted
Time to INSERT
(secs)
Time to ROLLBACK
(secs)
9
.06
.02
1,648
99
.04
.00
12,728
999
.04
.01
122,852
9,999
.94
.08
1,170,112
99,999
8.08
4.81
Redo Generated
(bytes)
11,842,168
Kötü Veritabanı İşlemi(Transaction) Alışkanlıkları
• Oracle için, bir veritabanı işlemi olması gerektiği kadar uzun
olabilir, işlem mantığına uygun olarak COMMIT edilmesi
gerektiği anda bitirilebilir.
BEGIN
FOR x IN ( SELECT rowid rid, object_name
FROM t4 )
LOOP
UPDATE t4
SET object_name = lower(x.object_name)
WHERE rowid = x.rid;
IF ( MOD(x.r, 100) = 0 ) THEN
COMMIT;
END IF;
Operation
END LOOP;
COMMIT;
INSERT 200 rows
END;
UPDATE 200 rows
/
DELETE 200 rows
UPDATE t4
SET object_name = LOWER(object_name);
COMMIT;
Row
Affected
Total Redo
(no COMMITs)
Total Redo
(with COMMITs)
%
increase
200
442,784
530,396
20%
200
849,600
956,660
13%
200
469,152
537,132
14%
Kötü Veritabanı İşlemi(Transaction) Alışkanlıkları
• Dikkat, Java Database Connectivity(JDBC) AUTOCOMMIT
özelliği ön değeri TRUE ayarlıdır. İşlemlerinizin denetimini ele
geçirmek için FALSE ayarlamalısınız.
pstmt.setLong(1,2345);
pstmt.setString(2,”Sam”);
pstmt.execute();
pstmt.setLong(1,2346);
pstmt.setString(2,”Steve”);
pstmt.execute();
pstmt.setLong(1,2347);
pstmt.setString(3,”Scott”);
pstmt.execute();
commit
commit
commit
connection.setAutoCommit(false);
pstmt.setLong(1,2345);
pstmt.setString(2,”Sam”);
pstmt.execute();
pstmt.setLong(1,2346);
pstmt.setString(2,”Steve”);
pstmt.execute();
pstmt.setLong(1,2347);
pstmt.setString(3,”Scott”);
pstmt.execute();
connection.commit();
Kötü Veritabanı İşlemi(Transaction) Alışkanlıkları
• Katmanlı yapıda kim işlemi yönetecek ve sonlandıracak?
• B içinde COMMIT veya ROLLBACK geçiyor ise, ya da A için
AUTOCOMMIT TRUE kaldı ise:
• Java Procedure A
• PL/SQL Procedure B
• PL/SQL Procedure C
Not: Özerk (Autonomous) veritabanı işlemleri ile PL/SQL blokları içindeki COMMIT veya ROLLBACK çağırımlarından dış blokların etkilenmemesi sağlanabilir. *
Ürettiğim REDO miktarını nasıl ölçebilirim
• Bir tabloya INSERT – UPDATE – DELETE işlemleri gönderek
COMMIT veya ROLLBACK çalıştırmadan REDO üretimini bu
basit görüntü yardımı ile ölçebiliriz.
CREATE OR REPLACE VIEW redo_size AS
SELECT value
FROM v$mystat ms, v$statname sn
WHERE ms.statistic# = sn.statistic# AND
sn.name = 'redo size';
REDO üretimini kapatabilir miyim
• Oracle veritabanında TEMPORARY TABLE seçeneğini
kullanarak REDO üretimini kısıtlayabilirsiniz, UNDO üretimi
ROLLBACK edebilmek için yine yaratılacaktır. *
• Oracle veritabanında sadece bazı özel işlemler
NOLOGGING seçeneği ile yapılabilinir. *
• Index creations and ALTERs (rebuilds)
• Bulk INSERTs using a ‘direct path insert’ via the /*+APPEND */ hint
• LOB operation (updates to large objects do not have to be logged
• Table creations via the CREATE TABLE AS SELECT
• Various ALTER TABLE operations such as MOVE and partition
SPLIT
• TRUNCATE (but it does not need a NOLOGGING clause, as it is
always in NOLOGGING mode)
* Notlar kısmında REDO üretim maliyetleri ve TEMPORARY TABLE örneklerine göz atın.
Oku, dene, sorgula, geliştir, *paylaş* ..
Kaynakça –
• Turkcell Staj Transactions-Redo/Undo sunumu, Ergin Erant, http://bhatipoglu.com
• Expert Oracle Chapter 4,5 Thomas Kyte, http://asktom.oracle.com
• Oracle® Database Concepts 11g Release 2 (11.2) @ http://tahiti.oracle.com
Chapter 10 Transactions
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/transact.htm#g11401
Chapter 9 Data Concurrency and Consistency
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/consist.htm#g43931
Çok daha fazlası için * –
• Transaction Internals, Julian Dyke http://www.juliandyke.com/Presentations/Presentations.html
• Oracle Concepts and Architecture Series @ tonguc.wordpress.com
Part 3: How Atomicity is implemented on Oracle
http://tonguc.wordpress.com/2007/01/13/oracle-concepts-and-architecture-part-3/
Part 4: Overview of Transaction Management Internals
http://tonguc.wordpress.com/2007/10/01/oracle-concepts-and-architecture-part-4/
Part 5: Concurrency versus Locking Concepts, Understanding Lock Contention with Examples
http://tonguc.wordpress.com/2007/10/04/oracle-concepts-and-architecture-part-5/
* Bu sunum giriş düzeyi için tasarlandı, ikinci bir ileri seviye sunumuna hazırlanacağım, Türk Oracle Kullanıcı Grubu – Veritabanı
Geliştirme Özel İlgi Grubu buluşmasında çok yakında 
Senelerdir bilgi paylaşımına fırsat yarattığı
için CETURK’e çok teşekkürler
?
?
?
H.Tonguç Yılmaz [email protected]
http://friendfeed.com/TongucY