مشکل Concurrency - تئوری و راه حل در SQL Server

این مطلب میتونه برای برنامه نویس‌ها و ادمین های پایگاه داده که دوست دارن در مورد مشکل همزمانی در Isolation level های مختلف دیتابیس SQL Server بدونن، مناسب باشه.

مفاهیم :

در هر دیتابیس رابطه ای، مفهوم transaction یا تراکنش وجود داره. تراکنش به مجموعه ای از چند عملیات منطقی گفته می‌شه که در یک user session به عنوان یه کار واحد انجام می شن.

اجازه بدین ویژگی های یک transaction رو بررسی کنیم:

ویژگی Atomicity

یه transaction باید اتمیک باشه یعنی نمیشه بخشی از عملیات توش انجام بشه و بخشی نشه، یا تمام عملیات باید کامل انجام شده باشه یا هیچ کدوم انجام نشه.

ویژگی Consistency

یه transaction باید consistent باشه، یعنی هر دیتایی که توسط دیتابیس سیستم نوشته میشه باید بر اساس قوانین تعریف شده در دیتابیس معتبر باشه، قوانینی مثل primary key , foreign key

ویژگی durability

وقتی از Consistency مطمعن شدیم، تراکنش به طور دائم در دیسک ذخیره میشه و این ویژگی durability رو برای تراکنش ایجاد میکنه.

به مجموع این 3 ویژگی به همراه ویژگی isolation ، خاصیت ACID گفته میشه که لازمه تمامی دیتابیس های رابطه ای هست.

تراکنش ها میتونن به طور همزمان در دیتابیس اجرا بشن، یعنی می‌تونیم تراکنش هایی رو پیدا کنیم که در حال خوندن یا نوشتن روی یک data object هستند(row,table,index). این موضوع مجموعه ای از مشکلات رو برای سطوح isolation ایجاد میکنه.

تنظیم Isolation level یک تراکنش مشخص می‌کنه که چگونه و چه زمانی دیتابیس تغییرات ایجاد شده توسط هر تراکنس رو در اختیار دیگر کاربران قرار بده. پس برای انتخاب transaction isolation level مناسب ، داشتن درک درست از مشکلات معمول concurrency برامون ضروریه.

مشکل Concurrency

قبل از ورود به جزئیات transaction level ، مهمه که با انواع معمول Concurrency و اصطلاحاتش آشنا بشیم .

نوع اول : Lost update and dirty write

این حالت وقتی پیش میاد که دو تراکنش به یک رکورد دسترسی پیدا می‌کنن و هر دو اونو آپدیت میکنن. تصویر پایین خلاصه این وضعیت رو نشون میده.

تو این مثال ما 2 تراکنش داریم که همزمان به رکورد با مقدار 60 دسترسی پیدا می‌کنن و مقدار اونو تغییر میدن . تراکنش اول این رکورد رو میخونه ، پروسس رو انجام میده و در نهایت تراکنش رو commit میکنه.

تراکنش دوم رکورد رو میخونه و بعد از آپدیت commit میشه. هر تراکنش رکورد رو به مقدار متفاوتی بروزرسانی میکنه و منجر به از دست رفتن بروز رسانی یکی از تراکنش ها میشه.

مفهوم Dirty read

زمانی که تراکنش به دیتایی دست پیدا میکنه که قبلا توسط تراکنش دیگه ای ویرایش شده اما این تغییر هنوز نه commit شده و نه roll back در این صورت Dirty read اتفاق میوفته.

شکل زیر مفهوم dirty read رو نشون میده. در این مثال رکورد دو ستون داره با مقدار اولیه 60,40 .فرض کنید ما شرطی داریم که میگه مجموع این دو ستون باید همیشه 100 باشه.

تراکنش دوم دیتایی رو خونده که مجموع دو مقدار در اون 100 نیست، به این دلیل که بهش اجازه داده شد وسط تراکنش مربوط به session 1 دیتا commit نشده رو بخونه. در واقع dirty read انجام داد.

نوع دوم : Non-repeatable read or fuzzy read

این وضعیت وقتی اتفاق میوفته که توی یک تراکنش یک کوئری دو نتیجه مختلف برگردونه ، طبق شکل زیر session 2 توی یک ترکنش دوبار select کرده اما هربار نتیجه متفاوتی برگردونده شده .

این بار هم به Session 2 اجازه داده شد وسط تراکنش 1 ، از همون دیتا بخونه!

نوع سوم : Phantom reads

شبیه Non-repeatable read در این مورد رخ میده ، به مثال زیر توجه کنین

