Почему повторяющиеся записи все еще появляются, когда данные инвентаризации данных логически предотвращены?

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

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

Если теперь есть книжный стол, структура аналогична этой

+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+

В нестандартных обстоятельствах могут появиться следующие записи

+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  2 | 人类简史     |
|  3 | 人类简史     |
+----+--------------+

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

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

Давайте кратко поговорим о четырех уровнях изоляции транзакций базы данных, затем воспроизведем описанные выше проблемы и, наконец, поговорим о решениях.

1 4 уровня изоляции для транзакций базы данных

1.1 Незафиксированное чтение

Как следует из названия, когда уровень изоляции транзакций установлен на этот параметр, разные транзакции могут считывать незафиксированные данные в других транзакциях.

Для наглядности я открыл двух клиентов (A и B) и установил для них уровень изоляции «чтение незафиксированных». (и нет глобальной настройки)

команда установки уровня изоляции

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Хорошо, давайте начнем.

Client A

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.01 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  4 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

Когда транзакция в A не закрыта, мы идем в B, чтобы увидеть данные

Client B

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  4 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

B может прочитать незафиксированные данные A, что является так называемым незафиксированным чтением.

Наконец, не забудьте зафиксировать каждую транзакцию.

Client A & Client B

mysql> commit;

1.2 Подтвердить чтение

Ни одна транзакция не может читать зафиксированные данные в других транзакциях.

Из-за нехватки места я не буду размещать здесь инструкцию для установки уровня изоляции, при тестировании определенного уровня изоляции уровень был установлен по умолчанию.

Client A

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  5 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

A не представил, посмотрите данные в B

Client B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

Незафиксированные данные в A не видны в B, как и ожидалось.

Зафиксировать транзакцию в A

Client A

mysql> commit;

Проверьте это в B

Client B

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  5 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

B может видеть данные, представленные в A.

1.3 Повторяемое чтение

Внимательные друзья могут обнаружить проблему, то есть одна и та же транзакция в Б читает одну и ту же таблицу, но полученные результаты противоречивы, в начале только 1, а в конце 2, и если такой проблемы нет, то она повторяемо Читать.

Давайте проверим

Client A

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.01 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  6 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

Client B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

Client A

mysql> commit
Query OK, 0 rows affected (0.00 sec)

Client B

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

как и ожидалось. На транзакцию в B не влияет фиксация транзакции в A. Данные считываются так же, как и при запуске транзакции.В книгах есть только одна часть данных, которую можно прочитать повторно.

Конечно, изменения Б в его собственных делах должны быть видны.

Client B

mysql> insert into books(name) value ('时间简史');
Query OK, 1 row affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  8 | 时间简史     |
+----+--------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

1.4 Сериализация

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

Client A

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

Запись в ту же таблицу в B, когда A не зафиксирован

Client B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

mysql> insert into books(name) value ('人类简史');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Указанный выше тайм-аут возникает из-за того, что операции чтения и записи в разных транзакциях блокируют друг друга.

Если А совершает транзакцию

Client A

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Тогда это может быть записано нормально на B

Client B

mysql> insert into books(name) value ('人类简史');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Точно так же, если транзакция открыта в A и запись вставлена ​​в книги, она не будет зафиксирована.Если транзакция открыта в B и прочитана из таблицы, она также истечет по тайм-ауту. Когда транзакция в A зафиксирована, нет проблем с операцией чтения книг в B.

2 воспроизвести проблему

Так как уровень изоляции транзакций по умолчанию MySQL's Innodb - повторяемое чтение, могут возникнуть проблемы с логикой суждения.Давайте воспроизведем его.

Теперь данные в базе данных выглядят так

+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+

Логика бэкенда такая

try:
	book_name = '人类简史'
	book = get_by_name(book_name)
	if book:
		raise Exception(f'图书 {book_name} 已存在')

	# 新增操作
	# 其它操作

	db.session.commit()
	return {'success': True}
except Exception as e:
	db.session.rollback()
	return {'success': False, 'msg': f'新增图书失败 {e}'}

Когда два пользователя вводят название книги «Краткая история человечества» и отправляют ее, два потока выполняют эту логику одновременно, что эквивалентно двум вышеуказанным клиентам, открывающим транзакции одновременно. Мы используем эти два клиента для иллюстрации. проблема.

Client A

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books where name = '人类简史';
Empty set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.00 sec)

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

Затем сделайте что-то подобное в B

Client B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books where name = '人类简史';
Empty set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.00 sec)

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

Наконец, после того, как A и B будут совершены

Client A & Clinet B

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Есть повторяющиеся записи

+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
| 12 | 人类简史     |
| 13 | 人类简史     |
+----+--------------+

3 Как решить

3.1 Уровень базы данных

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

3.2 Уровень кода

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

Если мы сталкиваемся с запросом с теми же ключевыми параметрами при получении запроса, мы можем напрямую отклонить его и вернуть ответ типа «выполняется операция», который решает проблему из источника.

Идея реализации вышеописанного тоже очень проста, достаточно использовать setnx из redis.

book_name = request.form.get('book_name', '')
if not book_name:
	reutrn json.dumps({'success': False, 'msg': '请填写书名'})

redis_key = f'add_book_{book_name}'
set_res = redis_client.setnx(redis_key, 1)
if not set_res:
	reutrn json.dumps({'success': False, 'msg': '操作进行中'})

add_res = add_book(book_name)  # 添加操作

redis_client.delete(redis_key)
return json.dumps(add_res)

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

4 Резюме

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

Эта статья была впервые опубликована в публичном аккаунте «Little Backend», следите и отвечайте на «1024», знаете ли.

Категории