Lesson 10

Tuning Oracle locks Conclusion

As you can see, there is quite a bit of complexity to Oracle locking. The main points of this module include:

Oracle locking is designed to prevent inconsistencies and inadvertent overlaying of row data
Oracle issues locks automatically for all UPDATE statements
Oracle programmers can issue row-exclusive locks using the FOR UPDATE clause
Oracle programmers can issue table-exclusive locks with the LOCK TABLE command
Oracle deadlocks are detected and prevented by aborting the task that would have caused the deadlock

Oracle provides the V$LOCK_ACTIVITY and V$SYSSTAT views to show lock information
Oracle provides the catblock.sql and utllockt.sql scripts to enhance the V$ views and show tasks that are holding or waiting onlocks
Application can issue custom locks using the dbms_lock package

Detecting Resolving Lock Contention

We will be continuing our discussion of Oracle locks in the next module where we will explore techniques for detecting and resolving lock contention. Now you should be able to:

Describe Oracle's locking scheme
Describe the purpose of shared and exclusive Oracle locks
Describe Oracle lock modes
Identify an Oracle deadlock
Prevent a database deadlock
List the dictionary lock views
Run the lock utility scripts
Use the dbms_lock package

New terms

Here are the terms from this module that may have been new to you:

coexistence methods: A technique for ensuring coexistence between two methods for access to the Oracle database.
concurrency management: This is the process of ensuring that all database changes are single-threaded, and that no two tasks can ever update a row at the same moment in time.
Deadlock: A situation that occurs when two processes are dependent on each other, resulting in an impass.
dbms_lock: An Oracle packages that makes Oracle kernel's lock management services available to user-written applications.
exclusive locks: Exclusive locks are issued for the duration of all SQL UPDATE or DELETE statements to ensure that all changes are single-threaded through the database.
exclusive table locks: The most restrictive of the table locks. Prevents everything except queries against the affected table. Exclusive locks are used when the programmer desired exclusive control over a set of rows until their operation has completed.
lock hit ratio: Used to identify excessive lock conversion by the DLM.
locks: Locks are used by the Oracle database to ensure that database rows are not inadvertently written-over by concurrent tasks.
pre-emptive locks: Exclusive locks issued at the time of retrieval and held until the task has committed or ended.
row exclusive locks: A type of lock issued automatically against a table when an UPDATE, DELETE, or INSERT statement is issued against the table.
row locks: Locks placed on individual rows within an Oracle table.
row share table locks: A type of lock issued when an SQL transaction has declared its intent to update the table in row share mode.
Shared row exclusive table locks: Type of lock issued with the LOCK TABLE table1 IN SHARE ROW EXCLUSIVE MODE command.
shared locks: In shared locking, whenever a unit of data is retrieved from the database, an entry is placed in the database storage pool.
table locks:A single lock that encompasses an entire Oracle table.
table share locks: Type of lock issued when the LOCK TABLE command is issued against the table.

Oracle Database 12c Performance Tuning
Now that we know the basics, let us move on to look at advanced Oracle locking topics. In the next module we will explore the detection and correction of Oracle lock contention.

Oracle Lock Concepts - Quiz

To complete this module, click the Quiz link below to test your knowledge so far of Oracle locks concepts and tuning.
Oracle Lock Concepts - Quiz