Əsas səhifə > MySQL > Transactions and Locks

Transactions and Locks

Bu mövzuda MySQL-də transaction və lock anlyışlarından danışacıq. Xüsusən deadlock-un nə olduğunu və MySQL-də bunun qarşısının necə alınacağına baxacıq.
*Bu yazıda MySQL Stored Procedure-dan istifadə olunub.*
İlk öncə transaction nədir? Bu haqda bir yazlm var artıq MySQL Transaction
Qısaca olaraq :
A transaction is a set of one or more SQL statements that are logically grouped together and that must be either applied to the database in their entirety or not applied at all.
Indi isə biraz Lock-dan danışaq…Lock adından da göründüyü kimi bağlamaq,kilidləmək deməkdir. Transaction mövzusunda Lock-un yeri ondan ibarətdir ki, məsələn biz bir cədvəldən məlumat çıxardıqda digər bir user həmin an bizim cədvələ update verə bilər. Və əgər birinci select-imizdə bizim məlumat deyək ki, 2000 idisə ikinci select-də bu məlumat 1500 ola bilər. Yəni biz select verərkən kimsə gəlib update və yaxud insert verəcək və bizim select-imiz hər dəfə fərqli bir məlumat çıxaracaq…Bu həmçinin elə update-in özünə də aiddir. Deyək ki, biz update edirik və İD=200 edirik digər birisi də həmin an İD-ni 205 verir…Belələliklə bizim məlumatlar öz dəqiqliyini itirmiş olur. Aşağıdakı şəkilə diqqət yetirsək (bayaqdan gimp-də şəkilə arrow əlavə eliyənə qədər canım çıxıb :D)

Şəkil üzərində daha yaxşı izah etmək olacaq bunu…Bizim balansımız 2000-dir…2 user transaction başladır…Birinci user balansımızı 100$ azaldır…Eyni anda 2ci user balansımızı 300$ artırır…İndi database hansını qəbul etməlidir? Günah bizdədir ki, cədvəli lock etməmişik və 2 user eyni anda update etməklə məşğuldur.Öz aramızdı Çox pis-pis işlərnən məşğuldurlar…Burda dəhşətli məqam odur ki, database hansı user birinci commit edir onu qəbul edəcək…Yəni ki pulumuz ya 1900 ya da 2300 olacaq, o baxır hansı user tez tərpənəcək🙂 belə də şey olar??? uşaq oyuncağıdır bu???
Deməli lock bu imiş…2ci şəklimizə diqqət yetirək:

2ci şəklimizdə görürük ki,1ci user (transaction A) update edərkən 2ci user(transaction B) lock olunub və 2ci user yalnız 1ci transaction bitdikdən sonra(indiki halda commit) öz şəxsi yüksək sevincli update-ni etsin…Lock anlayışı bundan ibarətdir…

MySQL\İnnoDB “row-level lock”-la məşğuldur…Yəni ki bizim misalda 2ci user account_id=15 olan row-nu asanlıqla update edə bilər. Əsas odur ki 2 transaction eyni row-nu update edə bilmir…Bu da maraqlı bir xüsusiyyətdi…Həqiqətən də bir row-nu update etmək üçün bütün table-ı lock etməyin mənası yoxdu…
Biz istədiyimiz vaxt hər hansı bir table-ı lock edə bilərik əslində…Amma 99% hallarda storage engine(İnnoDB) bu işi bizim əvəzimizə görəcək.
Aşağıdakı hallardan hər hansı birində lock baş verəcək:
1) Update edərkən bütün update olunacaq row-lar lock olunur
2) İnsert etdikdə hər hansı primary key və unique key olan row-lar lock olunacaq ki, duplicate key error və.s baş verməsin…
3) LOCK TABLES -dan istifadə etməklə bütün cədvəli lock edə bilərik amma bu məsləhət görülmür…Çünki deyək ki 1 update üçün bütün cədvəli lock edib camaatı gözlətməyin bir mənası yoxdu.
4) Maraqlı bir şey də var…Select verərkən select-in sonunda FOR UPDATE və yaxud LOCK İN SHARE MODE yazmaqla biz select-dən gələn bütün row-ları lock etmiş oluruq.

Davam edək…Az öncə 4cü bənndə select-də lock-un istifadəsindən yazdıq…Select-in bu cür istifadəsi zamanı
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ERROR-u çıxa bilər…Gəlin bu error-u sınaq üçün çıxardaq…Lakin bundan əvvəl innodb_lock_wait_timeout system variable-ından danışaq. bu system dəyişəni adından da göründüyü kimi lock-un açılması üçün gözləmə vaxtını təyin edir.
default olaraq bu vaxt 50 saniyə təşkil edir.

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         50 |
+----------------------------+
1 row in set (0.00 sec)

