OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Table LOCK (Oracle 12C)

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

Table LOCK [message #649417]

Fri, 25 March 2016 03:33

deepakdot
Messages: 89
Registered: July 2015

Member

Hi

We just migrated from DB2 to Oracle successfully. In DB2 we had a sql where we lock the row with RS Mode.

DB2 Versoin: SELECT COL1 FROM T1 WHERE COL1 = 1 FOR UPDATE WITH RS USE AND KEEP UPDATE LOCKS;

I changes this on Oracle to : SELECT COL1 FROM T1 WHERE COL1 = 1 FOR UPDATE;

But i am not sure if this is correct. Most of the time i see this table is locked. How can i write my select query specifying the RS(Row Share) mode?
or shall i define the lock mode in DDL like (LOCK TABLE T1 IN ROW SHARE MODE; ) ?

Please Guide.

Deepak.

Report message to a moderator

Re: Table LOCK [message #649418 is a reply to message #649417]

Fri, 25 March 2016 03:41

John Watson
Messages: 8927
Registered: January 2010
Location: Global Village

Senior Member

SELECT ... FOR UPDATE applies row share locks. You do not need to do anything else, though you might want to append a NOWAIT|WAIT|SKIP LOCKED clause.

[Updated on: Fri, 25 March 2016 03:42]

Report message to a moderator

Re: Table LOCK [message #649419 is a reply to message #649418]

Fri, 25 March 2016 04:01

Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Row Share, are you sure? It seems to me that since a patchset of 10.2 it is Row Exclusive due to some internal dead locks in RAC or maybe this has change in 12c.
In 11.2:

SQL> select * from t for update;
EID        FNAME      LNAME      SSN
---------- ---------- ---------- ---------
123456     JOHN       DOE        487328967
928347     PETER      FOSTER     472983398

2 rows selected.

SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
------------------------------------------------------
9

1 row selected.

SQL>  select type, lmode from v$lock where sid=9;
TY      LMODE
-- ----------
AE          4
TM          3
TX          6

3 rows selected.

Lock mode for TM lock is 3 that is Row Exclusive.

Report message to a moderator

Re: Table LOCK [message #649420 is a reply to message #649419]

Fri, 25 March 2016 04:17

John Watson
Messages: 8927
Registered: January 2010
Location: Global Village

Senior Member

No, I am not sure, and thank you for making me check. This is from the 12.1.0.2 docs:Quote:

ROW SHARE
ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.

ROW EXCLUSIVE
ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.

so the difference is whether this would be blocked:Quote:

SHARE
SHARE permits concurrent queries but prohibits updates to the locked table.

I can't imagine where one would want to use this SHARE mode, so I expect you are correct: it is something that would be used internally.
--update: I should have added that the reason I see no need for SHARE is that I would use EXCLUSIVE if I wanted to prevent any other session from DMLing the table.

[Updated on: Fri, 25 March 2016 04:21]

Report message to a moderator

Re: Table LOCK [message #649424 is a reply to message #649420]

Fri, 25 March 2016 04:48

Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I found it, it is older than I thought: 9.2.0.6 and 10.1.0.4.

Bug 3646162 - False deadlock (ORA-60) in a RAC environment / TM lock mode change (Doc ID 3646162.8)

[Updated on: Fri, 25 March 2016 04:49]

Report message to a moderator

Re: Table LOCK [message #649425 is a reply to message #649420]

Fri, 25 March 2016 04:56

deepakdot
Messages: 89
Registered: July 2015

Member

Thanks ..

With the example i see , the oracle used lock mode internally. Can i specify which mode to use for locking in my Select Query ?

Report message to a moderator

Re: Table LOCK [message #649426 is a reply to message #649425]

Fri, 25 March 2016 05:01

Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No, as you can see in SQL Reference this is not in the syntax.
But you can lock the whole table in a specific mode which does not mean Oracle will not take its own locks if they are more restrictive than the one you did.

Report message to a moderator

Re: Table LOCK [message #649427 is a reply to message #649425]

Fri, 25 March 2016 05:10

John Watson
Messages: 8927
Registered: January 2010
Location: Global Village

Senior Member

deepakdot wrote on Fri, 25 March 2016 09:56

Thanks ..

With the example i see , the oracle used lock mode internally. Can i specify which mode to use for locking in my Select Query ?

I think it is fair to say that in the Oracle environment you should not use any LOCK TABLE commands unless you have a good (and probably unusual) reason to do so. The default locking will give the highest possible concurrency compatible with the default read-committed isolation level. Oracle's multiversion consistency model (implemented with undo segments) is very efficient for this, much more so than (for example) either DB2 or SQL Server. Of course, it does have other drawbacks.

So, just trust Uncle Oracle to deliver data integrity, and do not interfere the mechanism.

Report message to a moderator

Re: Table LOCK [message #649428 is a reply to message #649427]

Fri, 25 March 2016 05:26

deepakdot
Messages: 89
Registered: July 2015

Member

Bug 3646162 - False deadlock (ORA-60) in a RAC environment / TM lock mode change (Doc ID 3646162. Cool

I hope this is fixed in Oracle 12C RAC .

Report message to a moderator

Re: Table LOCK [message #649429 is a reply to message #649428]

Fri, 25 March 2016 05:37

deepakdot
Messages: 89
Registered: July 2015

Member

Thanks all so much for all your input on this topic.

Report message to a moderator

Re: Table LOCK [message #649430 is a reply to message #649428]

Fri, 25 March 2016 06:12

Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

deepakdot wrote on Fri, 25 March 2016 11:26

Bug 3646162 - False deadlock (ORA-60) in a RAC environment / TM lock mode change (Doc ID 3646162.

I hope this is fixed in Oracle 12C RAC .

This has been fixed in the versions I mentioned (and of course newer ones) by changing the lock mode from row share to row exclusive.

Report message to a moderator

Re: Table LOCK [message #649436 is a reply to message #649430]

Fri, 25 March 2016 09:12

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Deepak.,
Welcome to oracle. If you need reference material to read up on oracle, see the following link

http://docs.oracle.com/en/database/database.html

Report message to a moderator

Previous Topic:	Using utl_file
Next Topic:	Multi Level Table Type Output

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 15 23:40:19 CDT 2024