Table LOCK [message #649417] |
Fri, 25 March 2016 03:33 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/da41024bca4935db557890be9c13b82c?s=64&d=mm&r=g) |
deepakdot
Messages: 91 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.
|
|
|
|
|
Re: Table LOCK [message #649420 is a reply to message #649419] |
Fri, 25 March 2016 04:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8938 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68665 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 #649427 is a reply to message #649425] |
Fri, 25 March 2016 05:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
deepakdot wrote on Fri, 25 March 2016 09:56Thanks ..
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.
|
|
|
|
|
|
|