ТРАНЗАКЦИЯ SQL | www.nookery.ru

Транзакцией называется выполнение последовательности команд (SQL- конструкций) в базе данных, которая либо фиксируется при успешной реализации каждой команды, либо отменяется при неудачном выполнении хотя бы одной команды.

BEGIN TRANSACTION; ---начало транзакции
DECLARE @Id int; 
INSERT MyUserName VALUES ('TestName0','TestLName0');
SET @Id = @@IDENTITY;
INSERT MyUserTell VALUES (@Id,'(097)2224455');
COMMIT TRANSACTION;  ----конец транзакции

BEGIN TRANSACTION; ---начало транзакции

DECLARE @Id int;

INSERT MyUserName VALUES ('TestName0','TestLName0');

SET @Id = @@IDENTITY;

INSERT MyUserTell VALUES (@Id,'(097)2224455');

COMMIT TRANSACTION; ----конец транзакции

Откат транзакции — это действие, обеспечивающее аннулирование всех изменений данных, которые были сделаны в теле текущей незавершенной транзакции.

BEGIN TRANSACTION;
DECLARE @Id int;
INSERT MyUserName VALUES ('TestName0','TestLName0');
SET @Id = @@IDENTITY;
INSERT MyUserTell VALUES (@Id,'(097)2224455');
ROLLBACK TRANSACTION;

BEGIN TRANSACTION;

DECLARE @Id int;

INSERT MyUserName VALUES ('TestName0','TestLName0');

SET @Id = @@IDENTITY;

INSERT MyUserTell VALUES (@Id,'(097)2224455');

ROLLBACK TRANSACTION;

При выполнении транзакций несколькими пользователями одной базы данных могут возникать следующие проблемы:

Dirty reads
Non-repeatable reads
Phantom reads

Dirty reads – «грязное» чтение. Первый пользователь начинает транзакцию, изменяющую данные. В это время другой пользователь (или создаваемая им транзакция) извлекает частично измененные данные, которые не являются корректными.

Non-repeatable reads – неповторяемое чтение. Первый пользователь начинает транзакцию, изменяющую данные. В это время другой пользователь начинает и завершает другую транзакцию. Первый пользователь при повторном чтении данных (например, если в его транзакцию входит несколько инструкций SELECT) получает другой набор записей.

Phantom reads – чтение фантомов. Первый пользователь начинает транзакцию, выбирающую данные из таблицы. В это время другой пользователь начинает и завершает транзакцию, вставляющую или удаляющую записи. Первый пользователь получит другой набор данных, содержащий фантомы – удаленные или измененные строки.

Для решения этих проблем разработаны четыре уровня изоляции транзакции в SQL:

Read uncommitted.
Read committed.
Repeatable read.
Serializable.

Read uncommitted. Транзакция может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции может привести ко всем перечисленным проблемам.
Read committed. Транзакция не может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции исключает проблему «грязного» чтения.
Repeatable read. Транзакция не может считывать данные, с которыми работают другие транзакции. Другие транзакции также не могут считывать данные, с которыми работает эта транзакция. Применение этого уровня изоляции исключает все проблемы, кроме чтения фантомов.
Serializable. Транзакция полностью изолирована от других транзакций. Применение этого уровня изоляции полностью исключает все проблемы.

Уровни изолированности транзакций.
Применение в SQL

BEGIN TRAN SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE
DECLARE @OshibkiTabliciTourists int,
@OshibkiTabliciInfoTourists int
...
ROLLBACK TRAN

BEGIN TRAN SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

DECLARE @OshibkiTabliciTourists int,

@OshibkiTabliciInfoTourists int

...

ROLLBACK TRAN

--------------------------------------------------------------------
-------------------------- Транзакции ------------------------------
--------------------------------------------------------------------

---------------------------------------------------------
-- Мы уже использовали транзакции (неявно)
 BEGIN TRANSACTION; -- начало транзакции
 INSERT MyUserName
 VALUES('TestName0','TestLName0')
 COMMIT TRANSACTION; -- успешное завершение транзакции   
 
 SELECT * FROM MyUserName;
---------------------------------------------------------
 -- откат транзакции
 
 BEGIN TRANSACTION  -- начало транзакции

 INSERT MyUserName
 VALUES
	('RollTest1','RollTestL1')
	
 ROLLBACK TRANSACTION; -- происходит откат транзакции, наша вставка не выполнится, тоесть: 
					   -- INSERT MyUserName VALUES ('TestName1','RollTestL1') ,- не произойдет 

SELECT * FROM MyUserName;

---------------------------------------------------------
 
-- в одной транзакции можно выполнять несколько действий

BEGIN TRANSACTION; -- начало транзакции
 
DECLARE @Id int;

INSERT MyUserName
VALUES
	('TestName0','TestLName0')
	
