Useful shortcuts for vi editor

Thursday, 18 May 2017

e.g. row lock (tx) and table lock (tm)

e.g.
row lock (tx)
In -A- database:
SQL>
select * from employees where employee_id = 100 FOR UPDATE;

to see the error;
In -B- database:
SQL>
update employees@alper_database
set salary = 10000
where employee_id = 100;

error -> ORA-02049: timeout: distributed transaction waiting for lock

e.g.
table lock (tm)
In -A- database:
SQL>
LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT;

to see the error;
In -B- database:
SQL>
update employees@alper_database
set salary = 2500
where employee_id = 105;

--or
In -B- database:
LOCK TABLE employees@alper_database IN SHARE MODE;

to see the error;
In -A- database:
SQL>
update employees
set salary = 2500
where employee_id = 105;

same error for both methods -> ORA-02049: timeout: distributed transaction waiting for lock

ref: https://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502