task_four/transactions.sql

-------------Task 1-------------
CREATE TABLE aircrafts_tmp
AS SELECT * FROM aircrafts;
--в первом терминале
BEGIN ISOLATION LEVEL READ COMMITTED;
SHOW transaction_isolation;

UPDATE aircrafts_tmp
SET range = range + 100
WHERE aircraft_code = 'SU9';

SELECT *
FROM aircrafts_tmp
WHERE aircraft_code = 'SU9';

--во втором терминале
BEGIN;

UPDATE aircrafts_tmp
SET range = range + 200
WHERE aircraft_code = 'SU9'; -- команда не закончится, пока действует первая транзакция

commit; -- в первый терминал; update во втором терминале сразу же срабатывает

--во второй
SELECT *
FROM aircrafts_tmp
WHERE aircraft_code = 'SU9';

end;

--------------------------------
--на первом
begin;
SELECT * FROM aircrafts_tmp;

--на втором
begin;
DELETE FROM aircrafts_tmp
WHERE model ~ '^Boe';
SELECT * FROM aircrafts_tmp;
end;

--на первом
SELECT * FROM aircrafts_tmp;
end;


-------------Task 2-------------
--на первом
begin;

SELECT *
FROM aircrafts_tmp
WHERE range < 2000;

UPDATE aircrafts_tmp
SET range = 2100
WHERE aircraft_code = 'CN1';

UPDATE aircrafts_tmp
SET range = 1900
WHERE aircraft_code = 'CR2';

--на втором
BEGIN;

SELECT *
FROM aircrafts_tmp
WHERE range < 2000;
DELETE FROM aircrafts_tmp WHERE range < 2000; -- удалилась строка CN1 | Сессна 208 Караван | 1200

--на первом
rollback;

end;
SELECT * FROM aircrafts_tmp;


-------------Task 4-------------
--на первом
begin;

SELECT *
FROM aircrafts_tmp
WHERE range > 6000;

--на втором
BEGIN;

INSERT INTO aircrafts_tmp (aircraft_code, model, range) VALUES ('NEW', 'New Aircraft Model', 6100);

end;

--на первом
SELECT *
FROM aircrafts_tmp
WHERE range > 6000;

end;

-- aircraft_code |       model        | range
-----------------+--------------------+-------
-- 773           | Боинг 777-300      | 11100
-- 763           | Боинг 767-300      |  7900
-- 319           | Аэробус A319-100   |  6700
-- NEW           | New Aircraft Model |  6100


-------------Task 5-------------

----1. Выборка подмножества-----

--на первом
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM aircrafts_tmp WHERE range > 6000 FOR UPDATE;

--на втором
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM aircrafts_tmp WHERE range > 7000 FOR UPDATE;

--Вторая транзакция не выполняется, потому что ей не даёт первая.

----2. Выборка надмножества-----

--на первом
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM aircrafts_tmp WHERE range > 6000 FOR UPDATE;

--на втором
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM aircrafts_tmp WHERE range > 5000 FOR UPDATE;

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

----3. Пересечение множеств-----

--на первом
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM aircrafts_tmp WHERE range > 6000 FOR UPDATE;

--на втором 
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM aircrafts_tmp WHERE range > 5000 AND range < 8000 FOR UPDATE;

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

---4. Непересечение множеств----

--на первом
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM aircrafts_tmp WHERE range > 6000 FOR UPDATE;

--на втором
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM aircrafts_tmp WHERE range < 2000 FOR UPDATE;

--Вторая транзакция заблокировала строки, 
--которые не пересекаются с строками, заблокированных первой транзакцией.

--Итог: на уровне изоляции Read Committed блокировки, установленные с помощью SELECT ... FOR UPDATE, 
--влияют на возможность других транзакций заблокировать те же строки или их подмножества/надмножества. 
--Конфликты блокировок возникают, когда строки пересекаются или являются надмножеством/подмножеством уже заблокированных строк.