In
this practice we will look at the different levels of locking and how we can
manage them.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Lock
the employees table in exclusive mode. Try
to perform a select…for update on the table.
|
SQL>
lock table employees in exclusive mode; Table(s)
Locked. … SQL>
select * from employees for update; |
2.
Update
the salary of employee number 278 to half of its’ current value.
|
SQL>
update employees
2 set salary =
salary/2
3 where employee_id
= 278; 1
row updated. |
3.
Open
a second session and try to perform the same operation.
|
Notice
the operation hangs SQL>
update hr.employees
2 set salary =
salary*2
3 where employee_id
= 278; |
4.
Return
to the first session and commit the transaction.
|
SQL>
update employees
2 set salary =
salary/2
3 where employee_id
= 278; 1
row updated. SQL>
commit; Commit
complete. SQL> |
5.
Return
to the second session and notice the transaction has completed.
|
SQL>
update hr.employees
2 set salary =
salary*2
3 where employee_id
= 278; 1
row updated. SQL> |
This
completes this practice.
In
this practice we will look at how to detect and resolve locking issues.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Connect
as user hr and perform a select…for update on the employees table.
|
SQL>
connect hr/hr Connected. SQL>
select * from employees 2
for
update; … |
2.
Assuming
that this transaction is blocking, determine which user is locking the table
employees.
|
SQL>
l
1 select owner, object_name
2 from dba_objects,
v$lock
3 where object_id=id1
4* and type='TM' SQL>
/ OWNER
OBJECT_NAME ------------------------------
-------------------- HR
EMPLOYEES SQL> |
3.
Determine
the user’s sid and serial# and kill the user’s
session.
|
SQL>
select sid, serial#, username
2 from v$session
3 where type='USER';
SID SERIAL#
USERNAME ----------
---------- ------------------------------
7
1
11
17 HR
12
133 SYSTEM SQL>
alter system kill session '11,17'; System
altered. |
This completes this practice