Транзакции в MySQL

Транзакции в MySQL

От автора: очень часто при решении тех или иных задач необходимо выполнять несколько запросов к базе данных. Но как же поступить, если это очень важные запросы, и каждый из них должен обязательно выполнится. Другими словами, если при выполнении одного из запросов возникает ошибка либо запрос не выполняется по другим сторонним причинам – это может нарушить работу всего скрипта в целом и привести к довольно неприятным результатам. Поэтому в данном уроке мы с Вами рассмотрим транзакции, которые обеспечат 100% выполнение всех запросов в базе данных либо не выполнение их вообще при возникновении ошибок.

автор

Автор: Виктор Гавриленко

Меня зовут Виктор Гавриленко, по образованию я инженер электромеханик. Еще учась в институте, начал увлекаться компьютерными технологиями и программированием, на протяжении двух лет усиленно занимаюсь разработкой интернет сайтов и веб-программированием. Увлекаюсь написанием небольших скриптов на языке PHP, в связке базами данных MySQL, SQLite, неплохо знаю такие движки как WordPress, Drupal, Joomla.

скачать исходникискачать урок

1. Постановка задачи

Для сегодняшнего урока я подготовил вот такую HTML страницу:

Как Вы видите — это всего одна страница интернет магазина. А именно страница отображения товаров из каталога – центральный блок, и корзина, в которой уже содержится несколько товаров.

На самом деле – это статическая страница и конечно сам механизм добавления товаров в корзину не работает, так как тема урока совсем другая. Но тем не менее три товара, которые отображены в корзине – сохранены в сессии и если нажать по ссылке “Оформить заказ”, то заказ на покупку данных товаров будет оформлен. Как раз на примере оформления заказа товара мы с Вами увидим все преимущества использования транзакций при работе с базой данных. Но перед этим, давайте рассмотрим, как работает данный тестовый сайт.

Итак, весь контент данного тестового сайта, содержится в базе данных, а именно в таблице magazine. Также в базе содержатся еще три таблицы – otpravleno, user_cash, zakazi.

Таблица otpravleno – используется для оплаченных и как бы отправленных заказов (опять же виртуально), таблица user_cash – хранит виртуальные средства пользователя, как будто у нас на сайте действуют виртуальные денежные средства, которыми можно оплачивать товар (это нужно просто для примера) и наконец, таблица zakazi –все заказы оформленные на сайте.
То есть процесс оформления заказа сводится к последовательному выполнению нескольких SQL запросов к базе данных: добавление в таблицу zakazi – данных об оформленном заказе, снятие денежных средств у пользователя за купленные товары (то есть изменение данных в таблице user_cash), добавление в таблицу otpravleno данных об оплаченных товарах и наконец изменение количества товаров на складе(то есть уменьшение количества товаров в таблице magazine, что были куплены).
Конечно, в реальном магазине такие запросы не выполняются, но для данного урока такой пример будет как раз кстати.
Теперь давайте кратко рассмотрим исходный код данного сайта.

Итак, основа логики сайта – это файл functions.php, в котором описаны все необходимые для работы сайта функции.

<?php
function connect_db() {
	//Подключаемся к серверу базы данных
	$db = mysqli_connect('localhost','Viktor','1234','for_cart');
	if(!$db) {
		exit('Error'.mysqli_error());
	}
	//Устанавливаем кодировку запросов
	mysqli_query($db,"SET NAMES cp1251");
	
	return $db;
}
function get_goods($db) {
	//Запрос на выборку все товаров
	$sql = "SELECT * FROM magazine";
	$result = mysqli_query($db,$sql);
	for($i = 0;$i < mysqli_num_rows($result); $i++) {
		$goods[] = mysqli_fetch_array($result);
	}
	
	return $goods;
}

function get_cart($db) {
	$sql = "SELECT id,title FROM magazine LIMIT 3";
	$result = mysqli_query($db,$sql);
	for($i = 0;$i < mysqli_num_rows($result); $i++) {
		$res =  mysqli_fetch_array($result,MYSQLI_ASSOC);
		$_SESSION['cart'][$res['id']] = $res['title'];
	}
}

