Home » SQL & PL/SQL » SQL & PL/SQL » Locks on Row and Table in Oracle
Locks on Row and Table in Oracle [message #434610] Wed, 09 December 2009 23:15 Go to next message
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

I am encountered with dead lock error.

1. Updating different records of same table in different sessions will result in deadlock error?

2.While updating a record in session1 on rowlock is accquired or it places table level lock?

The below is scenario.

Emp table with different deptno's.

I am calling a procedure in session with deptno as parameter(ex:10). It queries the records of deptno from emp table. Update only those records.
Concurrently in other session the same function is called with difffernt deptno and it update records fo thos deptno.

While updating a row oracle places table lock or only places a rowlock. If it places a table lock then the deadlock error results.

In this case is it possible to partition the table based on deptno and do the same.Each partition is treated as different table or loakc is placed on complete table.

Request to give some idea on this.

Thanks in Advance
Re: Locks on Row and Table in Oracle [message #434611 is a reply to message #434610] Wed, 09 December 2009 23:19 Go to previous messageGo to next message
Messages: 25529
Registered: January 2009
Location: SoCal
Senior Member
>I am encountered with dead lock error.
The trace file contains both SQL involved.

00060, 00000, "deadlock detected while waiting for resource"
// *Cause:  Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
//          involved. Retry if necessary.
Re: Locks on Row and Table in Oracle [message #434636 is a reply to message #434610] Thu, 10 December 2009 01:11 Go to previous message
Michel Cadot
Messages: 65084
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. No
2. (in simple case) Row exclusive lock on the row

Check if you have non-indexed foreign keys.

Previous Topic: Problem with Long column
Next Topic: Getting an ORA-02264 Error on Table Drop
Goto Forum:

Current Time: Sat Jul 22 12:04:12 CDT 2017

Total time taken to generate the page: 0.12906 seconds