
1. Perform a modification in auto-commit mode.
Then try to roll it back or abort it.
What happens?

2. Perform another modification within a transaction.
Then again try to roll it back or abort it.  Check the 
state of the modified data before and after the rollback.


Execute a modification (insert, update, delete) and then try to
rollback the transaction. Check the state of the modified data
after the rollback (e.g., a change of the mileage of the vehicle
'194 SUA').

-- T1
begin ;

select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';

update Limousine
   set kilometers = 144444
 where licensePlate = '194 SUA';

rollback;

select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';

3. Investigate the transaction behavior of two concurrent transactions,
which read (T1) and update (T2) the same tuple. To do this, start
two clients (psql sessions) in two shells.
When become changes in concurrent transactions visible? Why?

-- T1                                  -- T2
begin ;
                                       begin ;
-- T1 reads
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
                                       -- T2 writes
				       update Limousine
                                          set kilometers = 144444
                                        where licensePlate = '194 SUA';
                                                     
-- T1 reads again
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';

                                       -- T2 commits
                                        commit;

-- T1 reads
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';

-- T1 commits
commit;

                                       -- reset value
                                       update Limousine
                                          set kilometers = 177397
                                        where licensePlate = '194 SUA';

4. Avoid the non-repeatable read problem.

a)
T1                                     T2
begin;
                                       begin;
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine'
       for update;
--  194 SUA      | 177397.000
                                       update Limousine
                                          set kilometers = 133333
                                        where licensePlate = '194 SUA';
					-- hangs
                                                     
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 177397.000

commit;
                                       -- update has been executed
                                       commit;

                                                     
                                       update Limousine
                                          set kilometers = 177397
                                        where licensePlate = '194 SUA';

b)
T1                                     T2
begin ISOLATION LEVEL REPEATABLE READ;
                                       begin ISOLATION LEVEL REPEATABLE READ;
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 177397.000
                                       update Limousine
                                          set kilometers = 133333
                                        where licensePlate = '194 SUA';
					-- UPDATE 1
                                                     
                                       commit;

select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 177397.000 (!!!)

commit;

select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 133333.000 (!!!)

                                                     
                                       -- reset
				       update Limousine
                                          set kilometers = 177397
                                        where licensePlate = '194 SUA';


5. Run the schedule in exercise 3) with isolation level  "Serializable".

T1                                     T2
begin isolation level serializable;
                                       begin isolation level serializable;
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 177397.000
                                       update Limousine
                                          set kilometers = 133333
                                        where licensePlate = '194 SUA';
					-- UPDATE 1
                                                     
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 177397.000

                                        commit;
					-- COMMIT

select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';

commit;
                                                     
                                       update Limousine
                                          set kilometers = 177397
                                        where licensePlate = '194 SUA';


6. Investigate concurrent updates in two parallel transactions.
   Use the "Read Committed" isolation level.
T1                                     T2
begin isolation level read committed;
                                       begin isolation level read committed;
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';

update Limousine
   set kilometers = kilometers + 2000
 where licensePlate = '194 SUA';

select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 179397.000
                                       update Limousine
                                          set kilometers = kilometers / 1000
                                        where licensePlate = '194 SUA';
					-- hangs
                                                     
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 179397.000

commit;

select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 179397.000

                                       commit;
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 179.397


7. Investigate concurrent updates in two parallel transactions.
   Use the "Serializable" isolation level.
T1                                     T2
begin isolation level serializable;
                                       begin isolation level serializable;
update Limousine
   set kilometers = kilometers + 2000
 where licensePlate = '194 SUA';

select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 179397.000
                                       update Limousine
                                          set kilometers = kilometers / 1000
                                        where licensePlate = '194 SUA';
					-- hangs
                                                     
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 179397.000

commit;
                                       -- ERROR:  could not serialize access due to concurrent update

                                       select licenseplate, kilometers
                                         from Vehicle
                                        where type = 'Limousine';
					--  ERROR:  current transaction is aborted, commands ignored until end of transaction block


                                       rollback;
				       -- commit not possible, see above
select licenseplate, kilometers
  from Vehicle
 where type = 'Limousine';
--  194 SUA      | 179397.000

                                       -- reset
				       update Limousine
                                          set kilometers = 177397
                                        where licensePlate = '194 SUA';

8. The normal workflow check prior to the insertion of a reservation
whether the requested vehicle is actually free. This is to prevent
overlapping reservations of the same vehicle.
Write a query that finds overlapping reservations (of the same vehicle).
Then create a schedule that leads to two overlapping reservations.

select r1.id, r2.id, v.licensePlate,
       r1.interval, r2.interval,
       r1.vehicle, r2.vehicle
  from reservation r1 join
       reservation r2 on r1.vehicle = r2.vehicle join
       Vehicle v on r1.vehicle = v.id
 where overlaps(r1.interval, (r2.interval).begints, (r2.interval).endts)
       and r1.id < r2.id
 order by 1, 2;

T1                                     T2
begin;
                                       begin;
select r.id 
  from reservation r join
       Vehicle v on r.vehicle = v.id
 where v.licensePlate = '194 SUA'
       and overlaps(r.interval, 
                    '2021-11-17 16:00',
                    '2021-11-17 18:00');

                                       select r.id 
                                         from reservation r join
                                              Vehicle v on r.vehicle = v.id
                                        where v.licensePlate = '194 SUA'
                                              and overlaps(r.interval,
                                                           '2021-11-17 17:00',
                                                           '2021-11-17 19:00');

select nextval('reservationSeq');
                                       select nextval('reservationSeq');

-- replace 3204 with  the result of nextval
insert into reservation values(3204, 
                               ('2021-11-17 16:00',
                                '2021-11-17 18:00'),
                               3204,
                               1000,
                               1000);

                                       -- replace 3205 with  the result of nextval
                                       insert into reservation values(3205, 
                                                                      ('2021-11-17 17:00',
								       '2021-11-17 19:00'),
                                                                      3205,
                                                                      1000,
                                                                      1001);

commit;
                                       commit;

select r1.id, r2.id,
       r1.interval, r2.interval,
       r1.vehicle, r2.vehicle
  from reservation r1 join
       reservation r2 on r1.vehicle = r2.vehicle
 where ((r1.interval).begints, (r1.interval).endts) overlaps ((r2.interval).begints, (r2.interval).endts)
       and r1.id < r2.id
       and r1.vehicle = 1000
 order by 1, 2;

-- replace 3204, 3205 with the ids from above
delete 
  from reservation
 where id in (3204, 3205);

9. Which options exist to avoid schedules as in exercise 8? Discuss
advantages and disadvantages.
a. Use explicit locking
Before reservation is inserted, lock table:
lock table Reservation in exclusive mode;
--> will prevent other transactions from inserting any (!) other
    reservation, even for different vehicles and intervals

b.
Create a table that counts reservations per vehicle:

create table resCounter(id integer primary key, cnt integer);
insert into resCounter select id, 0 from vehicle;

Then, as part of the reservation, update the counter for the reserved vehicle.
--> Concurrent transactions will (attempt to) lock the row, and conflicting updates will
    have to wait (or abort). Will prevent overlapping reservations, but also reservations
    for completely disjoint intervals.

c. 
Create a table that has one row per vehicle and (future) one hour slot. An additional column
indicates whether the vehicle is free at the corresponding time slot.
create table vehicleHourSlots(vehicle integer, slotbegin Timestamp, isfree boolean, primary key(vehicle, slotbegin));

As part of the reservation, update the corresponding rows (for the vehicle and interval to be reserved). 
In case of overlapping reservations, transactions will run into lock conflicts. 

