Oracle Articles


Total available count: 3
Subject - Databases
Subsubject - Oracle

Types of Locks allowed and Handling Deadlocks in Oracle

When two computer users refer to the same data at the same time, and at least one of the users is updating or deleting parts of the data, certain conflicts can arise. For example, two users read the same data, and then both change it and commit it. The changes committed first are lost since the other set of changes are based on data that was read before the first set of changes was made.

Oracle has provisions for avoiding and resolving deadlocks. Oracle’s basic provisions for data locking and deadlock resolution work automatically, but manual control can be exercised.

  • A share lock that lets other users see the locked data, but they cannot alter them. The lock is useful when you want to query a table, but don’t plan to change it, and want to ensure that other users will not change the data that you are querying between your queries.
  • A share update lock will let other users lock the data as well as query them. This lock is appropriate when you are querying and changing data in a table, and are sharing the data with other users who are doing the same. However, other users may be able to establish locks that keep you from completing an update.
  • A table definition lock prevents other users from gaining any sort of access to the table. Oracle applies this lock automatically whenever it receives the ALTER TABLE command or other commands that would change the table’s structure.
  • An exclusive lock is much like a share lock in that other users can issue queries that allow them to examine data but not change them. Unlike a share lock, an exclusive lock prevents them from applying locks of their own. An exclusive lock is most useful when you plan a series of multiple updates and you want to keep other users from entering other changes until you have finished your work.

Once you have established a lock, it will remain in effect until you issue a COMMIT or a ROLLBACK.

Oracle also has an automatic deadlock detector. When two users become entangled in a deadly embrace, the program will resolve matters by terminating one or the other of the commands that created the situation.

SQL commands that alter the size or structure of a table automatically lock the entire table. UPDATE,INSERT AND DELETE create exclusive locks that allow others to view the table’s contents but not to alter them while you are at work.

 If you don’t wish to rely on Oracle’s automatic locking facilities, you can apply your own locks, either by issuing direct commands or through an SQL command file. The general syntax of a lock command is as :

 LOCK TABLE <table name> IN <type> MODE;

 

Now see how to create different commands by using above syntax where <table name> is name of the table and <type> is lock type.

LOCK TABLE EMPS IN SHARE MODE;
LOCK TABLE EMPS IN SHARE UPDATE MODE;
LOCK TABLE EMPS IN EXCLUSIVE MODE;

 

If another user has already applied a lock that interferes with yours, Oracle will wait until the other lock has been released with a COMMIT or ROLLBACK, then, it will apply your lock. If you don’t want to wait that long you may use the NOWAIT clause to attempt to place the lock immediately:

LOCK TABLE EMPS IN SHARE MODE NOWAIT;

 

In this case, LOCK TABLE will simply inform you if it could not establish the lock.

In Sql*Plus, you can lock individual records of your choice, you can do so with a variation of the SELECT command. Basically, you can lock whatever you can SELECT.

The SELECT command, when used with the FOR UPDATE OF clause places an exclusive lock on one more rows of a table. An example of this would be,

SELECT * FROM EMPS WHERE DEPTNO=3 FOR UPDATE OF PAY,COMN;

 

This command locks rows in which the value of DEPTNO is 3. It also lists the selected rows just like a normal SELECT command. This command locks the entire rows although fields have been specified. Now you can make any updates you wish on the locked rows without interference from other users. After completing updates commit them or roll them back. This will release the lock and let others have access to the file.

The SELECT command, when used with the FOR UPDATE OF clause places an exclusive lock on one more rows of a table. An example of this would be,

SELECT * FROM EMPS WHERE DEPTNO=3 FOR UPDATE OF PAY,COMN;

 

This command locks rows in which the value of DEPTNO is 3. It also lists the selected rows just like a normal SELECT command. This command locks the entire rows although fields have been specified. Now you can make any updates you wish on the locked rows without interference from other users. After completing updates commit them or roll them back. This will release the lock and let others have access to the file.




Next 1 article(s)

1
Introduction to RDBMS and Oracle Products

Comments