create table t(a number, b varchar2(10), primary key(a));

/* Exercise 1.3 */
t2: insert into t values (6, 'six of T2');
t1: insert into t values (6, '6 of T1');

/* When we try to insert the tuple in terminal 1, we will wait. This happens because Oracle uses row-level locks to control concurrency.
Therefore, we have to wait for t2 to commit or rollback in order to release the lock, in order to t1 to proceed */

/* commit in t2*/
/* do again but now rollback in t2*/

/* Without primary key the wait does not occur since there is no conflict between the tuples */


/* Exercise 1.4 */

t1: insert into t values (7, 'seven');
t1: commit;
t1: delete from t;
t2: delete from t;

/* Terminal 2 waits */
/
/* A transaction acquires a table lock when a table is modified in the following DML 
statements: INSERT, UPDATE, DELETE, SELECT */


/* Exercise 1.5 */

t2: update t set b ='menos um' where a=1
t1: update t set b ='menos dois' where a=2
t2: update t set b ='dois' where a=2
t1: update t set b ='um' where a=1

/* The victim is terminal t2 since it was the first transaction to execute, i.e. the victim is the oldest transaction in execution */

/* Exercise 1.6 */

create table t2(a1 number, a2 number,a3 number);

delete from t2;
insert into t2 values (1,2,3);
insert into t2 values (4,5,6);
select * from t2;
commit;

t1: update t2 set a2 = 100 where a3 = 3;
t2: update t2 set a1 = 200 where a3 = 6;
t1: update t2 set a2 = 100 where a3 = 6;
t2: update t2 set a1 = 200 where a3 = 3;

/* Oracle detects the deadlock and terminates the execution of transaction 1; 
 */ 

drop table a;
drop table b;
create table a ( x int );
create table b ( x int );
commit;
 
select * from a;
select * from b;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* Exercise 2.1 */

t1: 											t2:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;		SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;		SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
insert into t values (3,'c');
													insert into t values (4,'d');
insert into b select count(*) from t;								
													commit;												
insert into a select count(*) from t;
commit;


/* Exercise 2.2 */

create table t_log ( x int )
create table p1 ( x int );

create or replace PROCEDURE insertp1
IS
	v number;
BEGIN
  FOR v IN 1..10000 LOOP
      insert into p1 values (v);
	  commit;
  END LOOP;
END;

create or replace
PROCEDURE countp1
IS
	v number;
BEGIN
  FOR v IN 1..20 LOOP
      insert into t_log select count(*) from p1;
  END LOOP;
END;


t1:
begin
DBMS_LOCK.sleep(1);
insertp1();
end;

t2:
begin
countp1();
end;


/* Exercise 2.5 */
/* Both transactions in serializable mode*/

create table t(a number, b varchar2(10), primary key(a));

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

t1: select * from t;

t2: select * from t;

t1: update t set b ='a' where a=1;

t1: commit;

t2: update t set b ='c' where a=1;

/* Cannot serialize. Uses an optimistic model */


t1: SELECT * FROM t where a=1 FOR UPDATE of t.b;

t2: SELECT * FROM t where a=1 FOR UPDATE of t.b; 

t1: update t set b ='a' where a=1;

t1: commit;

t2: update t set b ='c' where a=1;

/* Exercice 2.6 */

/* Both transactions in serializable mode*/

t1: select count(*) from t;

t2: select count(*) from t;

t1: insert into t values (1,'a');

t2: insert into t values (2,'a');

t1: select count(*) from t;

t2: select count(*) from t;

t1: commit;

t2: commit;


select count(*) from t;
insert into t values (1,'a');
								insert into t values (2,'a');
select count(*) from t;
								select count(*) from t;
commit;								
								commit;
								
								
/* Savepoints and recovery - exercise 3.1 */

create table t2(a1 number, a2 number,a3 number);
delete from t2;
insert into t2 values (1,2,3);
insert into t2 values (4,5,6);
select * from t2;
commit;


insert into t values (1,'a');
savepoint bla;
													insert into t values (3, 'c');
													commit;
insert into t values (2,'b');
select * from t;
rollback to bla;

delete from t;
commit;


/* Exercise 4.1 */

t1: create table t(a number, b varchar2(10), primary key(a));
t1: LOCK TABLE t IN EXCLUSIVE MODE;
t2: LOCK TABLE t IN EXCLUSIVE MODE;

/* T2 waits for the release of the exclusive lock by t1 */

/* When t1 finishes t1 the lock is released and t2 obtains the exclusive lock for table t*/

/* Exercise 4.2 */

t1: LOCK TABLE t IN EXCLUSIVE MODE NOWAIT;
t2: LOCK TABLE t IN EXCLUSIVE MODE NOWAIT;

/* Exercise 4.3 */

t1: LOCK TABLE t IN EXCLUSIVE MODE NOWAIT;
t2: select * from t;
t2: insert into t values (4,'letra a');

/* Despite the lock acquired by t1, it is possible in t2 to access the table for querying (select in Oracle does not acquire locks).
However, as expected, it is not possible to insert new tuples */


/* Exercise 4.4 */

t1: insert into t values (5,'letra b');
t2: LOCK TABLE t IN EXCLUSIVE MODE NOWAIT;

/* 

In terminal 1, the insert command acquires a row-exclusive lock;
In terminal 2, when we try to acquire an exclusive lock for table t, we obtain an error since transaction t1 has a row-exclusive lock

*/

/* Exercise 4.5 */

/* Read the manual to see the compatibility matrix for the several Oracle locking modes */

/* Exercise 4.6 */

t1: SELECT * FROM t where t.a = 1 FOR UPDATE OF t.b;
t2: update t set b = 'bla' where a = 2;
t2: update t set b = 'blacod1' where a = 1;

/* It is possible to change the tuple with code 2, but it is not possible to update the tuple with code 1. 

In transaction t1 a row-shared lock is obtained for each selected row.
The last update tries to acquire a row-exclusive lock which is not possible since we already have a shared-row lock obtained by t1.
*/