Что такое блокирование в MySQL и когда вы его используете?

Ответ 1

У нас есть совлокальный банковский счет с балансом $200

Я иду в банкомат и вставляю свою карточку в машину, машина проверяет, что у меня есть баланс $200

Между тем, вы заходите в банк и просите 50 долларов, кассир поднимает вашу учетную запись и подтверждает, что у вас есть деньги.

Я прошу снять 200 долларов, машина подсчитывает мои деньги, дает мне 200 долларов и устанавливает мой баланс в $0

Кассир подсчитывает ваши деньги и дает вам 50 долларов США, затем система обновляет остаток на счете в размере 150 долларов США (от 200 до 50 долларов США).

Итак, теперь у нас есть 250 долларов наличными и 150 долларов на счет. Прибыль в 200 долларов.

В базе данных должны использоваться блокировки для предотвращения одновременной транзакции.

Проблема в том, что если вы обрабатываете каждую транзакцию таким образом, тогда мы потеряем concurrency, и производительность будет страдать, поэтому в зависимости от сценария используются разные transaction isolation levels, например, вам может быть безразлично, что кто-то может изменить данные, которые были прочитаны в транзакции.

http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

Вы должны изучить их и понять сценарии, в которых они применимы.

Ответ 2

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

Представьте себе следующую ситуацию без использования блокировок: Джон открывает свой экран (он не знает, что он использует базу данных, он только конечный пользователь, который смотрит на симпатичный экран), изменяет некоторые данные, а затем показывает "Сохранить". Скажем, Джон откроет экран в 9:30, а затем сохранит данные в 9:32.

Однако Мэри открыла точно такой же экран и ту же запись в 9:29. В то время она увидела те же данные, что и Джон в 9:30. Затем она обновляет запись и нажимает "Сохранить" в 9:31.

Какие данные были сохранены? Джон или Мэри?

Мэри счастливо продолжает работать над другими записями, и когда она вернется позже, чтобы снова открыть запись, она видит, что ее изменения были потеряны, и вместо этого она видит изменения Джона.

Имейте в виду, что блокировка должна использоваться с умом, чтобы предотвратить неожиданные побочные эффекты. Например, скажем, что ваша программа блокирует запись каждый раз, когда кто-то открывает ее, она вносит изменения. Что произойдет, если Джон закроет запись и покинет экран своего сеанса, чтобы пообедать, или он теряет связь? Блокировка может оставаться там, заблокирована и неизменна, в течение длительного времени, запрещая всем другим изменять (или даже смотреть) эту запись. Другим соображением может быть производительность, поскольку время для базы данных для блокировки и разблокировки записей может стать заметным для большого количества транзакций.

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

Ответ 3

Несколько дней назад я ответил на вопрос о SO и привел пример, демонстрирующий ситуацию, когда блокировка позволяет нескольким пользователям одновременно вставлять строки в таблицу с приращением id без использования AUTO_INCREMENT.

В качестве примера рассмотрим следующую схему:

CREATE TABLE demo_table (id int) ENGINE=INNODB;

-- // Add few rows
INSERT INTO demo_table VALUES (1), (2), (3);

Тогда мы можем сделать следующее:

START TRANSACTION;

-- // Get the MAX(id) so that we increment it by one
SELECT @x := MAX(id) FROM your_table FOR UPDATE;

+---------------+
| @x := MAX(id) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

Синтаксис FOR UPDATE - это то, что фактически блокирует строки, прочитанные этим запросом.

Без совершения транзакции мы запускаем другой отдельный сеанс (имитирующий одновременный пользователь) и делаем то же самое:

START TRANSACTION;

-- // Get the MAX(id) as well
SELECT MAX(id) FROM demo_table FOR UPDATE;

База данных будет ждать, пока блокировка, установленная в предыдущем сеансе, не будет выпущена до запуска этого запроса.

Поэтому, переключаясь на предыдущий сеанс, мы можем вставить новую строку и зафиксировать транзакцию:

-- // Insert a new row with id = MAX(id) + 1
INSERT INTO demo_table VALUES (@x + 1);

COMMIT;

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

+---------+
| MAX(id) |
+---------+
|       4 |
+---------+
1 row in set (8.19 sec)

Обратите внимание, что без блокировки второй сеанс будет возвращен немедленно, но с 3 как MAX(id) вместо 4. Если обе сессии должны были вставить строку с id из MAX(id) + 1, обе вставляли бы id = 4. Вы можете смоделировать один и тот же тест без бита FOR UPDATE, чтобы увидеть, как это обрабатывается без блокировок.