فرض کنید تراکش 1 رو داریم که شامل دو select از جدول T هست یکی وقتی تراکنش شروع میشه و یکی هم درست پیش از اتمام تراکنش. تراکنش 2 رو بعد از اولین select تراکنش 1 شروع میشه و رکورد جدیدی به جدول T اضافه میکنه و قبل از select دوم Commit میکنه .باز هم نتیجه دو select اجرا شده در یک تراکنش متفاوت شد.

نوع چهارم : Locking reads

این درواقع مشکل Concurrency نیست و بیشتر مشکل design pattern هست. به طور خلاصه، قرار هست یک رکورد رو بخونیم و با مقداری که تو همون تراکنش به دست اومده بروزرسانی کنیم. با تضمین اینکه session دیگه ای نمی‌تونه مقدار دیتایی رو read شده رو تغییر بده .

پیاده سازی در SQL Server

اگه تغییری تو transaction isolation level سطح SQL Server داده نشده به طور پیش فرض به صورت READ COMMITTED هست.

حالت اول : Lost update

فرض کنین یک سیستم بانکی داریم و حسابی برای شما با موجودی 1500 افتتاح شده.

CREATE TABLE BankAccounts
( AccountId INT IDENTITY(1,1), 
BalanceAmount INT);

insert into BankAccounts (BalanceAmount)
SELECT 1500;

مدیرتون تصمیم میگیره حقوق 1600 دلاری شما رو پرداخت کنه.

-- Session 1: Employer
DECLARE @CustomerBalance	INT ;
DECLARE @BalanceDifference	INT ;
SET @BalanceDifference = 1600 ;

 BEGIN TRANSACTION ;
-- Getting back current balance value
SELECT @CustomerBalance = BalanceAmount
FROM BankAccounts
WHERE AccountId = 1 ;
PRINT 'Read Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance);

-- adding salary amount
SET @CustomerBalance = @CustomerBalance + @BalanceDifference ;

-- Slowing down transaction to let tester the time
-- to run query for other session
PRINT 'New Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance);

WAITFOR DELAY '00:00:10.000';
-- updating in table
UPDATE BankAccounts
SET BalanceAmount = @CustomerBalance 
WHERE AccountId = 1 ;

-- display results for user
SELECT BalanceAmount as BalanceAmountSession1
FROM BankAccounts
WHERE AccountId = 1 
COMMIT ;

همزمان دوستتون بدهی 40 دلاری رو بهتون برمی‌گردونه.

-- Session 2: Web reseller
DECLARE @CustomerBalance INT ;
DECLARE @BalanceDifference INT ;
SET @BalanceDifference = 40 ;
BEGIN TRANSACTION ;
-- Getting back current balance value
SELECT @CustomerBalance = BalanceAmount
FROM BankAccounts
WHERE AccountId = 1 ;
PRINT 'Read Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance);

-- adding salary amount
SET @CustomerBalance = @CustomerBalance + @BalanceDifference ;
PRINT 'New Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance);
-- updating in table
UPDATE BankAccounts
SET BalanceAmount = @CustomerBalance
WHERE AccountId = 1 ;
-- display results for user
SELECT BalanceAmount as BalanceAmountSession2
FROM BankAccounts
WHERE AccountId = 1 ;
COMMIT ;

خب نتیجه دو تراکنش انجام شده این خواهد بود :

متاسفانه بدهی پرداختی دوستتون رو از دست دادین !

حالت دوم : Dirty read

برای تشریح Dirty read ما دیتایی رو تو جدول Person.Person بروزرسانی می‌کنیم.

SELECT COUNT(DISTINCT LastName) DistinctLastNameBeforeBeginTran
FROM Person.Person
WHERE FirstName = 'Aaron';

BEGIN TRANSACTION;
UPDATE Person.Person
SET LastName = 'Hotchner'
WHERE FirstName = 'Aaron'

SELECT  COUNT(DISTINCT LastName) DistinctLastNameInTransaction
FROM Person.Person
WHERE FirstName = 'Aaron';

WAITFOR DELAY '00:00:10.000';
ROLLBACK TRANSACTION;

SELECT    COUNT(DISTINCT LastName) DistinctLastNameAfterRollback
FROM Person.Person
WHERE FirstName = 'Aaron';

تو مدت wait که تو کوئری وجود داره میتونیم تعداد Aaron ها رو بشمریم :

SELECT   COUNT(DISTINCT LastName) SecondSessionResults
FROM Person.Person
WHERE FirstName = 'Aaron';

با توجه به اینکه تنظیمات روی default isolation level هست ، Session دوم منتظر میمونه تا اولی تموم بشه.