biz bunu qəsdən 10 saniyə edək:

mysql> set @@innodb_lock_wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

Daha sonra 101000 row-luq bir select verək…Və eyni zamanda da bir update!
Select:

mysql> select * from customers for update;
.
.
.
101000 rows in set (0.78 sec)

Update:

mysql> update customers set customer_name='Shahriyar' where customer_id=500;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Bəli həqiqətən də select-imiz bizim cədvəli 10 saniyədən daha artıq bir müddətə lock etdiyi üçün qəşəng bir ERROR çıxdı…
Bu andan etibarən biz bu tip error-lar üçün stored procedure-lardan istifadə edəcik.Mən stored procedure-ların yaradılması, syntax xüsusiyyətləri haqqında ayrıca bir mövzu yazmaq istəmirəm.Bunun əvəzinə stored procedure-ların faydalı ola biləcəyi və çox əlverişli olduğu anlar haqqında yazacam…Ele indi həmin andır necə deyərlər…
Biz select-imizi adi şəkildə verəcik.Update-imizi isə procedure içində verəcik…Əgər ERROR çıxsa handler vasitəsilə bu ERROR-la məşğul olacıq. Əgər ERROR çıxmasa adi qaydada update olunacaq…Procedure kodumuz:

delimiter $$

create procedure lock_timeout_test
		(in_customer_name varchar(30),
		 in_customer_id int
		 )
begin 
		declare var_lock_timeout int default 0;
		
		declare lock_timeout_detected condition for 1205;
		declare continue handler for lock_timeout_detected set var_lock_timeout=1;

		set var_lock_timeout=0;
		
		start transaction;
			
			update customers set customer_name=in_customer_name
					where customer_id=in_customer_id;
		commit;

		if var_lock_timeout=1 then
				select 'Lock wait timeout exceeded ERROR xahis olunur select-lerde diqqetli olun';
				-- ve yaxud ERROR-la bagli istenilen nese!
		else 
				select 'Updated';
		end if;
	
end$$

Və yuxarıdakı kimi yenə select versək:

mysql> select * from customers for update;
.
.
.
101000 rows in set (0.41 sec)

Və procedure-umuzdan istifadə etsək:

mysql> call lock_timeout_test('Shahriyar',500);
+--------------------------------------------------------------------------+
| Lock wait timeout exceeded ERROR xahis olunur select-lerde diqqetli olun |
+--------------------------------------------------------------------------+
| Lock wait timeout exceeded ERROR xahis olunur select-lerde diqqetli olun |
+--------------------------------------------------------------------------+
1 row in set (10.88 sec)

Gözəldi elə deyil mi?😛

Sonuncu və ən əhəmiyyətli mövzu Deadlock…
A deadlock occurs when two transactions are each waiting for the other to release a lock-they each block each other, and neither can proceed.
Şəklimizə diqqət yetirək:

Və şəkildən də göründüyü kimi Transaction A update edir, daha sonra Transaction B update edir(Transaction A-nın sonlanmasını gözləmədən) və davam edərək Transaction A yenə update edir bu zaman artıq Transaction B-ni gözləməli olur. Və ən sonunda Transaction B update edir və bu zaman da Transaction A-ni gözləməli olur…Yəni o bunu gözləyir bu onu gözləyir elə gözləyə-gözləyə qalırlar ki axırda da olur deadlock…
Deadlock baş verdikdə MySQL\İnnoDB deadlock-u avtomatik detect edəcək, 2 transaction-dan birini məcburi rollback edəcək və yekə bir error çıxardacaq…Rollback olunan transaction isə cədvələ ən az dəyişiklik etməyə çalışan transaction olacaq…
Sınaq üçün bir deadlock yaradaq🙂 Cədvəlimiz və içindəki məlumatlar:

CREATE TABLE account_balance (
  account_id int(11) NOT NULL,
  balance decimal(6,2) NOT NULL DEFAULT '0.00',
  account_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO account_balance (account_id,balance,account_timestamp) VALUES (1,500.00,'2005-12-13 15:12:34');
INSERT INTO account_balance (account_id,balance,account_timestamp) VALUES (2,9999.99,'2005-12-11 05:01:41');
INSERT INTO account_balance (account_id,balance,account_timestamp) VALUES (324,4000.00,'2005-07-09 16:19:57');
INSERT INTO account_balance (account_id,balance,account_timestamp) VALUES (916,2000.00,'2005-07-09 16:19:57');

Və aşağıdakıları sırası ilə yazsaq(qeyd edim ki aşağıdakılar 2 fərqli transaction-dan yazılmalıdır):

-- 1ci transaction/ ilk once bunu yazin(1)
start transaction;

update account_balance
set balance=balance-100
where account_id=2;

-- 2ci transaction/ daha sonra bunu(2)
start transaction;

update account_balance
set balance=balance-300
where account_id=1;

-- 1ci transaction/ 3cu olaraq bunu (3)
update account_balance
set balance=balance+100
where account_id=1;

-- 2ci transaction/ 4cu olaraq da bunu (4)

update account_balance
set balance=balance+300
where account_id=2;

Və şəkillərdən də göründüyü kimi ürəyimiz istiyən şəkildə ERROR-umuzu alırıq:

Deadlock hər database-də meydana gələ bilər amma MySQL\İnnoDB kimi row-level locking edən database-lərdə bu hadisə çox nadir hallarda baş verəcək…Əgər ki, siz hər hansı iş üçün şübhəlisiniz ki, burada deadlock ola bilər onu handler-lə qarşılamaq olar.Bu zaman yenə stored procedure-dan istifadə edəcik.Procedure kodumuz:

delimiter $$

create procedure deadlock_test
				(from_account int,
				 to_account int,
				 tfer_amount numeric(10,2))
begin 
		
		declare deadlock int default 0;
		declare attempts int default 0;

		tfer_loop: while(attempts<3) do
			begin 
					declare deadlock_detected condition for 1213;
					declare exit handler for deadlock_detected
						begin 
								rollback;
								set deadlock=1;
						end;
					set deadlock=0;
					
					start transaction;
					
					update account_balance
						set balance=balance-tfer_amount
					where account_id=from_account;

					update account_balance
						set balance=balance+tfer_amount
					where account_id=to_account;

					commit;
			end;
		
		if deadlock=0 then
			leave tfer_loop;
		else 
		set	attempts=attempts+1;
		end if;
		
		end while tfer_loop;

		if deadlock=1 then
			select 'Deyisiklik olmadi...3 defe cehd olunmagina baxmayaraq Deadlock ERROR';
		else 
			select concat('Updated ',attempts,' cehd olundu');
		end if;
end$$

Bizim procedure Update etməyə çalışır.Əgər update zamanı deadlock baş verərsə, 3 dəfə transaction-u yenidən başladacaq(while loop). 3 dəfə cəhd etdikdən sonra yenə deadlock aradan qalxmayırsa loop-dan çıxır və
“Deyisiklik olmadi…3 defe cehd olunmagina baxmayaraq Deadlock ERROR” mesaji gosterilir.
Yox əgər heç bir deadlock baş vermirsə, sakitcə update olunur və Updated mesajı verilir.Vəssalam🙂

Təbii ki bizim kodumuza bu qədər Error handling yerləşdirdikdə onun oxunaqlığı azalır. Yuxarıdakı proceduru daha oxunaqlı vəziyyətə gətirək. Həmçinin aşağıdakı koddan error handler, loop və if-lər çıxardılmışdır. Burada account_id-lər select tərəfindən növbə ilə lock olunduğu üçün heç bir deadlock təhlükəsi yoxdur. Prosedur kodumuz:

delimiter $$

create procedure deadlock_test2
		(from_account int,
		to_account int,
		tfer_amount numeric(10,2))
begin 
	declare local_account_id int;
	declare lock_cursor cursor for
		select account_id
			from account_balance
		where account_id in(from_account,to_account)
	    order by account_id
		for update;

	start transaction;
	
	open lock_cursor;
	fetch lock_cursor into local_account_id;

	update account_balance
		set balance=balance-tfer_amount
	where account_id=from_account;

	update account_balance
		set balance=balance+tfer_amount
	where account_id=to_account;

	close lock_cursor;

	commit;

end$$

Deadlock haqqında bu qədər. Gördüyümüz kimi bu problemdən 2 növ prosedurla yaxa qurtarmaq olur🙂
Gələn yazımızda Lock Strategy-lərdən danışacıq…
Fikirlərinizi bölüşməyi unutmayın…Təşəkkürlər…

Kateqoriyalar: MySQL Etiketlər: , , ,
  1. Hələlik heç bir şərh yoxdur
  1. No trackbacks yet.

Bir cavab yazın

Sistemə daxil olmaq üçün məlumatlarınızı daxil edin və ya ikonlardan birinə tıklayın:

WordPress.com Loqosu

WordPress.com hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Twitter rəsmi

Twitter hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Facebook fotosu

Facebook hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Google+ foto

Google+ hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

%s qoşulma

%d bloqqer bunu bəyənir: