Question

Oracle Locking Modes

Asked by: Vikas_Dixit

Experts,

Can some one tell me the definition and what types of statement will create locks in different modes i.e
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',   ** Specially this one
6, 'Exclusive'

I have a scenario where i am facing blocking lock problem :

An user is modifying MY_TABLE fromFront end, and  FE holds lock in "Row-X (SX)" mode.  The FE has selected record using  "SELECT * FROM MY_TABLE WHERE KEY = :A1 FOR UPDATE NOWAIT "

Now in a different session Application Server trying to delete another record in the same table and Holds lock in "Row-X (SX)" mode  because of " SELECT ROWID FROM MY_TABLE WHERE KEY = :B1 FOR UPDATE "
and requeste S/Row-X (SSX), to Execute the statement "DELETE MY_TABLE WHERE ROWID = :B1"

Here's how the dba_dml_locks looks like :
SESSION_ID      NAME      MODE_HELD      MODE_REQUESTED      LAST_CONVERT
51      MY_TABLE      Row-X (SX)      S/Row-X (SSX)      3344
70      MY_TABLE      Row-X (SX)      None               3688

Now the FE Lock Becomes blocking. Any help in understanding this is much appreciated.

Regards,
Vikas Dixit

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2005-10-20 at 09:43:39ID21602004
Tags

oracle

,

lock

,

modes

Topic

Oracle Database

Participating Experts
2
Points
500
Comments
13

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Locks at Oracle 7.x.x
    I would like to know how can I check for an existing locks at my D.B , is there any tools to help me ? and if there any trace of lock that had been in my D,B ? Thanks ,
  2. interview questions for oracle dba's
    I want to know the general questions for oracle dba interviews. Please advice me the sites and questions ( with answers - in brief )
  3. Oracle DBA
    How to proceed with Oracle DBA ..
  4. Insert code in Trigger causes ORA01591 lock held by in…
    Hi there, We use the Oracle transparent gateway for informix to access an informix db from oracle. This is generally working. When I create a trigger on the oracle db and it is fired, it causes the ORA-01591 error message. It looks like there is a problem with the 2point c...
  5. lock held
    hi, which table is locked i can see anyt there ?? get snapshot for locks on pilot Database Lock Snapshot Database name = PILOT Database path = W:\DB2\NODE0000\SQL00006\ Input database alias ...
  6. Oracle What is Locked.
    How do I know what tables are locked and who is locking them? The DB is Oracle 9.2.070. Useres access through a java web connection. Which is hanging.

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: ram_0218Posted on 2005-10-20 at 10:49:31ID: 15126601

 

by: Vikas_DixitPosted on 2005-10-20 at 10:58:41ID: 15126660

Thanks ram_0218,

This page does not have any information on 'S/Row-X (SSX)' or any other lock modes.
I am looking for detailed info on the different lock modes..

Regards,
Vikas

 

by: ram_0218Posted on 2005-10-20 at 11:16:47ID: 15126795

One of the script we have in our office, for monitoring locks..This may be of your interest,

This script checks the locking activity in an Oracle database. This is very useful for finding out about locking problems, and it gives very detailed information to determine if SQL scripts needs to be optimized for locking issues.



SET PAGESIZE 20          
Set linesize 132;
column "B/U" format a3
COLUMN LOCK_ID1 FORMAT A45 heading "Object Name/Lock ID1"
column ctime format 999999
column block format 99999

SELECT
       substr(to_char(l.sid),1,4)  "SID",
       substr(s.type,1,1)          "B/U",
       P.spid                      "SRVR PID",
       s.process                   "CLNT PID",
       substr(s.machine,1,7)       "MACHINE",                    
       l.type,

       DECODE(L.TYPE,'MR','File_ID: '||L.ID1,
                     'TM', LO.NAME,
                     'TX','USN: '||to_char(TRUNC(L.ID1/65536))||'  RWO: '||nvl(RWO.NAME,'None'),
              L.ID1)
       LOCK_ID1,

       decode(l.lmode,
       0, 'None',
       1, 'Null',
       2, 'Row-S (SS)',
       3, 'Row-X (SX)',
       4, 'Share',
       5, 'S/Row-X (SSX)',
       6, 'Exclusive',
       substr(to_char(l.lmode),1,13)) "Locked Mode",
       decode(l.request,
       0, 'None',
       1, 'Null',
       2, 'Row-S (SS)',
       3, 'Row-X (SX)',
       4, 'Share',
       5, 'S/Row-X (SSX)',
       6, 'Exclusive',
       substr(to_char(l.request),1,13)) "Requested",
       l.ctime,
       l.block

FROM   v$process P,
       v$session S,
       v$lock l,
       sys.obj$ lo,
       sys.obj$ rwo

WHERE  l.type      != 'MR'
AND    l.sid       = S.sid  (+)
AND    S.paddr     = P.addr (+)
AND    LO.OBJ#(+)  = L.ID1
AND    RWO.OBJ#(+) = S.ROW_WAIT_OBJ#
order by l.sid;
SET PAGESIZE 14
clear columns





 

by: ram_0218Posted on 2005-10-20 at 11:22:50ID: 15126849

oops!! I didnt see your msg.., what exactly is your question?

 

by: Vikas_DixitPosted on 2005-10-20 at 11:53:39ID: 15127135

Thaaks ram_0218,

I am looking for more insight into various locking modes, to that I can figure out a solution/work-around for the locking problem that I mentioned.
Monotoring locks is not an issue....

Regards,
Vikas Dixit

 

by: ram_0218Posted on 2005-10-20 at 12:24:32ID: 15127496

Ok, heres my understanding!!

why your Application server takes S/Row-X (SSX) lock before deleting?? If I'm not mistaken, they obtain this lock by program., If so, you'll have to remove it!!

By default when a DML operation is carried out, Row-X (SX) is lock given to the application!! That is largen than enough to maintain your data integrity!!

In your scenerio, application server puts a SSX on the table that denies any further DMLS be executed on the top of the table which is explicitly understood.

 

by: Vikas_DixitPosted on 2005-10-21 at 06:51:14ID: 15132295

ram_0218,

I don't have any explicit code in my application to put "S/Row-X (SSX) " lock. Only piece of code are "FOR UPDATE" and "FOR UPDATE NO WAIT" to get locks., which are necessary as per application design.

Futher, Application has not put the "S/Row-X (SSX) " lock yet, It's requesting lock in this mode, which it is not able to get, because the FE "Row-X (SX)" becomes blocking.
All I want to know, why my application will request "S/Row-X (SSX) " lock and what can I do to avoid it ?

regards,
Vikas Dixit

 

by: Vikas_DixitPosted on 2005-10-21 at 08:26:49ID: 15133112

Ok.. I got my answer partialy at :

http://www.csee.umbc.edu/help/oracle8/server.815/a68003/01_08pro.htm#8123 "Table 8-2 Non-default Locking Behavior"

and

http://www.csee.umbc.edu/help/oracle8/server.815/a68001/dbms_loc.htm "Table 18-1 Lock Compatibility ".

regards,
Vikas Dixit

 

by: schwertnerPosted on 2005-10-21 at 08:27:36ID: 15133121

A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements:

INSERT INTO table ... ;

UPDATE table ... ;

DELETE FROM table ... ;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

A row exclusive table lock is slightly more restrictive than a row share table lock.

Permitted Operations: A row exclusive table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table.

Prohibited Operations: A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements:

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;


1.   How does Oracle handle locking?
====================================

Oracle use enqueues as locking mechanism for managing access to shared
resources. A shared resource can be a table definition, a transaction
or any type of structure that represent something sharable between sessions.
Each type of actions performed by Oracle sessions on those shared resources
will require a certain type of lock or lock mode (e.g. a 'select on a table'
action will require that the executing session has a shared lock on the
resource 'table definition' of the selected table). When conflicting
actions are occuring, Oracle will serialize the processing by putting
a number of sessions in waiting mode until the work of the blocking
session has been completed.

Each enqueue represent a sharable resource. Sessions are acquiring,
converting and releasing locks on resources in function of the work
they need to perform. Releasing locks are performed by the sessions
when they issue a commit or a DDL statement (i.e. implicit commit), or
by SMON if the sessions have been killed. Conversion is the process of
changing a lock from the mode we currently hold to a different mode.
Acquiring a lock is the process of getting a lock on a resource on which we
currently do not have a lock. We are allowed to convert a lock if the mode
we require, is a subset of the mode we hold or is compatible with the
modes already held by other sessions. Otherwise, we wait on the converters
queue of the resource. We are allowed to acquire a lock, if there are no
converters or waiters ahead of us and the mode we require is compatible with
the modes already held by others.  Otherwise, we wait on the waiters queue
of the resource. When a session has a lock on a resource, then it stands
in the owner queue of the resource. When a lock is released or converted,
the converters and waiters are re-checked to see if they can be acquired.
The converters are processed first, then the waiters.