function zakaz($db) {
		
		foreach($_SESSION['cart'] as $key=>$value) {
			$sql1 = "INSERT INTO zakazi (title) VALUES ('$value')";
			$result1 = mysqli_query($db,$sql1);
			
			
			
			$sql2 = "UPDATE user_cash SET cash=cash-(SELECT price FROM magazine WHERE id='$key')";
			$result2 = mysqli_query($db,$sql2);
			
			
			
			$sql3 = "INSERT INTO otpravleno (ti1tle) VALUES ('$value')";
			$result3 = mysqli_query($db,$sql3);

			$sql4 = "UPDATE magazine SET kolvo=kolvo-1 WHERE id='$key'";
			$result4 = mysqli_query($db,$sql4);
			
		}
		
	
}
?>

Кратко о каждой функции:

connect_db – функция которая выполняет подключение к базе данных.

get_goods – функция которая получает из базы данных, данные о товарах и возвращает их в виде массива.

get_cart – функция которая сохраняет в сессии три первых товара из базы данных (буквально для примера, что бы было с чего оформлять заказ).

zakaz — функция оформления заказа. Данная функция последовательно выполняет запросы к базе данных о которых я говорил выше.

Также частью исходного кода, являются файлы index.php и cart.php, но их код я сейчас приводить не буду, так как он очень простой. Данные файлы Вы сможете найти в исходниках к данному уроку.

Из всего выше сказанного следует – для того что бы оформить заказ необходимо что бы выполнились без ошибок SQL запросы в функции zakaz. Но представьте себе следующую ситуацию: к примеру пользователь выбрал товары для покупки, нажал на кнопку Оформить заказ и начинает выполняться функция zakaz, то есть выполняется первый запрос – сохраняем данные о заказе, далее второй – списываем денежные средства со счета пользователя (изменяем данные в таблице user_cash), но при выполнении третьего запроса возникает ошибка. Что при этом получается — заказ оформлен, деньги у пользователя сняты со счета, но товар не был передан на отправку. Согласитесь довольно неприятная ситуация. Поэтому давайте найдем решение данной проблемы.

2. Что такое транзакции?

Транзакции – это последовательность различных SQL запросов, выполняющихся как одно целое, и не прерывающееся другими клиентами. То есть, когда выполняются запросы транзакции, доступ к записям никто получить не может.

Благодаря транзакциям мы можем, как бы создать группу запросов, которые гарантированно будут выполнены без ошибок. Если же в ходе выполнения транзакции происходит сбой, или возникают ошибки, то результаты выполнения всех запросов до места возникновения ошибки отменяются. Тем самым, состояние базы данных, возвращается в исходное – до момента выполнения запросов транзакции.

К примеру, если Вам нужно выполнить подряд 100 SQL запросов, и Вы, при этом используете транзакции, то, если в ходе выполнения этих запросов хотя бы один из них выполнится с ошибкой, или не выполнится вообще – состояние базы данных вернется к своему исходному состоянию – на момент выполнения данных запросов. И только после успешного выполнения последнего 100го запроса все изменения вступят в силу.

Суть транзакций заключается в том, что по умолчанию отключено подтверждение выполнения запросов, то есть после выполнения запроса база данных будет ждать специальное сообщение о подтверждении успешного выполнения запроса – сообщение commit. Как только будет получено данное сообщение – изменения вступят в силу. Если же отправить в базу данных сообщение rollback – произойдет откат всех изменений (отмена) в базе данных на момент начала выполнения транзакции.

Транзакции выполняются путем ведения журнала всех изменений, вносимых в базу данных при работе каждой транзакции. Когда выполняется откат изменений, база данных обращается к журналу и отменяет все выполненные запросы для текущей транзакции.

3. Применение транзакций к тестовому скрипту

Итак, для того что бы начать работать с транзакциями, первым делом необходимо отменить автоматическое подтверждение выполнения SQL запросов в базе данных. Для этого используем функцию:

mysqli_autocommit($db,FALSE);

Хочу отметить, что для работы с базой данных я использую расширения языка php mysqli, так как расширение mysql уже существенно устарело, и не содержит в себе, функций для работы с транзакциями. Еще одно важное замечание: для работы с транзакциями необходимо использовать таблицы в базе данных типа InnoDB, тип MyISAM – транзакции не поддерживает.

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

mysqli_rollback($db);

