Sql транзакции

Транзакция — это последовательность операций, выполняемых в логическом порядке пользователем, либо программой, которая работает с БД.

Транзакция – это распространение изменений в БД. Например, если мы создаём, изменяем или удаляем запись, то мы выполняем транзакцию. Крайне важно контролировать транзакции для гарантирования.

Основные концепции транзакции описываются аббревиатурой ACID –

  • Atomicity — Атомарность
  • Consistency — Согласованность
  • Isolation — Изолированность
  • Durability — Долговечность

Атомарность

гарантирует, что любая транзакция будет зафиксирована только целиком (полностью). Если одна из операций в последовательности не будет выполнена, то вся транзакция будет отменена. Тут вводится понятие “отката” (rollback). Т.е. внутри последовательности будут происходить определённые изменения, но по итогу все они будут отменены (“откачены”) и по итогу пользователь не увидит никаких изменений.

Согласованность

Согласованность означает, что любая завершённая транзакция (транзакция, которая достигла завершения транзакции – end of transaction) фиксирует только допустимые результаты. Например, при переводе денег с одного счёта на другой, в случае, если деньги ушли с одного счёта, они должны прийти на другой (это и есть согласованность системы). Списание и зачисление  – это две разные транзакции, поэтому первая транзакция пройдёт без ошибок, а второй просто не будет. Именно поэтому крайне важно учитывать это свойство и поддерживать баланс системы.

Изолированность

Каждая транзакция должна быть изолирована от других, т.е. её результат не должен зависеть от выполнения других параллельных транзакций. На практике, изолированность крайне труднодостижимая вещь, поэтому здесь вводится понятие “уровни изолированности” (транзакция изолируется не полностью).

Долговечность

Эта концепция гарантирует, что если мы получили подтверждение о выполнении транзакции, то изменения, вызванные этой транзакцией не должны быть отменены из-за сбоя системы (например, отключение электропитания).

Для управления транзакциями используются следующие команды:

  • COMMIT
    Сохраняет изменения
  • ROLLBACK
    Откатывает (отменяет) изменения
  • SAVEPOINT
    Создаёт точку к которой группа транзакций может откатиться
  • SET TRANSACTION
    Размещает имя транзакции
Create table t(
id int not null primary key,
s varchar (40),
si smallint);
insert into t(id,s) values (1,'first')
insert into t(id,s) values (3,'second')
insert into t(id,s) values (2,'third')
--tarnsaktsiond loomine
begin transaction;
insert into t(id,s) values (4,'foruth')
select*from t;
--tegevuesu tagasimine 
rollback;
select*from t;
--loomine transaktsionid, mis võtab tagasi kustutadtud kirjed
begin transaction;
delete from t where id > 1
select*from t;
rollback;
select*from t;

SQL ULENASNNE

Xampp task:

CREATE table developers (
id int primary key AUTO_INCREMENT,
    name varchar(100),
    speciality varchar(20),
    experience int,
    salary int
);

Создание Таблицы

insert into developers(name,speciality,experience,salary) values('Euegne','Java',2,2500);
insert into developers(name,speciality,experience,salary) values('Peter','Java',3,3500);
insert into developers(name,speciality,experience,salary) values('Andrei','C++',3,2500);
insert into developers(name,speciality,experience,salary) values('Konstantin','C#',2,2000);
insert into developers(name,speciality,experience,salary) values('Asya','UI/UX',2,1800);
insert into developers(name,speciality,experience,salary) values('Ivan','C#',1,900);
insert into developers(name,speciality,experience,salary) values('Ljudmila','UI/UX',2,1800);
select * from developers;

Принцип работы Commit и Rollback

SET autocommit=0; 

DELETE FROM developers 
       WHERE speciality= 'C++';
       
Commit;

Rollback не работает и не дложен так как мы написали Commit

Но если первый запрос мы сделаем без коммита

Rollback;

Принцип работы Savepoint

SAVEPOINT SP1;

