| Different between ROLLBACK and COMMIT [message #286524] |
Fri, 07 December 2007 21:35  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Hello,all:
I have a test below,I notice the result of ROLLBCK and COMMIT is different,could someone give a explain.
-- session 1
SQL> select distinct sid from v$mystat;
SID
----------
10
SQL> create table t1(x int);
Table created.
SQL> lock table t1 in exclusive mode;
Table(s) Locked.
-- session 2
SQL> select distinct sid from v$mystat;
SID
----------
13
SQL> insert into t1 values(1);
-- was blocked by session 1
--session 3
SQL> select distinct sid from v$mystat;
SID
----------
14
SQL> insert into t1 values(1);
-- was blocked by session 1
--session 4
SQL> select * from v$lock where sid in (10,13,14);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
68F4E054 68F4E068 10 TM 33381 0 6 0 204 1
68F4E0E0 68F4E0F4 13 TM 33381 0 0 3 138 0
68F4E16C 68F4E180 14 TM 33381 0 0 3 6 0
--session 1
SQL> rollback;
Rollback complete.
-- session 2
SQL> insert into t1 values(1);
1 row created. -- one row inserted.
-- session 3
SQL> insert into t1 values(2);
--- still be blocked
-- session 4
SQL> select * from v$lock where sid in (10,13,14);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
68FA309C 68FA31A8 13 TX 458765 5576 6 0 99 0
68F4E054 68F4E068 13 TM 33381 0 3 0 99 1
68F4E0E0 68F4E0F4 14 TM 33381 0 0 6 99 0
It looks when session 1 ROLLBACK,session 3 still be blocked.
but when I did COMMIT instead of ROLLBACK on session 1,now session 2 and session 3 will not be blocked.
What the different here between ROLLBACK and COMMIT?
Regards
Alan
|
|
|
|
| Re: Different between ROLLBACK and COMMIT [message #286525 is a reply to message #286524] |
Fri, 07 December 2007 21:43  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
|
|
|
|
| Re: Different between ROLLBACK and COMMIT [message #286526 is a reply to message #286524] |
Fri, 07 December 2007 21:44 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
The following query may shed some light on this situation.
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/
and should be run from a window/session different from 3 above
[Updated on: Fri, 07 December 2007 21:57] by Moderator Report message to a moderator |
|
|
|
| Re: Different between ROLLBACK and COMMIT [message #286527 is a reply to message #286526] |
Fri, 07 December 2007 22:05 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Thanks for your sql ,anacedent!
It looks clearly,but could you tell me the reason?
-- session 1
SQL> select distinct sid from v$mystat;
SID
----------
10
SQL> create table t1(x int);
Table created.
SQL> lock table t1 in exclusive mode;
Table(s) Locked.
-- session 2
SQL> select distinct sid from v$mystat;
SID
----------
13
SQL> insert into t1 values(1);
-- was blocked by session 1
--session 3
SQL> select distinct sid from v$mystat;
SID
----------
14
SQL> insert into t1 values(1);
-- was blocked by session 1
--session 4
sql>/
SESS ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 10 33381 0 6 0 TM
Waiter: 13 33381 0 0 3 TM
Waiter: 14 33381 0 0 3 TM
--session 1
SQL> rollback;
Rollback complete.
-- session 2
SQL> insert into t1 values(1);
1 row created. -- one row inserted.
-- session 3
SQL> insert into t1 values(2);
--- still be blocked
-- session 4
sql>/
SESS ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 13 33381 0 3 0 TM
Waiter: 14 33381 0 0 6 TM [Updated on: Fri, 07 December 2007 22:06] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Different between ROLLBACK and COMMIT [message #286532 is a reply to message #286531] |
Fri, 07 December 2007 22:31 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
SQL> select tablespace_name from user_tables where table_name='T1';
TABLESPACE_NAME
------------------------------
TEST
SQL> set long 1000;
SQL> select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','TEST')
--------------------------------------------------------------------------------
CREATE TABLESPACE "TEST" DATAFILE
'F:\ORACLE\ORADATA\ORCL\TEST01.DBF' SIZE 209715200 REUSE
AUTOEXTEND ON NEXT 8192 MAXSIZE 1048576000
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2097152 SEGMENT SPACE MANAGEMENT MANUAL |
|
|
|
|
|
| Re: Different between ROLLBACK and COMMIT [message #286535 is a reply to message #286533] |
Fri, 07 December 2007 22:52 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
I changed table t1 initrans to 2 and did the test again,but nothing seems changed.
I noticed there is something strange:
When session 1 locked the table in exclusive mode, both session 2 and session 3 were requesting a 3 mode TM lock like:
sql>/
SESS ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 10 33381 0 6 0 TM
Waiter: 13 33381 0 0 3 TM
Waiter: 14 33381 0 0 3 TM
But when session 1 ROLLBACK,session 2 insert successfully, now session 3 was changed to request a 6 mode TM lock,like:
SESS ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 13 33381 0 3 0 TM
Waiter: 14 33381 0 0 6 TM
|
|
|
|
| Re: Different between ROLLBACK and COMMIT [message #287518 is a reply to message #286535] |
Wed, 12 December 2007 08:43 |
ora_zhp
Messages: 10
Registered: September 2007
|
Junior Member |
|
|
when i create table and add primary key like this：
SQL> create TABLE test
2 (
3 c1 varchar2(1),
4 c2 varchar2(1)
5 )
6 ;
Table created
SQL> alter TABLE test
2 add constraint PK_TEST primary key (C1);
Table altered
after when test rollback situation，session2 and session3 can obtain required lock:
--SESSION1:
SQL> lock table test in exclusive mode;
Table(s) locked
SQL> rollback;
Rollback complete
SQL>
--SESSION2：
SQL> insert into test values('1','A');
--SESSION3:
SQL> insert into test values('2','B');
--查询锁：
SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST FROM v$lock WHERE sid in(532,
2 510,
3 506) ORDER BY sid;
SID TYPE ID1 ID2 LMODE REQUEST
---------- ---- ---------- ---------- ---------- ----------
506 TM 113966 0 0 3
510 TM 113966 0 0 3
532 TM 113966 0 6 0
532 TX 655380 572628 6 0
--在SESSION1中rollback:
SQL> rollback;
Rollback complete
SQL>
--查询锁：
SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST FROM v$lock WHERE sid in(532,
2 510,
3 506) ORDER BY sid;
SID TYPE ID1 ID2 LMODE REQUEST
---------- ---- ---------- ---------- ---------- ----------
506 TM 113966 0 3 0
506 TX 327701 430279 6 0
510 TX 458772 415416 6 0
510 TM 113966 0 3 0
SQL>
why???
|
|
|
|
|
|
| Re: Different between ROLLBACK and COMMIT [message #287526 is a reply to message #287521] |
Wed, 12 December 2007 09:05 |
ora_zhp
Messages: 10
Registered: September 2007
|
Junior Member |
|
|
--like this????
--select v$lock
SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST FROM v$lock WHERE sid in(532,
2 510,
3 506) ORDER BY sid;
SID TYPE ID1 ID2 LMODE REQUEST
---------- ---- ---------- ---------- ---------- ----------
506 TM 113966 0 3 0
506 TX 327701 430279 6 0
510 TX 458772 415416 6 0
510 TM 113966 0 3 0
SQL>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