خب پس میتونیم بگیم SQL Server به طور پیش فرض ما رو از dirty read محافظت میکنه. اجازه بدین isolation level به READ UNCOMMITTE تغییر بدیم و ببینیم آیا SQL دیتا dirty رو به Session دوم میده یانه ؟

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

و حالا کوئری Session 2 رو اجرا می‌کنیم.

نتیجه 1 برگردونده شد و wait هم نداشت. پس session 2 تونست وسط تراکنش اول دیتا رو بخونه.

حالت سوم : Non-repeatable reads

اگه توضیحات non-repeatable reads یادتون باشه اشاره می کرد به اینکه دو کوئری read تو یک تراکنش با توجه به زمان اجرا میتونه نتایج متفاوتی برگردونه.

از دو session استفاده می‌کنیم، session اول کوئری select هست که 5 رکورد اول از جدول Person. Person برمی‌گردونه، یه زمانی رو صبر میکنه و مجددا همون کوئری رو اجرا میکنه.

کوئری session اول :

-- ensure we use SQL Server default isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

-- Query 1 - first run
SELECT TOP 5
FirstName,
MiddleName,
LastName,
Suffix
FROM Person.Person
ORDER BY LastName

-- let some time for session 2
WAITFOR DELAY '00:00:10.000';

-- Query 1 - second run
SELECT TOP 5
FirstName,MiddleName,LastName,Suffix
FROM Person.Person
ORDER BY LastName

COMMIT TRANSACTION;

زمانی که session اول waiting هست ، session دوم رو اجرا میکنیم تا FirstName و LastNmae رو آپدیت کنه.

-- ensure we use SQL Server default isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
UPDATE Person.Person
SET  Suffix = 'Clothes'
WHERE LastName = 'Abercrombie'
AND FirstName = 'Kim';
COMMIT TRANSACTION;

نتیجه بدست آمده از Session 1 :

می‌تونیم تغییر رو به شکل زیر انجام بدیم:

UPDATE Person.Person
SET  Suffix = NULL
WHERE  LastName = 'Abercrombie'  AND FirstName = 'Kim';

اگه مایلیم از رخ داد همچین چیزی جلوگیری کنیم باید در مورد REPEATABLE READ isolation level بدونیم،پس بهتره تستش کنیم. برای session اول فقط لازمه وضعیت رو از

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

به

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

تغییر بدیم .

درواقع با تغییر ISOLATION LEVEL دیتابیس 2 session رو وادار کردیم تا منتظر تموم شدن session 1 بمونه. نتیجه بدست اومده :

در آخر تغییر انجام شده رو به حالت قبلی برمی‌گردونیم تا تست آخر رو انجام بدیم.

حالت چهارم Phantom reads :

برای این تست یک جدول dbo.Employee درست می‌کنیم و خالی نگهش میداریم.

IF (OBJECT_ID('dbo.Employee') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[Employee];
END;

CREATE TABLE [dbo].[Employee] (
EmpId       int IDENTITY(1,1) NOT NULL,
EmpName     nvarchar(32)      NOT NULL,
CONSTRAINT pk_EmpId 
PRIMARY KEY CLUSTERED (EmpId)
);

برای Session 1 یه تراکنش شامل دو کوئری select روی این جدول با فاصله زمانی 10 ثانیه اجرا می‌کنیم.

-- ensure we use SQL Server default isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 

BEGIN TRANSACTION;
 -- Query 1 - first run
SELECT *
FROM dbo.Employee

-- let some time for session 2
WAITFOR DELAY '00:00:10.000';

 -- Query 1 - second run
SELECT *
FROM dbo.Employee
COMMIT TRANSACTION;

تو session 2، تراکنش درج رکورد اجرا می‌کنیم.

-- ensure we use SQL Server default isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
BEGIN TRANSACTION;

 INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('Oby');
INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('One');
INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('Ken');
INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('Tukee');

COMMIT TRANSACTION;

نتیجه به دست آمده از session اول :

با توجه به اینکه بین دو select تراکنش اول ، تراکنش دوم توی جدول insert انجام داد ، نتیجه بدست اومده تو یه تراکنش متفاوت شد.

خلاصه :

همانطور که دیدیم امکان بدست آوردن نتایج مختلف با کوئری یکسان در یک تراکنش با توجه به cuncurrency و isolation level وجود داره و همزمانی تراکنش‌ها میتونه مشکلاتی رو به وجود بیاره. تنظیم درست isolation level با توجه به سناریو تعریف شده می‌تونه از خرابی دیتا جلوگیری کنه.

منبع :

www.sqlshack.com