SQL Flashcards
Что такое вложенные транзакции?
Вложенные транзакции могут быть следующих видов:
Псевдо-вложенные транзакции
Вложенная субтранзакция
Вложенная независимая транзакция
Уровень изолированности транзакций
Изолированность же транзакции показывает то, насколько сильно влияют друг на друга параллельно выполняющиеся транзакции.
SQL
Это стандартный язык для доступа к базам данных и управления ими.
Structured Query Language
Виды joins
Вот различные типы JOIN в SQL:
Оператор INNER JOIN включает в результирующую таблицу только те записи, в которых выполняется условие, заданное в ON.
LEFT (OUTER) JOIN:При выполнении запросов с LEFT JOIN возвращаются все строки левой таблицы. Данными из правой таблицы дополняются только те строки левой таблицы, для которых выполняются условия соединения, описанные после оператора ON. Для недостающих данных вместо строк правой таблицы вставляются NULL-значения.
RIGHT (OUTER) JOIN: возвращает все записи из правой таблицы и соответствующие записи из левой таблицы.
FULL (OUTER) JOIN: При запросе FULL (OUTER) JOIN выводятся все записи из объединяемых таблиц. Те записи, у которых запрошенные значения совпадают — выводятся парами, у остальных недостающее значение заменяется на NULL (Python выведет None).
many-to-many
Это самый сложный тип связей: нескольким записям из одной таблицы могут соответствовать несколько записей из другой.
Учителя-предметники. Один учитель может преподавать в нескольких классах, в то же самое время в одном классе может преподавать несколько учителей. Несколько учителей — несколько классов.
Фильмы — режиссёры. Каждый режиссёр может снимать разные фильмы. У одного фильма может быть несколько режиссёров.
В реальной таблице БД нельзя сослаться из одной ячейки сразу на несколько записей.
На уровне базы данных нельзя установить прямую связь M:M между двумя таблицами. Для такой связи необходима промежуточная таблица.
Проблемы параллельного доступа с использованием транзакций(1/4)
Потерянное обновление
Ситуация, когда при одновременном изменении записи разными транзакциями одно из изменений теряется.
UPDATE tbl1 SET f2=f2+20 WHERE f1=1; UPDATE tbl1 SET f2=f2+25 WHERE f1=1;
1. Обе транзакции одновременно читают текущее состояние поля. Физ одновременность не важна, главное чтобы вторая транзакция закончила чтение до того как первая запишет результат
2. Обе транзакции вычисляют поле прибавляя 20 и 25 соответственно.
3. Потом обе транц/закции будут выполнять запись. Поскольку одновременно это сделать невозможно, по итогу одна запишет раньше, а вторая перезапишет эти данные.
И в итоге значение увеличится не на 45, а на 20 или 25, вторая транзакция потеряется
Проблемы параллельного доступа с использованием транзакций(2/4)
“Грязное” чтение
Предположим, имеются две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:
UPDATE tbl1 SET f2=f2+1 WHERE f1=1;
SELECT f2 FROM tbl1 WHERE f1=1;
ROLLBACK WORK;
В транзакции 1 изменяется значение поля f2, а затем в транзакции 2 выбирается значение этого поля. После этого происходит откат транзакции 1. В результате значение, полученное второй транзакцией, будет отличаться от значения, хранимого в базе данных.
Проблемы параллельного доступа с использованием транзакций(3/4)
Неповторяющееся чтение
Предположим, имеются две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:
Транзакция 1 Транзакция 2
SELECT f2 FROM tbl1 WHERE f1=1;
UPDATE tbl1 SET f2=f2+3 WHERE f1=1;
COMMIT;
SELECT f2 FROM tbl1 WHERE f1=1;
В транзакции 2 выбирается значение поля f2, затем в транзакции 1 изменяется значение поля f2. При повторной попытке выбора значения из поля f2 в транзакции 2 будет получен другой результат. Эта ситуация особенно неприемлема, когда данные считываются с целью их частичного изменения и обратной записи в базу данных.
Проблемы параллельного доступа с использованием транзакций(4/4)
Чтение «фантомов»
Предположим, имеется две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:
Транзакция 1 Транзакция 2
SELECT SUM(f2) FROM tbl1;
INSERT INTO tbl1 (f1,f2) VALUES (15,20);
COMMIT;
SELECT SUM(f2) FROM tbl1;
В транзакции 2 выполняется SQL-оператор, использующий все значения поля f2. Затем в транзакции 1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение SQL-оператора в транзакции 2 выдаст другой результат. Такая ситуация называется чтением фантома (фантомным чтением). От неповторяющегося чтения оно отличается тем, что результат повторного обращения к данным изменился не из-за изменения/удаления самих этих данных, а из-за появления новых (фантомных) данных.
Уровни изоляции (версия 2)
Read uncommitted, Read committed, Repeatable read, Serializable. Первый из них является самым слабым, последний — самым сильным, каждый последующий включает в себя все предыдущие.
Уровни изоляции
Read uncommitted (чтение незафиксированных данных)
Уровень, имеющий самую плохую согласованность данных, но самую высокую скорость выполнения транзакций. Название уровня говорит само за себя — каждая транзакция видит незафиксированные изменения другой транзакции (феномен грязного чтения).
- Шаг 1. Начинаем 2 параллельные транзакции.
- Шаг 2. Смотрим какая информация имеется у нас в начале.
- Шаг 3. Теперь выполняем операции INSERT, DELETE, UPDATE в Т1, и посмотрим, что теперь видит другая транзакция.
Т2 видит данные другой транзакции, которые еще не были зафиксированы. - Шаг 4. И Т2 может получить какие-то данные.
- Шаг 5. При откате изменений Т1, данные полученные Т2 окажутся ошибочными.
Уровни изоляции
Read committed (чтение фиксированных данных)
Для этого уровня параллельно исполняющиеся транзакции видят только зафиксированные изменения из других транзакций. Таким образом, данный уровень обеспечивает защиту от грязного чтения.
- Шаг 1. Начинаем 2 параллельные транзакции.
- Шаг 2. Смотрим какая информация имеется у нас в начале.
- Шаг 3. Также выполним 3 простейшие операции с таблицей accounts (Т1) и сделаем полную выборку из этих таблиц в обеих транзакциях.
И увидим, что феномен грязного чтения в Т2 отсутствует. - Шаг 4. Зафиксируем изменения Т1 и проверим, что теперь видит Т2.
Теперь Т2 видит все, что сделала Т1. Это так называемые феномен неповторяющегося чтения, когда мы видим обновленные и удаленные строки (UPDATE, DELETE), и феномен чтения фантомов, когда мы видим добавленные записи (INSERT).
Уровни изоляции
Repeatable read (повторяющееся чтение)
Уровень, позволяющий предотвратить феномен неповторяющегося чтения. Т.е. мы не видим в исполняющейся транзакции измененные и удаленные записи другой транзакцией. Но все еще видим вставленные записи из другой транзакции. Чтение фантомов никуда не уходит.
Снова повторяем Шаг 1 и Шаг 2.
* Шаг 3. В Т1 выполняем запросы INSERT, UPDATE и DELETE. После, в Т2 пытаемся обновить ту же самую строку, которую обновили в Т1.
И получаем lock: T2 будет ждать, пока T1 не зафиксирует изменения или не откатится.
* Шаг 4. Зафиксируем изменения, которые сделала Т1. И прочитаем снова данные из таблицы accounts в Т2.
Как видно, феноменов неповторяющегося чтения и чтения фантомов не наблюдается. Как же так, ведь по умолчанию, repeatable read позволяет нам предотвратить только феномен неповторяющегося чтения?
На самом деле в MySQL отсутствует эффект чтения фантомов для уровня repeatable read. И в PostgreSQL от него тоже избавились для этого уровня. Хотя в классическом представлении этого уровня, мы должны наблюдать этот эффект.
Уровни изоляции
Serializable (упорядочиваемость)
Уровень, при котором транзакции ведут себя как будто ничего более не существует, никакого влияния друг на друга нет. В классическом представлении этот уровень избавляет от эффекта чтения фантомов.
Шаг 1. Начинаем транзакции.
Шаг 2. Т2 читаем таблицу accounts, затем Т1 пытаемся обновить данные прочитанные Т2.
Получаем lock: мы не можем изменить данные в одной транзакции, прочитанные в другой.
Шаг 3. И INSERT и DELETE ведет нас к lock’у в Т1.
Пока Т2 не завершит свою работу, мы не сможем работать с данными, которые она прочитала. Мы получаем максимальную согласованность данных, никакие лишние данные не зафиксируются. Цена за это медленная скорость транзакций из-за частых lock’ов поэтому при плохой архитектуре приложения это может сыграть с Вами злую шутку.
Транзакция
это N (N≥1) запросов к БД, которые выполнятся успешно все вместе или не выполнятся вовсе.
