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
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

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
BlackSwan
Messages: 26766
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: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1. No
2. (in simple case) Row exclusive lock on the row

Check if you have non-indexed foreign keys.

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


Current Time: Tue Feb 11 09:20:18 CST 2025