Manage Oracle Locks
Oracle has different types of locks. Locking means oracle has
different type of mechanism to protect oracle objects or oracle data. Its means
that if multiple users working in data base and they can establish different
type of locks on different tables or different type of objects. If user is
working on record1 then he has the lock on record1 till either his transaction
rollback or commit.
There is row level locking and table locking. In during row
level locking there is also table level locking. For example: if a query for
updating records fires and it takes 30 minutes to update records and during
this 30 minutes if any other user want to perform drop, truncate or alter table
command on same table then it will not perform and its table level locking. In
select there is not lock. In during select, table level lock exists no one can
alter the table structure, at this time DDL statement is not permitted.
Locks are automatically applied and removed as database requires
it, Its not on DBA head to release or apply locks. DBA should check that there
is no excessive lock. Lock creates a block the it become a problem.
Locks
. Prevent
multiple sessions from changing the same data at the same time.
. Are automatically obtained for a given statement.
Locking Mechanism
. row level locks
for inserts, updates and deletes
. No locks required
for queries
. Locks will be
release when Users COMMIT or ROLLBACK
When we run the query then there is no lock on the rows but
its on the table
Data Concurrency
Different transactions
can be updating different rows within the same table without interfering with one
another.
Transaction 1
|
Update hr.employees set
salary = salary+100 Where employee_id =100;
|
Transaction2
|
Update hr.employees set
salary = salary + 100 Where employee_id = 101;
|
Transaction3
|
Update hr.employees set
salary = salary + 100 Where employee_id = 102;
|
Above transactions are different and they will not lock others
row because all updating different rows and they apply lock on its own row. All
records are different and working on different rows so they will lock
indivisual rows and since the records are different so none of them has to
wait. This is called concurrency. Concurrent user can work on same table at
same time.
If you want to check what locks are there then use the
following query: ( for sessions)
Set linesize 150;
Column username format A20
Column object_name format A30
SELECT
C.USERNAME, C.SID,
C.SERIAL#, A.OBJECT_NAME, B.TYPE
FROM
DBA_OBJECTS
A, V$LOCK B, V$SESSION C
WHERE
A.OBJECT_ID(+) = B.ID1
AND B.SID = C.SID
AND C.USERNAME IS NOT NULL
AND
A.OWNER != 'SYS'
USERNAME
SID SERIAL# OBJECT_NAME TY
-------------------- ---------- ----------
------------------------------ -------------- -------------- --
REPORT
########## 5177
SDO_GEOR_DDL__TABLE$$ TO
REPORT
########## 6235
SDO_GEOR_DDL__TABLE$$ TO
REPORT
########## 6628
SDO_GEOR_DDL__TABLE$$ TO
INDIAMART ##########
33465 SDO_GEOR_DDL__TABLE$$
TO
INDIAMART ########## 8203 GLUSR_COUNT TM
INDIAMART ########## 8203 GLUSR_SUS_DUPLICATE_081013 TM
REPORT
########## 2579
CUSTPRO_HIST_DTWISE_COUNT TM
Where TM is table mode lock.
If we want to execute Transaction2 from other terminal where
before this transaction not done then
It will wait until the first query is not rolled back or
commit.
There Is another query to see the current locks:( for
blocking sessions)
Select
sid, serial#, username
from
v$session
where
sid in (select blocking_session from v$session) ;
alter system kill session ‘144,8982’; #where 144 is sid and
8982 is serial#.
DML Locks
Transaction1 Transaction2
Update employees
set salary = slary*1.1 where employee_id = 107;
1 row updated.
|
Update employees
set salary = slary*1.1 where employee_id = 107;
|
Each DML transaction
must acquire two locks:
. EXCLUSIVE (TX) row
lock on the row or rows being updated
. ROW EXCLUSIVE (TM)
table-level lock on the table containing
the rows
DDL LOCKS
Transaction1 Transaction1
Update employees
set salary = salary * 1.1 where
employee_id = 107;
|
Alter table
employees drop column comm.;
|
We can not drop
column using alter a table but can add column if the table has some uncommitted
Transactions
By default, a DDL
lock request won’t wait for a DML lock. Use below command to set ddl lock
timeout to 30 seconds.
ALTER SESSION SET
ddl_lock_timeout = 30;
Dba work to identify
locks which is taken very long time and find user and tell him that please
commit or rollback the transaction otherwise have to kill session.
When Lock Conflicts Can
Happen
. Uncommited changes
. Long-running transactions
. Unnecessarily high locking levels
Monitoring the locks
Set linesize 150
COLUMN USERNAME FORMAT A20
COLUMN OBJECT_NAME FORMAT A30
SELECT C.USERNAME, C.SID, C.SERIAL#, A.OBJECT_NAME, B.TYPE
FROM DBA_OBJECTS A, V$LOCK B, V$SESSION C
WHERE
A.OBJECT_ID(+) = B.ID1
AND S.SID = C.SID
AND C.USERNAME IS NOT NULL
AND A.OWNER != ‘SYS’
ORDER BY B.SID;
|
SELECT SID, SERIAL#, USERNAME
FROM V$SESSION
WHERE
SID IN (SELECT BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION
IS NOT NULL)
|
ALTER SYSTEM KILL SESSION ‘144,8982’;
|
DEADLOCKS
It is a very
important concept.
. Deadlocks arises when two or more sessions wait for the
data that has been locked by the other.
. Oracle database automatically detects deadlocks and terminates the statement.
Transaction 1
|
Transaction 2
|
|
Update employees set salary = salary * 1.1 where employee_id
= 100;
|
9:00
|
Update employees set salary = salary * 1.1 where employee_id
= 200;
|
Update employees set salary = salary * 1.1 where employee_id
= 200;
|
9:15
|
Update employees set salary = salary * 1.1 where employee_id
= 100;
|
ORA-0060: Deadlock detected while waiting for resource
|
9:16
|
In deadlock first session is blocking second session and
second is blocking first session and by this each other is blocking other
session.
So, deadlock is a situation in which two different sessions or
two different transactions block each other.
But when this happen then this is a situation of infinite
loop. In this case oracle is very intelligent and oracle know the deadlock
situation then it kills one of the session and give the deadlock detected error.
So, when oracle get the deadlock situation then oracle automatically
resolves.
Solution is update the record and commit
What is undo?
Undo segments
contain “before image” of changed data
Undo records are used for
-
Rollback of DML transactions
-
Read consistency
·
Users see
the current committed data in database. If another user changes the data and is
not committed, the change is not visible to other users.
-
Instance Recovery by SMON process
-
FLASHBACK operation
Using FLASHBACK we can find record
which is deleted and commited. FLASHBACK is only possible when before image stored
in UNDO segment.
When create a database automatic management in there.
UNDO Management
·
Two Undo managements methods were supported
·
In 9i/10g/11g Automatic Undo Management is
default
·
Undo_management=auto/manual
·
Manual mode was used till Oracle 8i and is now
obsolete.
UNDO PARAMETERS
·
Init.ora
parameter for undo management
SQL> show parameter undo_
NAME TYPE
VALUE
------------------------------------
----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
undo_management is set as AUTO and undo_management is static
variabal and it can not be changed while database is running.
Other two parameters undo_retention and undo_tablespace are
dynamic parameter and can change be changed in between running database.
Undo retention is How long you want to save or keep the “before
image” and by default it is 900 seconds i.e. 15 minutes.
Undo tablespace is Where undo data can store.
We can have multiple undo tablespaces but at a time only one
tablespace can be used and it can create as any other tablespace.
Multiple undo tablespaces may require for different
processes.
For example if we want to use every month’s Monday use an
undo tablespace of size 100GB and other days require 10 GB undo tablespace then
we can create 2 undo_tablespaces as per requirement.
UNDO
PARAMETERS
1.
UNDO_MANAGEMENT
· AUTO for
AUM and MANUAL for rollback segments
· Static parameter
2.
UNDO_TABLESPACE = Undo tablespace
name
· Dynamic
parameter
· DBA can
change to new tablespace with active
transactions on old tablespace
>> Old transaction will use old undo tablespace
>> New Transactions use new undo tablespace
3.
UNDO_RETENTION = 3600
· Number of
seconds to keep UNDO
· Dynamic
· Default =
900 seconds
|
CREATING UNDO TABLESPACE
>
Use the UNDO clause on CREATE TABLESPACE to
create UNDO Tablespace.
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘/u01/app/oracle/undotbs02.dbf’ SIZE 100M; |
Changing and Dropping Undo Tablespace
>
Create a new Tablespace and use Alter system to
changeUndo Tablespace.
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘/u01/app/oracle/undotbs02.dbf’
SIZE 100M;
ALTER SYSTEM
SET UNDO_TABLESPACE = UNDOTBS2
SCOPE=BOTH;
DROP TABLESPACE undotbs1;
# No transaction should be using the UNDO tablespace that
is getting dropped.
|
If undo tablespace is in use, then we can not drop it.
If you want to know current
undo_tablespace name is user “show parameter undo_
FIND UNDO TABLESPACE
Query DBA_TABLESPACES to find
available undo tablespaces.
Select tablespace_name, status
From DBA_TABLESPACES
Where contents = ‘UNDO’;
TABLESPACE_NAME
STATUS
------------------------------
-----------------
UNDOTBS1
ONLINE
UNDOTBS2
ONLINE
|
And you can offline the tablespace and then you can not use it but could be drop.
drop tablespace undotbs2;
alter tablespace undotbs2 offline;
Should we give unlimited size
to undo tablespace?
No. As per the notion of Oracle that
oracle always try to not overwrite the queries which are executed earlier so it
take all your harddisk space for undo tablespace requirement i.e. it extend the space as requirement raised.
Could we use more than one undo
tablespace?
Yes. But at one time only one undo tablespace can be used.
This is a temporary storage so always keep in mind not become so big.
Consider an example to understand this. Let we required 29
days 10 GB space and 1 day 100 GB space in a month, then we can use different
undo tablespaces. Can we resize the undo tablespace? No, when you decide the
size of tablespace then you can not reclaim it.
Consider an example to understand this. Let we required 29
days 10 GB space and 1 day 100 GB space in a month, then we can use different
undo tablespaces. Can we resize the undo tablespace? No, when you increase the
size of tablespace then you can not reclaim it.
Snapshot-Too-Old Error
Sometime a long-running query can’t find the undo data it
needs and fails with the well-known ORA-01555
snapshot too old error.
To Fix Snapshot Too Old error a DBA should
·
Increase Undo Tablespace size
·
Set RETENTION GUARANTEE for tablespace
·
Keep high UNDO_RETENTION
01:20:00
Configuring Undo Retention
·
UNDO_RETENTION specifies (in seconds) how long
already committed undo information should be retained.
·
Undo Retention is guaranteed when tablespace is
created with RETENTION GUARANTEE.
·
Retention guarantee will avoid Snapshot too old error due to long
running query.
CREATE UNDO TABLESPACE UNDOTBS3 DATAFILE ‘/u01/app/oracle/undotbs3.dbf’
SIZE 100M RETENTION GUARANTEE;
ALTER TABLESPACE UNDOTBS3 RETENTION NOGUARANTEE
Monitoring Undo Statistics
> V$UNDOSTAT
provides a snapshot every 10 minutes
·
UNDOBLKS
>> Total number of undo blocks generated
·
MAXQUERYLEN
>> Length, in seconds of the longest running query
·
SSOLDERRCNT
>>
Number of Snapshot too old error generated
SELECT BEGIN_TIME,
END_TIME, UNDOBLKS, MAXQUERYLEN, SSOLDERRCNT FROM GV$UNDOSTAT
