OraFAQ Forum: SQL & PL/SQL » Regarding row lock

Home » SQL & PL/SQL » SQL & PL/SQL » Regarding row lock (oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Regarding row lock [message #647347]

Tue, 26 January 2016 21:42

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Hello experts,

I have question on regarding row lock, i have a query like below in my application code, it means it is locking the row? how long will it lock, the reason i am asking is i have scheduler which executes every 2 minutes, the next occurrence scheduler sometimes waiting for 1, 4 , 5 , 10 minutes randomly on query execution. the query execution is just select statement on LM_ID=123131 record.

select LM_IO from LMT where LM_ID=123131 FOR UPDATE NOWAIT;

How do i know because of locking if my query execution is waiting.

Appreciate your thoughts.

Report message to a moderator

Re: Regarding row lock [message #647348 is a reply to message #647347]

Tue, 26 January 2016 22:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How do i know because of locking if my query execution is waiting.
the SELECT is NOT waiting because you explicitly direct it to "NOWAIT"

Once or after the SELECT actually obtains the row, it will retain this row exclusively until COMMIT or ROLLBACK is issued.

I surmise that the PL/SQL procedure has a flawed logic bug coded into it.

Report message to a moderator

Re: Regarding row lock [message #647353 is a reply to message #647347]

Wed, 27 January 2016 01:24

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

How do i know because of locking if my query execution is waiting.

How do you know this is this statement that waits?
By definition SELECT ... NOWAIT does not wait.

Report message to a moderator

Re: Regarding row lock [message #647424 is a reply to message #647353]

Wed, 27 January 2016 10:02

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Temporary locked to impede the attack on the site using this topic.

Report message to a moderator

Re: Regarding row lock [message #647427 is a reply to message #647424]

Wed, 27 January 2016 10:45

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Hacker IP is now blocked, topic is unlocked.

Report message to a moderator

Re: Regarding row lock [message #647459 is a reply to message #647427]

Thu, 28 January 2016 15:25

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Sorry for not being clear on the question

let say if session1 locked the row.

Session1:
SQL>select * from dept where deptno =10 for update nowait;

Now session 2 trying to get that row, what happens? session2 will wait until session1 is committed or rollbacked? how do know if there are any locks existed thats the reason session2 failed or waiting?

Session2:
SQL>select * from dept where deptno =10;

Report message to a moderator

Re: Regarding row lock [message #647462 is a reply to message #647459]

Thu, 28 January 2016 19:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

yes
Time will tell.
results are intuitively obvious.

Report message to a moderator

Re: Regarding row lock [message #647463 is a reply to message #647462]

Thu, 28 January 2016 21:28

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

if commit/rollback takes 30 minutes, until then session2 will wait or error out immediately? how do i know if there are any locks existed thats the reason session2 failed or waiting?

Report message to a moderator

Re: Regarding row lock [message #647465 is a reply to message #647463]

Thu, 28 January 2016 21:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If you had simply issued the 2 SELECT yourself after your previous message, you'd have your answer by now.

With Oracle READERS do not block WRITERS & WRITERS do not block READERS.

Report message to a moderator

Re: Regarding row lock [message #647472 is a reply to message #647463]

Fri, 29 January 2016 00:45

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A good book for you which will answer all your questions:
Database Concepts

Report message to a moderator

Re: Regarding row lock [message #647482 is a reply to message #647472]

Fri, 29 January 2016 06:43

EdStevens
Messages: 1377
Registered: September 2013

Senior Member

Michel Cadot wrote on Fri, 29 January 2016 00:45

A good book for you which will answer all your questions:
Database Concepts

Specifically, https://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT1334

Report message to a moderator

Re: Regarding row lock [message #647483 is a reply to message #647463]

Fri, 29 January 2016 06:50

EdStevens
Messages: 1377
Registered: September 2013

Senior Member

gorants wrote on Thu, 28 January 2016 21:28

if commit/rollback takes 30 minutes, until then session2 will wait or error out immediately? how do i know if there are any locks existed thats the reason session2 failed or waiting?

Please review the Concepts Manual (link provided by Michael), specifically the section on data concurrency.

If the only lock is session B waiting on the lock held by Session A, then the wait will be as long as it takes for A to release the lock via commit or rollback. But if in the course of events, B then acquires a lock on a resource that is then needed by A, then each session has a resource needed by the other and each is waiting on the other to release. this is known as a "deadly embrace", or a "deadlock". Eventually, the db will detect this and kill one of the transactions, allowing the other to complete. When this happens, a message to that effect is written to the alert log, and that message specifically states that it is an application error. And I just gave you two terms on which to do further reading.

Report message to a moderator

Re: Regarding row lock [message #647496 is a reply to message #647483]

Fri, 29 January 2016 12:48

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Thank you all for your inputs

Report message to a moderator

Re: Regarding row lock [message #647497 is a reply to message #647496]

Fri, 29 January 2016 13:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What did you learn?
What is the answer to your question?

Report message to a moderator

Re: Regarding row lock [message #647498 is a reply to message #647482]

Fri, 29 January 2016 13:08

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Thanks for the link it is helpful to understand deadlock...

Have another question, Oracle automatically detect deadlock and rollbacks? In Java world deadlock will not get resolved automatically.

also from the link is my understanding is correct?

https://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT1334

t0 session 1 -- committed
t1 session 1 -- roll backed
t0 session 2 -- committed
t1 session 2 - committed

[Updated on: Fri, 29 January 2016 13:19]

Report message to a moderator

Re: Regarding row lock [message #647499 is a reply to message #647498]

Fri, 29 January 2016 13:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I have NO idea what you understand.
You have your own database to experiment & observe.

>t0 session 1 -- committed
>t1 session 1 -- roll backed
at t0 I have no idea what, if anything, is being committed
at t1 there is nothing to ROLLBACK due to COMMIT at t0

Report message to a moderator

Re: Regarding row lock [message #647505 is a reply to message #647499]

Fri, 29 January 2016 15:17

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

You are absolutely true.

I am really confused .. producing deadlock is really difficult i tried myself.

if you dont mind could you please correct me again

let say if session1 locked the row. i believe below select statement will does row lock -- is this correct?

Session1:
SQL>select * from dept where deptno =10 for update nowait;

Now session 2 trying to get that row. here session2 doesn't wait as Oracle READERS do not block WRITERS & WRITERS do not block READERS. -- is this correct ?

Session2:
SQL>select * from dept where deptno =10;

Here session3 will wait until session1 is rollback/committed -- is this correct?

session3
SQL>select * from dept where deptno =10 for update nowait;

Report message to a moderator

Re: Regarding row lock [message #647506 is a reply to message #647505]

Fri, 29 January 2016 15:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Here session3 will wait until session1 is rollback/committed -- is this correct?
NO, session 3 does not wait due to NOWAIT

If you actually take time to READ the documented deadlock example you might notice that the SQL involved are UPDATE (not SELECT)
WRITERS do DML; INSERT, UPDATE, or DELETE.
READERS do SELECT
Deadlock almost always involves two WRITERS

Report message to a moderator

Re: Regarding row lock [message #647508 is a reply to message #647506]

Fri, 29 January 2016 15:48

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Thank you sir, appreciate your patience answering all my silly question.

Another lingering question i have is

if Oracle Database automatically detects deadlocks and resolves, why we need to worry about deadlocks?

in this link example

https://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT1336 (this is confusing they have used session and transactions interchangeably)

user1 (session1) does transaction t0 and t1

user2 (session2) does transaction t0 and t1

when dead lock happened user1 t0 and t1 committed and user2 t0 and t1 rollbacked.

Report message to a moderator

Re: Regarding row lock [message #647509 is a reply to message #647508]

Fri, 29 January 2016 15:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>if Oracle Database automatically detects deadlocks and resolves, why we need to worry about deadlocks?

I do NOT worry about DEADLOCKS; ever & neither should you.
When DEADLOCK occur, the application must change if they are to not occur again in the future.
DEADLOCK result only from buggy application & can occur even by wizard level developers.

>(this is confusing they have used session and transactions interchangeably)
Any session will always have at most 1 active transaction at any point in time.
A transaction consists of 1 or more SQL statements.
A transaction start by issuing some SQL statement & terminates only with COMMIT or ROLLBACK.

http://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT117

know ACID!

Report message to a moderator

Re: Regarding row lock [message #647511 is a reply to message #647509]

Fri, 29 January 2016 16:12

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Or an implied rollback (disconnect without issuing a commit) or an implied commit (if exiting sql*plus without commit/rollback)

Report message to a moderator

Re: Regarding row lock [message #647529 is a reply to message #647509]

Sun, 31 January 2016 19:21

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

sure thanks for the details

Report message to a moderator

Previous Topic:	UTL_FILE Writeing in multiple text files
Next Topic:	Missing Day data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 11 16:11:22 CDT 2026