Home » SQL & PL/SQL » SQL & PL/SQL » select ... for update lock type is RX not RS? (Oracle Database 10g 10.2.0.1.0, Windows XP)
select ... for update lock type is RX not RS? [message #422863] Sun, 20 September 2009 23:09 Go to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
In Oracle Concepts, select ... for update uses RS lock mode TM lock.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref2063

V$LOCK
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1147.htm#REFRN30121

But in my test, select ... for update got a RX lock mode TM lock.

SQL> select * from v$version;

BANNER                                                                                              
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod                                    
PL/SQL Release 10.2.0.1.0 - Production                                                              
CORE	10.2.0.1.0	Production                                                                          
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production                                             
NLSRTL Version 10.2.0.1.0 - Production                                                              

Elapsed: 00:00:00.01
SQL> create table t(id int);

Table created.

Elapsed: 00:00:00.01
SQL> insert into t values(1);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select sid from v$mystat where rownum<=1;

       SID                                                                                          
----------                                                                                          
       136                                                                                          

Elapsed: 00:00:00.01
SQL> select * from v$lock where sid=136;

no rows selected

Elapsed: 00:00:00.01
SQL> select * from t for update;

        ID                                                                                          
----------                                                                                          
         1                                                                                          

Elapsed: 00:00:00.01
SQL> select * from v$lock where sid=136;

ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK 
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- 
333415A4 333415BC        136 TM        53715          0          3          0          5          0 
333A1194 333A12B0        136 TX       458781        620          6          0          5          0 

Elapsed: 00:00:00.01
SQL> spool off


ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
333415A4 333415BC 136 TM 53715 0 3 0 5 0


Could anybody explain this?
Re: select ... for update lock type is RX not RS? [message #422864 is a reply to message #422863] Sun, 20 September 2009 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this evolution was introduced in 9.2.0.4 (iirc) but documentation was never updated.
It was made to prevent from some dead-lock cases in RAC.

Regards
Michel
Re: select ... for update lock type is RX not RS? [message #422876 is a reply to message #422864] Mon, 21 September 2009 00:41 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
Michel, thank you for your help! Smile

Can I think that row share mode TM lock is useless after version 9.2.0.4?
Do we need RS lock mode after version 9.2.0.4?
If we need RS lock mode after version 9.2.0.4, could you give me an example?

Thank you!

[Updated on: Mon, 21 September 2009 00:49]

Report message to a moderator

Re: select ... for update lock type is RX not RS? [message #423034 is a reply to message #422864] Mon, 21 September 2009 20:13 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
I think we need row share mode lock in this scenario:
Transaction 1 doesn't want any other session to drop table, alter table when it is in progress.

for example, in session 1:
SQL> create table t (id int, name varchar2(30));

Table created.

Elapsed: 00:00:00.01
SQL> lock table t in row share mode;

Table(s) Locked.

Elapsed: 00:00:00.00
SQL> select * from v$lock where sid=(select sid from v$mystat where rownum<=1);

ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK 
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- 
333415A4 333415BC        159 TM        53728          0          2          0          9          0 

Elapsed: 00:00:00.01


Now in session 2, when we try to drop table and alter table, we will receive errors:
SQL> drop table t;
drop table t
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified 


Elapsed: 00:00:00.01
SQL> alter table t add addr varchar2(128);
alter table t add addr varchar2(128)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified 


Elapsed: 00:00:00.01
SQL> alter table t drop column name;

Table altered.

Elapsed: 00:00:00.01



In session 2 we can't add a new column to the table in row share lock mode, but we can drop an existing column from the table in row share lock mode. It is an strange thing!
Re: select ... for update lock type is RX not RS? [message #423039 is a reply to message #423034] Mon, 21 September 2009 20:30 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Any professionally managed Production DB does not allow DML to occur while DDL is being applied.
Only a single DBA is authorized to make DDL changes at any 1 time.
Re: select ... for update lock type is RX not RS? [message #423060 is a reply to message #422863] Mon, 21 September 2009 23:59 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Good point BlankSwan.

One of the most difficult systems I had to work with was one where programmers with the best intentions built a device where they would dynamically build tables on the fly. They claimed it would make certain pieces of code easier to write and easier to maintain.

What they found out was no solution is perfect and they did not understand the workings of databases in general and Oracle in particular. They had horrid performance, concurrency, and scalability issues with no easy solution but to re-write things not to use the "easy solution".

Kevin
Previous Topic: cursors with where in clause
Next Topic: NUMBER(1) or CHAR(1) for flag?
Goto Forum:
  


Current Time: Sat Dec 03 01:17:41 CST 2016

Total time taken to generate the page: 0.09880 seconds