SET @Id = @@IDENTITY;
		
INSERT MyUserTell
VALUES 
(@Id,'(097)2224455');
		
INSERT MyUserInfo
VALUES (@Id,'01/02/1990');

COMMIT TRANSACTION; -- успешное завершение транзакции   
 
SELECT * FROM MyUserName;
SELECT * FROM MyUserInfo;
SELECT * FROM MyUserTell;
GO

---------------------------------------------------------

-- В одной транзакции можно "откатить" несколько действий.

BEGIN TRANSACTION; -- Начало транзакции.
 
DECLARE @Id int;

INSERT MyUserName
VALUES
	('RollBackTest','TestLName0')
	
SET @Id = @@IDENTITY; 
		
INSERT MyUserTell
VALUES 
(@Id,'(097)2224455');
		
INSERT MyUserInfo
VALUES (@Id,'01/02/1990');

ROLLBACK TRANSACTION; -- Откат последовательности дейсвий.

SELECT * FROM MyUserName;
SELECT * FROM MyUserInfo;
SELECT * FROM MyUserTell;
---------------------------------------------------------
-- Точка сохранения транзакции (промежуточное сохранение транзакции)

BEGIN TRAN 

INSERT MyUserName
VALUES
	('SavePoinTest1','TestLName0')
SAVE TRAN SavePointTran; -- Точка сохранения транзакции (промежуточное сохранение транзакции)
PRINT 'SAVE POINT';
INSERT MyUserName
VALUES
	('SavePointTest2','TestLName0')

ROLLBACK TRAN SavePointTran; -- Откат к точке востановления.

COMMIT TRAN -- Снять комментарий после выполнения транзакции ниже по коду.

SELECT * FROM MyUserName;
GO

---------------------------------------------------------
-- Точка сохранения транзакции (промежуточное сохранение транзакции)

BEGIN TRAN 

INSERT MyUserName
VALUES
	('SavePoinTest3','TestLName0')
SAVE TRAN SavePointTran; -- Точка сохранения транзакции (промежуточное сохранение транзакции)
PRINT 'SAVE POINT';
INSERT MyUserName
VALUES
	('SavePointTest4','TestLName0')

ROLLBACK TRAN -- если не указана точка сохранения к которой нужно произвести откат, 
			  -- то откат происходит к началу транзакции

SELECT * FROM MyUserName;
GO

---------------------------------------------------------

DROP PROC MyTransactProc;
GO

-- Процедура записи данных о пользователе (имя, фамилия, номер телефона, дата рождения) , есть мальнькая ошибка, исправте пожалуйста...
CREATE PROC MyTransactProc
	@FName varchar(25),
	@LName varchar(25),
	@TellN char(12),
	@BDate date
AS
BEGIN
DECLARE @Id int;
BEGIN TRAN -- Начало транзакции.
			
		INSERT MyUserName
		VALUES (@FName,@LName);
		SET @Id = @@IDENTITY;
		
		INSERT MyUserTell
		VALUES (@Id,@TellN);
		
		INSERT MyUserInfo
		VALUES (@Id,@BDate);
		
IF EXISTS (SELECT * FROM MyUserName WHERE @FName = FName AND @LName = LName AND IdTest != @Id) -- Проверка на наличие данных об этом человеке.
			BEGIN
				ROLLBACK TRAN; -- Откат транзакции если человек существует.
				RETURN 1;
			END;	
			
		COMMIT TRAN ; -- Успешное завершение транзкции если данный пользователь еще не записан в БД.
END;	

 -- Проверка результатов работы процедуры. --
EXEC MyTransactProc			  -- Запись данных.
	 @Fname = 'Name1',
	 @LName = 'LName1', 
	 @TellN = '(097)7775566',
	 @BDate = '01/02/2010';
GO

EXEC MyTransactProc			  -- Повторная попытка ввести эти же данные.
	 @Fname = 'Name1',
	 @LName = 'LName1', 
	 @TellN = '(097)7775566' ,
	 @BDate = '01/02/2010';
GO

EXEC MyTransactProc				
	 @Fname = 'TestName2',
	 @LName = 'TestLName2', 
	 @TellN = '(097)7775566' ,
	 @BDate = '01/02/2010';
GO

SELECT * FROM MyUserName;
SELECT * FROM MyUserInfo;
SELECT * FROM MyUserTell;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

--------------------------------------------------------------------

-------------------------- Транзакции ------------------------------

--------------------------------------------------------------------

---------------------------------------------------------

-- Мы уже использовали транзакции (неявно)

BEGIN TRANSACTION; -- начало транзакции

INSERT MyUserName

VALUES('TestName0','TestLName0')

COMMIT TRANSACTION; -- успешное завершение транзакции