Row locking in Oracle is based on the TX enqueues and is known as
transactional locking. When two or more sessions are changing data on one
row of a table (DML statements on the same record), the first session will
lock the row by putting his transaction reference in the block containing
the row header. The other sessions will look at this lock information
and will wait on the transaction (i.e. the TX enqueue of the blocking session)
of the first session before proceeding. When the first session performs a
commit, the TX resource will be released and the waiters will start their
own locking. The waiting sessions are thus waiting on an exclusive TX
resource, but their TM resources they are holding give the objects they are
in fact waiting on.

If a lock has not been acquired or converted, a deadlock check is made by
the waiting session after a timeout. For example, following situation
generates a deadlock: user A gets an S lock on resource 1, then user B
gets an S lock on resource 2; later, A request an X lock on resource 2
and waits, then B requests an X lock on resource 1 and waits; now, A is
waiting for B to release resource 2, which is waiting for A to release
resource 1; A is indirectly waiting for A. It is a deadlock, generating
a tracefile in the user_dump_dest and and ORA-60 in the detecting session.

The lock and resource information stands within the SGA to allow PMON to
recover in the event of process failure. The PMON is responsible for
releasing the locks of the crashed/killed processes.


2.   How to find the resource definitions?
==========================================

Each resource is represented by an enqueue. An enqueue is identified by
a unique name, also known as the resource name. The name has the form:
<Type, ID1, ID2>. Type has two characters and represent a resource type
(e.g. “TM” for the table definition type). ID1 and ID2 are positive numbers
and identify the resource fully (e.g. ID1 is the object_id  of the table
if the resource type is "TM").

The description of most enqueue/resource types can be found in the appendixes
of the Oracle Reference Guide. The most commonly known resource types are
the TM, TX, ST and UL resources.
a. The TM resource, known as the DML enqueue, is acquired during the execution
of a statement when referencing a table so that the table is not dropped or
altered during the execution of it.
b. The TX resource, known as the transaction enqueue, is acquired exclusive
when a transaction initiates its first change and is held until the transaction
does a COMMIT or ROLLBACK. Row locking is based on TX enqueues. SMON will
acquire it when doing recovery of a killed/crashed process.
c. The ST resource is used to serialize space management tasks when a
session's job requires extents to be allocated to objects.
d. The UL resource represent the user-defined locks defined by the DBMS_LOCK
package.


3.   Which lock modes are required for which table action?
==========================================================

The following table describes what lock modes on DML enqueues are actually
gotten for which table operations in a standard Oracle installation.

 Operation                Lock Mode LMODE Lock Description
------------------------- --------- ----- ----------------
 Select                     NULL     1    null
 Select for update          SS       2    sub share
 Insert                     SX       3    sub exclusive
 Update                     SX       3    sub exclusive
 Delete                     SX       3    sub exclusive
 Lock For Update            SS       2    sub share
 Lock Share                 S        4    share
 Lock Exclusive             X        6    exclusive
 Lock Row Share             SS       2    sub share
 Lock Row Exclusive         SX       3    sub exclusive
 Lock Share Row Exclusive   SSX      5    share/sub exclusive
 Alter table                X        6    exclusive
 Drop table                 X        6    exclusive
 Create Index               S        4    share
 Drop Index                 X        6    exclusive
 Truncate table             X        6    exclusive
-----------------------------------------------------------

 

by: ram_0218Posted on 2005-10-21 at 08:48:40ID: 15133353

The originarl pbm if i'm not mistaken is,

'Row-X (SX)',
denies denies  locking in SHARE mode which is being requested by the application., As I see your pbm, if you can do the first update with 'Row-S (SS)', it wont prohibit the share more locking option which is being req by the application., So change your code may be to this, and see what happens.,

instead of select for update,
 lock table emp in row share mode;

and then do your update, and release the lock!!

meantime if the application requests for share exclusing lock mode, it'll be granted with no wait!!

 

by: Vikas_DixitPosted on 2005-11-03 at 13:17:49ID: 15220563

Hi all,

Thanks for al your help, found the problem : My this particular table had a self referential integrity constraint and as per the article found at :
 http://www.fors.com/orasupp/rdbms/misc/15476_1.HTM

"
1) When your application has referential integrity and attempts to modify
   the child table, Oracle will get a TABLE LEVEL SHARE LOCK on the parent
   table when there is NO index on the foreign key.
"

I created index on the column that was referencing the primary column and the problem got resloved....

Apparently this problem started happening after a recent Database patch VTM 2003 ( I have no idea about this patch).

Regards,
Vikas Dixit



20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...