Sql Server'da TRANSACTION ve WITH (NOLOCK) kullanımı
Adı ne olursa olsun tüm
RDBMS (relational database management system) veritabanları locking kavramını içinde barındırır. Veritabanında bir kayıt üzerinde işlem yapılırken veritabanı motoru
o kaydı kilitler ve işlem tamamlanana kadar değişiklik yapılmasına izin vermez. Dolayısıyla aynı kayda erişmeye veya güncellemeye çalışan diğer kullanıcılar kayıt serbest bırakılana kadar beklemek zorundadır. Bu handikap küçük ve nispeten orta ölçekli projelerde çok fazla hissedilmezken, anlık milyonlarca işlemin yapıldığı
transaction kullanılan büyük projelerde ciddi olarak hissedilmektedir.
WITH (NOLOCK) terimi bir Microsoft Sql Server kilitleme ipucudur.
Veritabanımızda Stock adında bir tablomuzun olduğunu varsayalım.
Bu tablo, üzerinde yoğunluklu işlem yapılan bir tablo olsun.
SELECT StockID, StockItem, StockStatus FROM dbo.Stock WITH (NOLOCK)
ifadesi Stock tablosunda
kilit oluşturmadan kayıt getirmeye yarar. Böylece bu tabloya başka kullanıcılar tarafından eş zamanlı yapılan insert, delete, update işlemleri select sorgusunun yarattığı kilit beklenmeksizin gerçekleştirilir. Bu bir avantaj olmakla birlikte bu yöntemin ciddi dezavantajları da bulunmaktadır. Yanlış locking uygulamaları "
READ UNCOMMITTED (Dirty Read)" oluşmasına neden olabilmektedir.
Bu durumu basit bir örnekle açıklamak gerekirse; FB-GS maçına sadece 1 biletin kaldığını varsayalım.
Ahmet, Mehmet, Anıl, Kemal, Selim kullanıcıları firmanın web sitesi veya yazılımı üzerinden
aynı anda sipariş verdiler. Ahmet ile Mehmet'in işlemi
aynı atomik zamanda gerçekleşti. Ahmet'in işlemi gerçekleşti ve kayıt sayısı sıfıra düşürüldü. Ahmet'in işlemi WITH (NOLOCK) ile gerçekleştirildiği için Mehmet de aynı anda kayıda erişebildi ve bir update işlemi yapmaya çalıştı. Bu durumda uygulama da mantık hatası oluştu ve sistem hata verdi. Bu örnekte hataya WITH (NOLOCK) teriminin yanlış kullanımı neden olmuştur fakat aynı örnekte (WITH NOLOCK) kullanılmaması da oluşacak hatayı engellemez. Hatayı ortadan kaldıracak
tek çözüm TRANSACTION kullanmaktır.
Örnekte gerçekleşmesi gereken işlemler ve stored procedure kodu şöyle olmalıdır:
1- Sistemde istenilen bilet sayısı kadar bilet var mı?
2- Bilet varsa
Transaction başlat.3- Ödemeyi al, bilet kotasını azalt.
4- Ödeme
başarılıysa transaction'ı
tamamla,
aksi halde kotayı
eski durumuna getir.
5- Bilet yoksa işlemi reddet.
Yazdıklarımızı koda dökecek olursak :
ALTER PROCEDURE [dbo].[TicketSales]
(
@TicketNumber bigint, -- talep edilen bilet
@GameID bigint, -- maç id
@UserID bigint, -- kullanıcı id
@CompanyID bigint -- satıcı firma id
)
AS
BEGIN
DECLARE @result tinyint
DECLARE @TicketQuota int
DECLARE @TicketFee money
SET @TicketQuota = 0 -- varsayılan kota sıfırdır.
SELECT @TicketQuota = TicketQuota, @TicketFee = TicketFee FROM Tickets WHERE GameID=@GameID
-- yeterli kota varsa
IF @TicketNumber>=@TicketQuota
BEGIN
BEGIN TRANSACTION
-- Ödemeyi al. Kullanıcı hesabından eksilt.
UPDATE Accounts SET AccountAmount = AccountAmount - (@TicketFee * @TicketNumber) WHERE AccountHolderId = @UserID
-- Firma hesabına aktar.
UPDATE Accounts SET AccountAmount = AccountAmount + (@TicketFee * @TicketNumber) WHERE AccountHolderId = @CompanyID
-- kotayı azalt
UPDATE Tickets SET TicketQuota = TicketQuota - @TicketNumber WHERE GameID=@GameID
IF @@ERROR=0
BEGIN
-- tüm işlemleri onayla.
COMMIT TRANSACTION
SET @result=0 -- işlem tamam. İyi seyirler. :)
END
ELSE
BEGIN
-- tüm işlemleri geri al. Tüm değişiklikler geri alınır.
ROLLBACK TRANSACTION
SET @result=1 -- işlem başarısız. ödeme başarısız, sistem hatası vs... :(
END
END
ELSE
BEGIN
SET @result=2 -- yeterli bilet yok. :(
END
RETURN @result
END
Yukarıdaki prosedürün oldukça anlaşılır olduğunu düşündüğüm için detaylarını tek tek açıklamıyorum. İçinde transaction'ın nasıl başlatıldığını, hangi şartlarda işlemlerin onaylandığını ve hangilerinde geri alındığını incelemenizi öneriyorum.
Transaction konusu çok ayrıntılı olarak incelenmesi gereken bir konu olduğu için başka bir yazıda detaylarından bahsedeğim. Biz burada veritabanının yerleşik olarak sunduğu transaction yapısından faydalandık. Kurumsal projelerde genelde sadece bu iş için ayrılmış bir Transaction sunucusu bulunmaktadır. Diğer yandan
ADO.NET de içinde barındırdığı tümleşik transaction desteği ile bize uygulama tarafından transaction oluşturma ve yönetme şansı verir.
Ben transaction'ların veritabanı motoru tarafından oluşturulmasını tercih edenlerdenim. Seçim size kalmış.
"Bug Free" kalın.
MS Sql Server saklı yordamlarına (stored procedure) çoklu parametre geçmek
Bu yazıda Microsoft Sql Server ™ üzerinde yazılmış prosedürlere (stored procedure) çoklu parametre geçmenin yollarından birini inceleyeceğiz. T-Sql diline ve temel programcılık bilgisine hakim değilseniz bu yazı sizin için karmaşık olabilir.
Bildiğimiz gibi Microsoft Sql Server en yeni sürümü olan 2005 dahil olmak üzere bütünleşik bir dizi (array) desteği getirmemektedir. Bu nedenle veriyle iletişimizi sağlayan prosedürlerimizde dizi veya benzeri bir veri tipini kullanmamız mümkün değil. Böyle olmasaydı uygulamamız içinde yaratacağımız diziyi prosedürümüze geçer ve bu yazıyı okumak yerine arkamıza yaslanır tv izlerdik. Bu yazıda bu zorluğu aşmak için xml metodunun nasıl kullanılacağını göstereceğim.
Vaktiyle üzerinde çalıştığım bir emlak portalı projesinde, kullanıcılara seçtikleri ilanları kıyaslamalarını sağlayabilecek bir sistem geliştiriyordum. Kullanıcı sınırsız sayıda ilan seçebilecek ve seçtiği ilanların tüm detayları veritabanından alınarak birebir kıyaslanacaktı. Bu durumda daha önce yazmış olduğum ve sadece bir ilana ait üm detayları getiren prosedür yeterli olmayacaktı.
Elimdeki mevcut prosedür şöyleydi :
CREATE PROCEDURE [dbo].[SelectAdvertisement]
(@AdvertisementIndex bigint,
.... )
AS ....
Bu prosedürü baz alarak şu şekilde yeni bir tane yazdım :CREATE PROCEDURE [dbo].[SelectAdvertisementMultiple]
(
@AdvertisementIndexListXml varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DocXml int
EXEC sp_xml_preparedocument @DocXml OUTPUT, @AdvertisementIndexListXml
SELECT * FROM dbo.Advertisements AS a WITH (NOLOCK) INNER JOIN
OPENXML (@DocXml, '/advertisementlist/adv', 1) WITH (AdvertisementIndex int) AS b
ON a.AdvertisementIndex = b.AdvertisementIndex
WHERE a.Active=1
EXEC sp_xml_removedocument @DocXml
END
Prosedürün çalıştırılması ise şöyle olmalı :
EXEC dbo.[SelectAdvertisementMultiple]
'<advertisementlist>
<adv advertisementindex="24343" />
<adv advertisementindex="536332" />
<adv advertisementindex="72224" />
</advertisementlist>
'
Gördüğünüz gibi geçeceğimiz parametreleri bir xml metin haline getirip prosedüre geçiyoruz.
Prosedür aldığı xml metni OPENXML ile parse ederek (parse : işlemek, ayrıştırmak) AdvertisementIndex'leri ayrıştırmakta ve ayrıştırılan AdvertisementIndex'ler inner join ile ilgili tabloyla eşlenmektedir.
OPENXML ile ilgili ayrıntılar için
http://technet.microsoft.com/en-us/library/ms186918.aspx
adresini ziyaret edebilirsiniz.Uygulamanız içerisinden Xml oluşturmak için .NET classlarını kullanabilirsiniz. System.Xml altında bulunan classları başka bir yazıda ayrıntılı olarak anlatacağım.
Yine de konuyu tamamlamak açısından uygulama tarafında basit bir örnek vermek istiyorum :
using System.Xml ; // Xml sınıflarını (class) barındıran namespace (uzay)
............
private long[] AdvertisementIndexList = new long[5];
// kullanıcıdan gelen ilan idleri al, işle ve AdvertisementIndexList dizisine al.
// bu yazıyı okuduğunuza göre bu kısmı kodlamak sizin için çocuk işi :).
.............
XmlDocument xmldoc = new XmlDocument();
XmlNode advertisementListNode = xmldoc.CreateElement("AdvertisementList");
xmldoc.AppendChild(advertisementListNode);
for (int i = 0; i < this.AdvertisementIndexList.Length; i++)
{
XmlNode advNode = xmldoc.CreateElement("Adv");
XmlAttribute advNodeAttribute = xmldoc.CreateAttribute("AdvertisementIndex");
advNodeAttribute.Value = this.AdvertisementIndexList[i].ToString();
advNode.Attributes.Append(advNodeAttribute);
advertisementListNode.AppendChild(advNode);
}
string xmlString = xmldoc.InnerXml;
.....
// bağlantı (connection), komut (command) objelerini oluştur..
// xml komut objesine ekle.
cmd.Parameters.Add("@AdvertisementIndexListXml", SqlDbType.VarChar,1000).Value = xmlString;
.......
// örnekte data reader kullanıyorum ama DataSet de kullanabilirsiniz.
SqlDataReader reader = cmd.ExecuteReader();
Gördüğünüz gibi esasında xml yöntemi ile prosedürlere "dizi" geçmek oldukça kolay. Farklı yöntemler de mevcut olmasına rağmen , kişisel olarak bu yöntemi kullanıyorum.
Diğer yöntemlere gelince :
1- Dinamik sql kullanmak. (Yanlış kullanım sql injection davetiyesi çıkarır.)
2- Diziyi CSV formatına getirip prosedüre geçmek ve "123,456,789,743,244,4223" şeklindeki veriyi işlemek.
3-Yukarıdaki işleme işini yapan ve tablo tipi veri döndüren bir UDF (user defined function : kullanıcı tanımlı fonksiyon) yazmak. Prosedürümüzü table veri tipi alır hale getirmek ve UDF'in çıktısını prosedüre vermek.
Xml'i tercih ettiğimi tekrarlıyorum.