Arxiv

Posts Tagged ‘transaction’

Statements That Cause an Implicit Commit

MySQL Transaction
Transactions and Locks
Transaction başlatdıqda transaction ərzində daxil edilən bütün komandalar commit\rollback-i gözləməli olur. Və o zamana qədər heç bir dəyişiklik baş vermir. Lakin elə komandalar var ki, transaction daxilində olmasından asılı olmayaraq implicit commit olur.
Bunlardan Bəzilərinə baxacıq.
1.ALTER TABLE

-- cədvəlin yaradılması
mysql> create table commit_test(id int) engine=innodb;

mysql> show create table commit_test\G
CREATE TABLE `commit_test` (
  `id` int(11)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

(1)Transaction başladaq:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table commit_test modify id int not null;
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

Məntiqlə düşünməli idik ki, commit olunmayınca table alter olunmalı deyildi. Amma digər bir connection-dan baxdıqda artıq alter-in implicit commit olduğunun şahidi oluruq:
(2)

CREATE TABLE `commit_test` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2. DROP DATABASE

Ilk öncə schema-nı yaradaq:

mysql> create database for_drop;
Query OK, 1 row affected (0.11 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup_test        |
| cms                |
| data               |
| db1                |
| db_first           |
| exam_db            |
| for_drop    
.
.
18 rows in set (0.00 sec)

Transaction daxilində drop database-i işlədək:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database for_drop;
Query OK, 0 rows affected (0.29 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

rollback etməyimizə baxmayaraq…artıq drop olunub…
Digər connection-dan baxdıqda:

mysql> use for_drop;
ERROR 1049 (42000): Unknown database 'for_drop'

3. TRUNCATE TABLE

mysql> insert into commit_test() values(1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.16 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from commit_test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+
8 rows in set (0.00 sec)

Transaction daxilində truncate edək:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table commit_test;
Query OK, 0 rows affected (0.25 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

rollback-ə baxmayaraq truncate işlədi. digər connection-dan baxsaq:

mysql> select * from commit_test;
Empty set (0.00 sec)

4. RENAME TABLE

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> rename table commit_test to commit_test85;
Query OK, 0 rows affected (0.84 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

Digər connection-dan:

mysql> select * from commit_test;
ERROR 1146 (42S02): Table 'tr_blog.commit_test' doesn't exist
mysql> select * from commit_test85;
Empty set (0.00 sec)

Belə komandalar Sayca çoxdular hamısını yazmaq lazım deyil məncə.
İmplicit commit olan statements full list:
Statements That Cause an Implicit Commit

Təşəkkürlər 🙂

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: , , ,

MySQL Transaction

Transaction-ın nə olduğu barədə tonla material var internetdə. Ona görə də, MySQL-də Transaction haqqında qısa olaraq bəhs edəcik ki, mən də bunu indi oxuyub öyrənmişəm.

MySQL-də Transaction dəstəyi 4.0-cı versiyadan var və bu dəstək yalnız İnnoDB saxlama mexanizmində təklif olunur (Oracle, PostgreSQL və MS SQL-də bu dəstək çoxdan var imiş).

Deməli ilk öncə biz table tiplərini İnnoDB-yə keçirməliyik. Bunun üçün:

ALTER TABLE tablename ENGİNE=İNNODB;

Məndə default engine İnnoDB olduğu üçün ehtiyac yoxdu 😉

İndi isə table yaradaq:

CREATE TABLE military (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(15),
surname VARCHAR(15),
occupation VARCHAR(10), PRIMARY KEY (id)
);

Transaction-u başlatmaq üçün:

START TRANSACTION;

İndi isə yaratdığımız table-a insert edək:

insert into military (name,surname,occupation) values ('Ahmed','Memmedov','lieutinant');
INSERT INTO military (name,surname,occupation) VALUES ('Necib','Samedov','captain');

Transaction-u ROLLBACK və ya COMMİT-lə sonlandırmaq lazımdır.Sınaq üçün heç bir şey etmədən EXİT;-ləsək.Və daha sonra yenidən qoşulub select versək EMPTY SET qayıdacaq.
Lakin COMMİT etsəydik, 2 insert-in 2sini də görmüş olardıq. ROLLBACK isə bütün edilmiş əməliyyatları geri qaytarır. Demək ki, EXİT=ROLLBACK 🙂

blog_image_1

blog_image_2

Default olaraq hər transaction bitdikdən sonra MySQL avtomatik olaraq yeni transaction başlatmır.
Demək ki, hər dəfə yeni transaction başlatmaq lazım gələcək.
START TRANSACTİON-a əlavə olaraq ADD CHAİNRELEASE komandaları da var.
ADD CHAİN-her transaction bitdikdən sonra yenisini başladır.
RELEASE-her transaction bitdikdən sonra user connection-u ləğv edir.
Eyni nəticəyə completion_type dəyişənini ya hər session-da(SET vasitəsilə) ya da qlobal olaraq dəyişə bilərik (MySQL konfiqurasiya faylından istifadə etməklə).
SAVEPOİNT (yadda saxlama nöqtələri) haqqında kiçicik danışaq. İlk öncə ardıcıl olaraq aşağıdakı kodları keçirək:

START TRANSACTION;

INSERT INTO military (name,surname,occupation) VALUES ('Ahmed','Memmedov','lieutinant');
INSERT INTO military (name,surname,occupation) VALUES ('Necib','Samedov','captain');

SAVEPOINT ml1;

INSERT INTO military (name,surname,occupation) VALUES ('Receb','Babayev','captain');

SAVEPOINT ml2;

INSERT INTO military (name,surname,occupation) VALUES ('Nihad','Ovcuyev','major');

SAVEPOINT ml3;

Və şəkildəki kimi ROLLBACK TO SAVEPOİNT komandasını verdikdə. Uyğun savepoint nöqtəsindən sonrakı bütün əməliyyatlar rollback olunacaq.

blog_image_3

SAVEPOİNT-ler haqqında bəzi vacib şeyləri yadda saxlamaq lazımdır (çox dəhşətdir elə deyil mi? 😛 )
1) Bir transaction-da bir neçə savepoint ola bilər. Yetər ki onların adları fərqli olsun (identifikatorları). Eyni adlı savepoint sonuncudan əvvəlki bütün savepoint-ləri silir.
2) Rollback savepoint komandas transaction-u sonlandırmır. Bunun üçün ya commit ya da rollback olmalıdır. Əgər rollback savepoint-dəki nöqtə mövcud deyilsə MySQL error verəcək.
3) Savepoint-i silmək üçün RELEASE SAVEPOİNT-dən istifadə etmək lazımdır.

MySQL default olaraq hər SQL sorğunu avtomatik olaraq commit edir. Başqa sözlə desək MySQL hır sorğunu ayrıca transaction kimi görür və onu avtomatik olaraq commit edir. Hər dəfə start transaction-a ehtiyac yoxdur yəni ki. Buna AUTOCOMMİT rejimi deyilir. Bu rejimi dəyişmək üçün

SET autocommit=0;

Yenidən aktiv etmək üçün:

SET autocommit=1;

Son mövzu isə TRANSACTİON İSOLATİON LEVEL-dir ki, 4 dənədir.
READ UNCOMMİTTED
READ COMMİTTED
REPEATABLE READ
SERİALİZABLE
MySQL-də default olaraq REPEATABLE READ-dir.
Dəyişmək üçün:

SET TRANSACTION ISOLATION LEVEL isolationname;

TRANSACTİON İSOLATİON LEVEL-i öyrənmək üçün:

blog_image_4

Default olaraq TRANSACTİON İSOLATİON LEVEL hər session üçün keçərlidir. Global olaraq dəyişmək üçün:

SET GLOBAL TRANSACTION ISOLATION LEVEL isolationname;

Təşəkkürlər 🙂

Kateqoriyalar: MySQL Etiketlər: , , , ,