DELETE FROM developers WHERE ID = 5;

DELETE FROM developers WHERE ID = 6;

DELETE FROM developers WHERE ID = 7;

Microsoft SQl SERVER

Создание таблицы которая была в видео и заполнение ее данными

Create table tblProduct(
Product int primary key identity(1,1),
NAme varchar (60),
Untprice int,
QtyAvaliable int);

insert into tblProduct(NAme,Untprice,QtyAvaliable) values ('Laptops',1230,12)
insert into tblProduct(NAme,Untprice,QtyAvaliable) values ('Phones',1333,12)
insert into tblProduct(NAme,Untprice,QtyAvaliable) values ('Sesktopes',1444,22)

обнoвление таблицы

update tblProduct set QtyAvaliable =200 where Product=1
Select * from tblProduct

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED: Эта часть запроса устанавливает уровень изоляции транзакций на «READ UNCOMMITTED». Это означает, что любая транзакция, выполняющаяся на этом уровне изоляции, может читать те данные, которые в данный момент изменяются другими транзакциями, что может привести к «грязным чтениям», т.е. чтениям незафиксированных изменений. Этот уровень изоляции обеспечивает наименьшую согласованность данных, но может обеспечить высокий параллелизм.

Set transaction isolation level read uncommitted

select * from tblProduct

Принцип работы Rollback

BEgin transaction
update tblProduct set QtyAvaliable =300 where Product=1

rollback transaction

Создаем еще 2 таблицы по видео и заполняем их

Create Table tblMailingAddress
(
AddressId int NOT NULL primary key,
EmployeeNumber int,
HouseNumber varchar(50),
StreetAddress varchar(50),
City varchar(10),
PostalCode varchar(50)
)

Create Table tblPhysicalAddress
(
 AddressId int NOT NULL primary key,
 EmployeeNumber int,
 HouseNumber nvarchar(50),
 StreetAddress nvarchar(50),
 City nvarchar(10),
 PostalCode nvarchar(50)
)

INSERT INTO tblMailingAddress(EmployeeNumber,HouseNumber,SheetAddress,City,PostalCode)
VALUES(101,'#10','King Street','LONDOON','CR27DW');
INSERT INTO tblPhysicalAddress(EmployeeNumber,HouseNumber,SheetAddress,City,PostalCode)
VALUES(101,'#10','King Street','LONDOON','CR27DW');

SELECT * FROM tblMailingAddress;
SELECT * FROM tblPhysicalAddress;

Созадние Процедуры на обновление полей в двух талицах и откатом через ролбэк если еть ошибка

CREATE PROCEDURE spUpdateAddress
as
Begin
	BEGIN TRY
		BEGIN TRANSACTION
			Update tblMailingAddress set City = 'LONDON'
			where Addressid = 1 and EmployeeNumber = 101

			Update tblPhysicalAddress set City = 'LONDON'
			where Addressid = 1 and EmployeeNumber = 101
		COMMIT TRANSACTION
		Print 'Transaction Committed'
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		Print 'Transaction Rolled Back'
	End Catch
END

exec spUpdateAddress;
SELECT * FROM tblMailingAddress;
SELECT * FROM tblPhysicalAddress;

Процедура работает верно мы видим ‘Transaction commited’ и данные в таблицы обновлены

После чего ма изменыем процедуру и вводим неверные данные

ALTER PROCEDURE spUpdateAddress
as
Begin
	BEGIN TRY
		BEGIN TRANSACTION
			Update tblMaiLingAddress set City = 'LONDON1'
			where Addressid = 1 and EmployeeNumber = 101

			Update tblPhysicalAddress set City = 'LONDON LONDON LONDON LONDON LONDON'
			where Addressid = 1 and EmployeeNumber = 101
		COMMIT TRANSACTION
		Print 'Transaction Committed'
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		Print 'Transaction Rolled Back'
	End Catch
END

Процедура не изменила данные в таблицах и показала Transaction rolled back