Если же Вы уверены, что все запросы успешно выполнены – значит можно отправлять сообщение commit – подтверждение выполнения транзакции:

mysqli_commit($db);

Теперь давайте изменим функцию zakaz таким образом, что бы отправка и выполнение запросов осуществлялось с помощью транзакций.

function zakaz($db) {
		
		mysqli_autocommit($db,FALSE);
		
	
			foreach($_SESSION['cart'] as $key=>$value) {
			$sql1 = "INSERT INTO zakazi (title) VALUES ('$value')";
			$result1 = mysqli_query($db,$sql1);
			
			if(!$result1) {
				mysqli_rollback($db);
				return;
			}
			
			$sql2 = "UPDATE user_cash SET cash=cash-(SELECT price FROM magazine WHERE id='$key')";
			$result2 = mysqli_query($db,$sql2);
			if(!$result2) {
				mysqli_rollback($db);
				return;
			}
			
			
			$sql3 = "INSERT INTO otpravleno (ti1tle) VALUES ('$value')";
			$result3 = mysqli_query($db,$sql3);
			if(!$result3) {
				mysqli_rollback($db);
				return;
			}
			
			
			$sql4 = "UPDATE magazine SET kolvo=kolvo-1 WHERE id='$key'";
			$result4 = mysqli_query($db,$sql4);
			if(!$result4) {
				mysqli_rollback($db);
				return;
			}
		}
		
		mysqli_commit($db);
}

Как Вы видите, выполняем все, о чем я говорил выше, то есть отключаем автоподтверждение выполняемых запросов (mysqli_autocommit($db,FALSE)), затем после каждого запроса выполняем проверку наличия ошибок, если же есть ошибки немедленно выполняем откат всех изменений (mysqli_rollback($db)). Здесь также важно выполнять выход их функции, в нашем случае простой возврат (return), так как если запрос выполнен с ошибкой, нет смысла продолжать выполнение кода функции zakaz. Если же все запросы выполнены успешно, подтверждаем транзакцию (mysqli_commit($db);).

Перед тем как проверить, я предлагаю функцию zakaz описать другим способом, а именно, использовать блоки try-catch для проверки выполненных запросов. Напомню, что блоки try-catch – реализуют механизм обработки исключений языка PHP.

function zakaz($db) {
		
		mysqli_autocommit($db,FALSE);
		
		try {
			foreach($_SESSION['cart'] as $key=>$value) {
			$sql1 = "INSERT INTO zakazi (title) VALUES ('$value')";
			$result1 = mysqli_query($db,$sql1);
			
			if(!$result1) {
				throw new Exception();
			}
			
			$sql2 = "UPDATE user_cash SET cash=cash-(SELECT price FROM magazine WHERE id='$key')";
			$result2 = mysqli_query($db,$sql2);
			if(!$result2) {
				throw new Exception();
			}
			
			
			$sql3 = "INSERT INTO otpravleno (ti1tle) VALUES ('$value')";
			$result3 = mysqli_query($db,$sql3);
			if(!$result3) {
				throw new Exception();
			}
			
			
			$sql4 = "UPDATE magazine SET kolvo=kolvo-1 WHERE id='$key'";
			$result4 = mysqli_query($db,$sql4);
			if(!$result4) {
				throw new Exception();
			}
			
		}
		}
		catch(Exception $e) {
			mysqli_rollback($db);
		}
		
		mysqli_commit($db);
}

Смотрите, весь код цикла мы заключаем в блок try, а в каждом блоке if() мы с Вами генерируем исключение. То есть когда выполнится, хотя бы один блок if, сразу же будет создано исключение, и скрипт мгновенно будет перенаправлен в блок catch, который в свою очередь и выполнит откат всех изменений. Если же все нормально, и ни один из блоков if не выполнится, значит нужно просто подтвердить транзакцию, что мы собственно и делаем.

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

На этом я данный урок завершаю, всего Вам доброго и до встречи в следующих уроках.

Хотите изучить MySQL?

Прямо сейчас посмотрите 24-х часовой курс по базе данных MySQL!

Смотреть курс

Метки: , ,

Комментарии Вконтакте:

Комментарии Facebook:

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

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Я не робот.

Spam Protection by WP-SpamFree