Home » SQL & PL/SQL » SQL & PL/SQL » Hows Oracle Lock Works (WIN XP, Oracle 10g)
Hows Oracle Lock Works [message #337774] Fri, 01 August 2008 03:20 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi All,

Could please anyone clarify my doubts about Locks,

I have a set of procedure,one of which is updating the say, XYZ table and other will be deleting the data from the same table.
Update procedure will update the records on incremental basis, that is every 45 mins and logic for deletion will work once in a day basis,

So my question is : does select will hold a lock on the table which will affect the delete procedure.


from,
FREAKABHI
Re: Hows Oracle Lock Works [message #337775 is a reply to message #337774] Fri, 01 August 2008 03:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, SELECT statements in oracle do not block other DML statements.
Re: Hows Oracle Lock Works [message #337776 is a reply to message #337774] Fri, 01 August 2008 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the opposite is also true.

Readers don't block writers.
Writers don't block readers.

Regards
Michel

Re: Hows Oracle Lock Works [message #337780 is a reply to message #337774] Fri, 01 August 2008 03:42 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
thanks for reply,

one more doubt I have, how about my update procedure
held lock on some of the rows, and there begin my delete
procedure tries to delete the same block,so will be delete procedure fail or will wait for lock to be released?


thanks,
freakabhi
Re: Hows Oracle Lock Works [message #337791 is a reply to message #337780] Fri, 01 August 2008 04:02 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Lock are on rows not on blocks.
If you try to delete rows that are currently updated (that is not commited) you will wait until the end of the other transaction.

Regards
Michel
Previous Topic: How to query of the table has a CHAR data type
Next Topic: Run procedure parallaly
Goto Forum:
  


Current Time: Sat Dec 10 08:51:32 CST 2016

Total time taken to generate the page: 0.27914 seconds