Транзакцията в SQL Server е a последователна група от изявления или запитвания за изпълнение на една или няколко задачи в база данни. Всяка транзакция може да има отделни операции за четене, запис, актуализиране или изтриване или комбинация от всички тези операции. Всяка транзакция трябва да се случи две неща в SQL Server:
- Или всички модификации са успешни, когато транзакцията е ангажирана.
- Или всички модификации се отменят, когато транзакцията е връщане назад.
Транзакцията не може да бъде успешна, докато не бъдат завършени всички операции в набора. Това означава, че ако някой аргумент е неуспешен, операцията по транзакцията ще се провали. Всяка транзакция започва с първия изпълним SQL оператор и завършва, когато открие ангажимент или връщане назад, изрично или имплицитно. Той използва АНГАЖИРАНЕ или ВЪРТАНЕ изрично, както и неявно, когато се използва DDL израз.
Изображението по-долу обяснява процеса на транзакция:
Следният пример ще обясни концепцията за транзакция:
Този пример ще използва системата от банкови бази данни, за да обясни концепцията за транзакция. Да предположим, че клиент на банка иска да изтегли пари от сметката си, като използва режим на банкомат. Банкоматът може да извърши тази операция в три стъпки:
- The първа стъпка е да проверите наличността на заявената сума по сметката.
- The втора стъпка удържа сумата от сметката, ако сумата е налична и след това актуализира баланса на сметката.
- The трета стъпка е да запишете операцията за теглене на пари в лог файла. Тази стъпка пише, че транзакцията е успешна или неуспешна. Ако е успешно, запишете промяната на данните в базата данни. В противен случай транзакцията ще бъде върната в предишното си състояние.
Основният принцип зад транзакциите е, че ако един от изразите върне грешка, целият набор от промени се връща назад, за да се гарантира целостта на данните. И ако транзакциите станат успешни, всички промени ще бъдат постоянни в базата данни. Следователно, ако има прекъсване на захранването или други проблеми при теглене на пари от банкомат, транзакциите гарантират, че балансът ни остава постоянен. Изявлението за транзакция изпълнява най-добре тези операции, защото четирите ключови свойства на транзакцията правят всички операции по-точни и последователни. Четирите свойства на транзакцията се наричат ACID.
Свойства на транзакция
Свойствата на транзакцията се наричат свойство ACID (Атомичност, Консистенция, Изолация, Издръжливост), което се обсъжда подробно по-долу:
Атомност: Това свойство гарантира, че всички отчети или операции, включени в транзакцията, трябва да бъдат изпълнени успешно. В противен случай цялата транзакция ще бъде прекъсната и всички операции се връщат обратно в предишното си състояние, когато някоя операция е неуспешна.
Консистенция: Това свойство гарантира, че базата данни променя състоянието само когато дадена транзакция бъде извършена успешно. Той също така отговаря за защитата на данните от сривове.
Изолация: Това свойство гарантира, че всички транзакции са изолирани от други транзакции, което означава, че всяка операция в транзакцията се управлява независимо. Той също така гарантира, че изявленията са прозрачни един за друг.
Издръжливост: Това свойство гарантира, че резултатът от извършените транзакции се запазва в базата данни за постоянно, дори ако системата се срине или се повреди.
Режими на транзакция в SQL Server
Има три различни режима на транзакция, които SQL Server може да използва:
Автоматичен режим на транзакция: Това е режимът на транзакция по подразбиране на SQL Server. Той ще оцени всеки SQL израз като транзакция и резултатите съответно се ангажират или върнат назад. По този начин успешните оператори незабавно се ангажират, докато неуспешните оператори незабавно се връщат назад.
Режим на имплицитна транзакция. Този режим позволява на SQL Server да започне имплицитната транзакция за всеки DML израз, но изрично изисква използването на команди за извършване или връщане назад в края на изразите.
Явен режим на транзакция: Този режим се определя от потребителя, което ни позволява да идентифицираме точно началната и крайната точка на транзакция. Автоматично ще се прекрати в случай на фатална грешка.
Контрол на транзакциите
Следните команди се използват за контрол на транзакциите:
ЗАБЕЛЕЖКА: Можем да използваме само DML изрази (INSERT, UPDATE и DELETE) за команди на Transaction Control Language. Не можем да ги използваме, докато създаваме или премахваме таблици, защото тези операции се ангажират автоматично с базата данни.
Състояние на транзакцията
Той показва как преминават транзакциите през целия им живот. Той описва текущото състояние на транзакцията, както и как транзакцията ще бъде обработена в бъдеще. Тези състояния дефинират правилата, които определят дали транзакцията се ангажира или прекъсва.
Нека опишем състоянията на всяка транзакция в SQL Server:
Активно състояние: Транзакцията е в активно състояние, докато инструкциите на транзакцията се изпълняват. Променя се на 'частично обвързано състояние' ако всички операции за четене и запис са завършени без грешки. Ако някоя инструкция е неуспешна, тя се променя в „неуспешно състояние“.
Частично ангажиран: Когато всички операции за четене и запис са завършени, промяната се извършва в основната памет или локалния буфер. Държавата ще отиде при 'отдадено състояние' ако промените са направени постоянни в базата данни. В противен случай преминава в „неуспешно състояние“.
Неуспешно състояние: Една транзакция преминава в неуспешно състояние, когато някоя инструкция на транзакция е неуспешна или постоянна модификация на базата данни е неуспешна.
Прекратено състояние: Транзакцията се движи от a 'неуспешно състояние' към ан 'прекратено състояние' когато възникне някакъв вид повреда. Промените се премахват или връщат назад, защото тези промени се правят само в локалния буфер или основната памет в предишни състояния.
Ангажирано състояние: Транзакцията е завършена и преминава в това състояние, когато промените са направени постоянни в базата данни и прекратени в „прекратено състояние“.
Прекратено състояние: Ако няма връщане назад и транзакцията е в „отдадена държава“, системата е последователна и готова за нова транзакция, докато старата е прекратена.
Катрина Кайф
Внедряване на транзакция в SQL Server
Нека вземем няколко примера, за да разберем как можем да внедрим транзакцията в SQL Server. Тук ще използваме „Продукт“ таблица за демонстриране на всички състояния на транзакция.
Следните SQL скриптове създават продуктовата таблица в избраната база данни:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
След това изпълнете скриптовете по-долу, за да вмъкнете данни в тази таблица:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Изпълнете командата SELECT, за да проверите данните:
Пример за транзакция COMMIT
Добра идея е да разделите SQL изразите, използвани в транзакцията, на множество логически части. И тогава можем да решим дали да ангажираме или върнем данните. Следните стъпки илюстрират създаването на транзакция:
- Стартирайте транзакцията с помощта на ЗАПОЧНЕТЕ ТРАНЗАКЦИЯТА команда.
- Напишете SQL операторите и ги разделете според нашите нужди
- Използвай АНГАЖИРАНЕ извлечение, за да завършите транзакцията и да запазите промените за постоянно.
По-долу са командите, които обясняват операциите COMMIT в SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Ако не бъде намерена грешка, ще видим следния изход, където всеки SQL оператор на транзакция се изпълнява независимо:
Операторите INSERT и UPDATE не могат да бъдат върнати след извършване на транзакцията. Когато проверим таблицата след извършване на операция, ще видим следните данни:
Пример за транзакция ROLLBACK
Ще използваме командата ROLLBACK, за да отменим всички транзакции, които все още не са записани в базата данни, и да се върнем към точката, в която транзакцията е започнала. Следният пример обяснява операцията ROLLBACK в SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
След като изпълним горната транзакция, можем да видим, че тя ще бъде изпълнена успешно. Това обаче няма да засегне никакви промени в базата данни, защото докато не изпълним оператора COMMIT или ROLLBACK, промените не могат да станат постоянни. Следователно имаме опцията да използваме командата за транзакция ROLLBACK, за да върнем всички операции на базата данни. Ето пълното извлечение от сделката:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Използване на глобална променлива @@Error в транзакции:
Тази променлива е използва се за проверка дали има грешка или не. Примерът по-долу обяснява неговата концепция. Тук първо ще започнем транзакцията с помощта на командата BEGIN и след това ще напишем два израза за вмъкване. След това ще използваме глобалната системна променлива @@ГРЕШКА в IF изявление за проверка на грешка. Ако стойността е по-голяма от 0, това означава, че има някаква грешка. Сега транзакцията е върната назад; в противен случай транзакцията е ангажирана.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Когато горната транзакция бъде изпълнена, ще забележим, че тя е върната назад. Това се дължи на опита ни да вмъкнем дублирана стойност в колоната Първичен ключ.
Транзакция с автоматично връщане назад
Повечето транзакции съдържат повече от една заявка. По време на изпълнение на транзакцията, ако някой от SQL операторите генерира грешка, в базата данни не се появяват модификации и останалите оператори не се изпълняват. Тази концепция е известна като транзакция за автоматично връщане назад в SQL Server. Нека използваме прост пример, за да демонстрираме този процес.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Тази транзакция произвежда следния резултат:
В този резултат можем да видим, че изразът за вмъкване е изпълнен успешно. Изпълнението на оператора за актуализиране обаче откри грешка поради проблема с преобразуването на типа данни. В този случай SQL Server не позволява никакви промени в базата данни, което означава, че операцията за вмъкване не добавя никаква стойност и командата select не се изпълнява.
Savepoint в Транзакции
Точката за запис вмъква специален знак в транзакция, който ни позволява да върнем всички промени, извършени след точката за запис. Използва се и за връщане назад на всяка конкретна част от транзакцията, а не на цялата транзакция. Можем да го дефинираме с помощта на ЗАПАЗЕТЕ ТРАНЗАКЦИЯ sp_name изявление. Следващият пример ще обясни използването на точка за запис в транзакции, които ангажират израза за вмъкване и връщат назад израза за изтриване.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Вижте резултата по-долу, където можем да видим, че идентификаторът на продукта 116 е изтрит и 117 е вмъкнат в първия изход. Във втория изход обаче операцията по изтриване се връща назад поради точката за запис.
Как да освободите точка за запис в транзакция?
динамично програмиране
Release savepoint се използва за премахване на назованата savepoint от текущата транзакция, без да се връщат резултатите от заявките, изпълнени след savepoint. MySQL има тази команда, но SQL Server не предоставя никаква команда за освобождаване на точка за запис. Вместо това те се освобождават автоматично в края на транзакция за извършване или връщане назад, така че не е нужно да се тревожим за тях междинно.
Неявна транзакция в SQL Server
Можем да дефинираме имплицитна транзакция, като активираме опцията IMPLICIT_TRANSACTIONS. Следният пример ще обясни тази концепция лесно:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
В тази сделка сме използвали две опции @@OPTION и @@TRANCOUNT. @@OPTOPN предоставя информация относно текущите опции SET, а @@TRANCOUNT предоставя израза BEGIN TRANSACTION в текущата сесия.
Сега изпълнението на транзакцията ще върне изхода по-долу:
Изрична транзакция в SQL Server
Явна транзакция трябва да бъде дефинирана чрез командата BEGIN TRANSACTION, тъй като тя идентифицира началната точка на изричната транзакция. Можем да дефинираме изричната транзакция в SQL Server, както е показано по-долу:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
В синтаксиса опцията trans_name указва уникално име на транзакция. The @trans_name_var показва дефинирана от потребителя променлива, която съхранява името на транзакцията. И накрая, на МАРК опция ни позволява да маркираме конкретна транзакция в регистрационния файл.
Явната транзакция чрез командата BEGIN TRANSACTION получи заключване в зависимост от нивото на изолация на ресурсите, свързани с транзакция. Помага за намаляване на проблемите със заключване. Вижте примера по-долу:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Ето резултата:
Маркирана транзакция в SQL Server
Маркираната транзакция се използва за добавяне на описание към конкретна транзакция в регистрационните файлове. Можем да го използваме като точка за възстановяване вместо дата и час, когато възстановяваме база данни до предишно състояние. Трябва да знаем, че маркировката се добавя в регистрационните файлове само когато маркираната транзакция модифицира базата данни. Можем да разберем неговата концепция, като използваме следния пример.
Да предположим, че случайно сме модифицирали базата данни и не знаем точния момент на промяната на данните; в този случай възстановяването на данни може да отнеме много време. Въпреки това, ако използваме маркираните транзакции, това може да бъде полезен инструмент за определяне на точното време на промените в данните.
Следният синтаксис илюстрира маркираната транзакция в SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Тук трябва да дефинираме името на транзакцията и след това да добавим опцията WITH MARK. В примера по-долу ще изтрием записи и ще добавим знака в регистрационния файл:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
The logmarkhistory таблицата е включена в msdb база данни и съхранява информация относно всяка маркирана транзакция, която е била ангажирана. Изпълнете оператора по-долу, за да получите подробностите от таблицата logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Именувана транзакция в SQL Server
Можем също така да предоставим име за нашата транзакция в SQL Server. Винаги се препоръчва използването на именованата транзакция, когато работите с много транзакции в една заявка. Примерът по-долу обяснява как да преименувате транзакция:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Ето резултата:
Заключение
Тази статия ще даде пълен преглед на транзакцията в отчетите на SQL Server. Транзакциите са полезни в системите за релационни бази данни, защото гарантират целостта на базата данни.