SELECT * FROM MyUserName;

---------------------------------------------------------

-- откат транзакции

BEGIN TRANSACTION -- начало транзакции

INSERT MyUserName

VALUES

('RollTest1','RollTestL1')

ROLLBACK TRANSACTION; -- происходит откат транзакции, наша вставка не выполнится, тоесть:

-- INSERT MyUserName VALUES ('TestName1','RollTestL1') ,- не произойдет

SELECT * FROM MyUserName;

---------------------------------------------------------

-- в одной транзакции можно выполнять несколько действий

BEGIN TRANSACTION; -- начало транзакции

DECLARE @Id int;

INSERT MyUserName

VALUES

('TestName0','TestLName0')

SET @Id = @@IDENTITY;

INSERT MyUserTell

VALUES

(@Id,'(097)2224455');

INSERT MyUserInfo

VALUES (@Id,'01/02/1990');

COMMIT TRANSACTION; -- успешное завершение транзакции

SELECT * FROM MyUserName;

SELECT * FROM MyUserInfo;

SELECT * FROM MyUserTell;

---------------------------------------------------------

-- В одной транзакции можно "откатить" несколько действий.

BEGIN TRANSACTION; -- Начало транзакции.

DECLARE @Id int;

INSERT MyUserName

VALUES

('RollBackTest','TestLName0')

SET @Id = @@IDENTITY;

INSERT MyUserTell

VALUES

(@Id,'(097)2224455');

INSERT MyUserInfo

VALUES (@Id,'01/02/1990');

ROLLBACK TRANSACTION; -- Откат последовательности дейсвий.

SELECT * FROM MyUserName;

SELECT * FROM MyUserInfo;

SELECT * FROM MyUserTell;

---------------------------------------------------------

-- Точка сохранения транзакции (промежуточное сохранение транзакции)

BEGIN TRAN

INSERT MyUserName

VALUES

('SavePoinTest1','TestLName0')

SAVE TRAN SavePointTran; -- Точка сохранения транзакции (промежуточное сохранение транзакции)

PRINT 'SAVE POINT';

INSERT MyUserName

VALUES

('SavePointTest2','TestLName0')

ROLLBACK TRAN SavePointTran; -- Откат к точке востановления.

COMMIT TRAN -- Снять комментарий после выполнения транзакции ниже по коду.

SELECT * FROM MyUserName;

---------------------------------------------------------

-- Точка сохранения транзакции (промежуточное сохранение транзакции)

BEGIN TRAN

INSERT MyUserName

VALUES

('SavePoinTest3','TestLName0')

SAVE TRAN SavePointTran; -- Точка сохранения транзакции (промежуточное сохранение транзакции)

PRINT 'SAVE POINT';

INSERT MyUserName

VALUES

('SavePointTest4','TestLName0')

ROLLBACK TRAN -- если не указана точка сохранения к которой нужно произвести откат,

-- то откат происходит к началу транзакции

SELECT * FROM MyUserName;

---------------------------------------------------------

DROP PROC MyTransactProc;

-- Процедура записи данных о пользователе (имя, фамилия, номер телефона, дата рождения) , есть мальнькая ошибка, исправте пожалуйста...

CREATE PROC MyTransactProc

@FName varchar(25),

@LName varchar(25),

@TellN char(12),

@BDate date

BEGIN

DECLARE @Id int;

BEGIN TRAN -- Начало транзакции.

INSERT MyUserName

VALUES (@FName,@LName);

SET @Id = @@IDENTITY;

INSERT MyUserTell

VALUES (@Id,@TellN);

INSERT MyUserInfo

VALUES (@Id,@BDate);

IF EXISTS (SELECT * FROM MyUserName WHERE @FName = FName AND @LName = LName AND IdTest != @Id) -- Проверка на наличие данных об этом человеке.

BEGIN

ROLLBACK TRAN; -- Откат транзакции если человек существует.

RETURN 1;

END;

COMMIT TRAN ; -- Успешное завершение транзкции если данный пользователь еще не записан в БД.

END;

-- Проверка результатов работы процедуры. --

EXEC MyTransactProc -- Запись данных.

@Fname = 'Name1',

@LName = 'LName1',

@TellN = '(097)7775566',

@BDate = '01/02/2010';

EXEC MyTransactProc -- Повторная попытка ввести эти же данные.

@Fname = 'Name1',

@LName = 'LName1',

@TellN = '(097)7775566' ,

@BDate = '01/02/2010';

EXEC MyTransactProc

@Fname = 'TestName2',

@LName = 'TestLName2',

@TellN = '(097)7775566' ,

@BDate = '01/02/2010';

SELECT * FROM MyUserName;

SELECT * FROM MyUserInfo;

SELECT * FROM MyUserTell;

Добавить комментарий Отменить ответ