UPDATE S SET nomer='S9' WHERE nomer='S2';
UPDATE SP SET nomer='S9' WHERE nomer='S2';
В одном операторе невозможно обновить две таблицы!
После первого оператора БД стала противоречивой. Теперь в ней есть поставки у которой нет поставщика. В этом противоречивом состоянии БД будет находиться до завершения второго оператора. Изменения порядка операторов принципиально ничего не меняет. Поэтому, с точки зрения целостности БД важно обеспечить завершение обоих операторов, а не одного из них (см. дальше).
DELETE
FROM <имя таблицы>
[ WHERE <условие> ];
Этот оператор удаляет все строки из таблицы, которые удовлетворяют <условию>.
Две формы оператора INSERT
а)
INSERT
INTO <имя таблицы> [ ( <имя столбца>{, <имя столбца>} ) ]
VALUES ( <константа>{, <константа>} );
б)
INSERT
INTO <имя таблицы> [ ( <имя столбца>{, <имя столбца>} ) ]
<подзапрос>;
В форме (а) в таблицу вставляется строка из констант, перечисленных в части VALUES. Причем, i-ая константа соответствует i-му столбцу в части INTO.
В форме (б) сначала вычисляется <подзапрос> (результат которого - таблица) и результат подзапроса вставляется в таблицу <имя таблицы>.
Пример:
Добавить в таблицу P деталь P7: город Москва, вес 2, название и цвет пока не известны.
INSERT INTO P (nomer, gorod, ves) VALUES ('P7', 'Москва', 2);
В таблице P появляется новая строка. Порядок столбцов в части INTO не обязательно должен совпадать с порядком столбцов при создании таблицы.
Вставка единственной строки с опущенными именами столбцов
Добавить в таблицу P деталь P8: название "звездочка", цвет розовый, вес 14, город Берлин.
INSERT INTO P VALUES ('P8', 'звездочка', 'розовый', 14, 'Берлин');
Если имена столбцов не заданы, то предполагается перечисление всех столбцов таблицы в порядке их перечисления в операторе CREATE.
Вставка множества строк
Для каждой поставляемой детали получить ее номер и объем ее поставки, сохранить результат в отдельной таблице.
CREATE TABLE VREMEN
(nomer CHAR(6),
sum_postavki INTEGER)
INSERT INTO VREMEN SELECT nomer_p, SUM(kol) FROM SPGROUP BY nomer_p
Тема 12. Управление транзакциями
Вернемся к примеру в котором заменяется номер поставщика S2 на S9. Обобщим этот пример. Допустим, есть некий язык программирования, внутри которого можно выполнять операторы SQL. Напишем процедуру, которая будет менять номер поставщика с SX на SY.
Trans: proc(SX, SY);
ON ERROR: begin ROLLBACK; RETURN end;
EXEC UPDATE S SET nomer=SY WHERE nomer=SX;
EXEC UPDATE SP SET nomer_s=SY WHERE nomer_s=SX;
EXEC COMMIT;
end;
Эта процедура воспринимается ее пользователем как неделимая (атомарная операция). На самом деле эта процедура состоит из 2-х операторов SQL. И между этими операторами нарушается согласованность БД (есть поставки у которых нет поставщика). Чтобы сохранить согласованность БД нужно уметь выполнять последовательность операторов SQL как неделимую операцию.
Транзакция - это последовательность операторов SQL, которая либо выполняется целиком, либо не выполняется совсем.
Предполагается при этом, что транзакция переведет некоторое согласованное состояние БД в другое согласованное состояние, но не гарантирует сохранение согласованности во все промежуточные моменты времени.
- Последовательность может состоять из одного оператора SQL.
- Транзакцию создает программист.
СУБД гарантирует, что если транзакция проводила некоторые изменения в БД (UPDATE, INSERT, DELETE) и затем, по какой-либо причине, произошла ошибка до нормального завершения транзакции, то эти изменения будут отменены.
Таким образом, транзакция либо полностью исполняется, либо полностью отменяется, как будто бы она не исполнялась вообще.
Вопрос: Как СУБД узнает когда начинается и когда заканчивается транзакция?
Транзакция заканчивается, чаще всего, как и в этом примере, с помощью операторов COMMIT (принять, подтвердить) и ROLLBACK (отменить, откатиться).
Оператор COMMIT сообщает СУБД об успешном завершении транзакции, о том, что БД вновь должна находиться в согласованном состоянии и все изменения в БД следует сделать постоянными.
Напротив, оператор ROLLBACK сообщает СУБД о неудачном завершении транзакции, о том, что БД находится, возможно, в противоречивом состоянии и что все сделанные внутри транзакции изменения (UPDATE, INSERT, DELETE) следует отменить.
Даже если во время исполнения транзакции сломается компьютер, то тот оператор ROLLBACK, который должен был выполняться по ошибке, будет все равно выполнен при рестарте СУБД.
Как отменяются изменения?
С помощью журнала, в котором записываются все операции изменения БД.
В частности, значения таблиц до и после изменения.
Транзакция начинается:
- первым оператором, после соединения с БД;
- первым оператором, после конца предыдущей транзакции.
Транзакция заканчивается:
- оператором COMMIT;
- оператором ROLLBACK;
- отсоединением от БД, причем предполагается ROLLBACK.
12.1. Три проблемы, связанные с параллельностью транзакций
Всякая СУБД, которая позволяет любому количеству пользователей одновременно пользоваться данными в одной и той же БД, должна иметь механизм, обеспечивающий независимость пользователей друг от друга, по крайней мере, кажущуюся.
Такой механизм нужен чтобы избежать трех случаев ошибочного исполнения транзакции, когда транзакция правильная сама по себе, может приводить к ошибочному результату из-за вмешательства со стороны некоторой другой транзакции, тоже правильной.
Таким образом, будем обсуждать чередование операторов SQL, по крайней мере, в двух параллельных и правильных транзакциях, которое может привести к ошибочному в целом результату.
-------------------------+-----------------------+-----------------
Время TA ! Значения ! TB
-------------------------+-----------------------+-----------------
t0 start ! !
t1 Сч1=SELECT R ! Сч1(TA)=50, R=50 !
t2 ! ! start
t3 ! Сч1(TB)=50, R=50 ! Сч1=SELECT R
t4 UPDATE R=Сч1+15 ! R=65 !
t5 COMMIT ! !
t6 ! R=40 ! UPDATE R=Сч1-10
t7 ! ! COMMIT
-------------------------+-----------------------+-----------------
TA выбирает строку R в t1, TB - в t3. TA обновляет R в t4, исходя из значений "увиденных" в t1, а TB обновляет эту же строку в t6, исходя из значений "увиденных" в t3. Очевидно, что в итоге в R остается лишь значение записанное TB, которая перекрывает значение записанное TA не глядя на него.
Возникает в том случае, если одной транзакции позволяется выбрать данные из строки, которая уже была обновлена другой транзакцией, но не успела подтвердить обновление.
------------------------+-----------------------+-----------------
Время TA ! Значения ! TB
------------------------+-----------------------+-----------------
t0 ! R=50 ! start
t1 ! R=20 ! UPDATE R=20
t2 start ! !
t3 Сч1=SELECT R ! Сч1(TA)=20 !
t4 ! R=50, Сч1(TA)=20 ! ROLLBACK
------------------------+------------------------+-----------------
TA "видит" неподтвержденное изменение, которое позже отменяется TB.
Следовательно, TA работает при ошибочном предположении, что R имеет значение 20, а на самом деле оно 50.
-------------------------+-----------------------+-----------------
Время TA ! Значения ! TB
-------------------------+-----------------------+-----------------
t0 start ! R1=40, R2=50, R3=30 !
t1 Сч1=SELECT R1 ! Сч1(TA)=40,Сум(TA)=40 !
t2 Сч2=SELECT R2 ! Сч2(TA)=50,Сум(TA)=90 !
t3 ! ! start
t4 ! Сч3(TB)=30 ! Сч3=SELECT R3
t5 ! R3=20 ! UPDATE R3=Сч3-10
t6 ! Сч1(TB)=40 ! Сч1=SELECT R1
t7 ! R1=50 ! UPDATE R1=Сч1+10
t8 ! ! COMMIT
t9 Сч3=SELECT R3 ! Сч3(TA)=20,Сум(TA)=110!
------------------------+------------------------+-----------------
Сум(TA) должна = 120
Здесь TA суммирует остатки на счетах, а TB переносит сумму 10 со счета 3 на счет 1. В результате параллельного выполнения TA получает неверную сумму.
Различия между этой и предыдущей проблемой в том, что здесь TA не зависит от неподтвержденных изменений, поскольку TB сделало все изменения постоянными еще до того, как TA "увидела" счет 3.
Все три случая ошибочного выполнения транзакций возникают только тогда, когда хотя бы одна транзакция изменяет данные в БД. Конфликтов не бывает, когда все транзакции только выбирают (читают) данные.
12.2. Решение проблем параллелизма
Существует два основных подхода к управлению параллельными транзакциями:
- основанный на захвате объекта БД (блокировке);
- основанный на метках времени (многоверсионных объектах).
Главная идея захвата проста: если для транзакции нужно, чтобы некоторый объект БД (обычно строка таблицы) не изменялся до ее завершения, то она устанавливает захват этого объекта. Захват заключается в том, что объект изолируется от других транзакций.
Различают два вида режимов захвата:
1) совместный режим (режим С) и
2) монопольный режим (режим М).
Ради упрощения будем обсуждать захваты только одной строки в таблице.
Алгоритм захватов
1. Если TA устанавливает М-захват строки R, то запрос из TB на любого типа захват строки R приведет к тому, что TB перейдет в состояние блокировки.
TB будет находиться в этом состоянии, пока TA не снимет захват.
2. Если TA устанавливает С-захват на строку R, то:
а) запрос из TB на М-захват строки R заставит TB перейти в состояние блокировки и TB будет заблокирована, пока TA не снимет свой захват.
б) запрос из TB на С-захват строки R будет удовлетворен, то есть TB также будет удерживать С-захват строки R.
Обсуждение п.2:
- в отсутствии захватов строки R будет удовлетворен запрос на захват любого вида этой строки;
- во время М-захвата строки R в запросе любого вида на захват R будет отказано;
- во время С-захвата строки R будут удовлетворяться только С-запросы.
3. Запросы транзакций на захват строки всегда являются неявными. Когда транзакция исполняет оператор SELECT она автоматически устанавливает С-захват. Когда транзакция изменяет, добавляет, удаляет строку, она автоматически устанавливает М-захват. Если же транзакция получила строку R в С-захват, а потом стала изменять строку R, то транзакция автоматически повышает вид захвата с С на М.