Home » RDBMS Server » Server Administration » Different between ROLLBACK and COMMIT
Different between ROLLBACK and COMMIT [message #286524] Fri, 07 December 2007 21:35 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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 Go to previous messageGo to next message
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 #286528 is a reply to message #286524] Fri, 07 December 2007 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
IMO, it is because no COMMIT or ROLLBACK has been issued by session #2 after doing INSERT and is blocking session #3.
Re: Different between ROLLBACK and COMMIT [message #286529 is a reply to message #286528] Fri, 07 December 2007 22:17 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hi,anacedent:
There is no pk or some constraints on t1. why session 2 insert will block session 3?
When I did COMMIT on session 1,both session 2 and session 3 insert successfully.
Regards
Alan
Re: Different between ROLLBACK and COMMIT [message #286531 is a reply to message #286524] Fri, 07 December 2007 22:27 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Would I be correct if I said that the tablespace holding this table is a dictionary managed tablespace?

Re: Different between ROLLBACK and COMMIT [message #286532 is a reply to message #286531] Fri, 07 December 2007 22:31 Go to previous messageGo to next message
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 #286533 is a reply to message #286524] Fri, 07 December 2007 22:41 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I could be mistaken but it might be because INITRANS=1
Re: Different between ROLLBACK and COMMIT [message #286535 is a reply to message #286533] Fri, 07 December 2007 22:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #287521 is a reply to message #287518] Wed, 12 December 2007 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, but without any formatting it is not clear what you try to show.
As there is no timestamp, we don't know when each action is done in each session. Currently, it appears sequentially and so what did session1 is useless (lock table/rollabck).
In addition, we don't which session has which SID.

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Also some parts of your post appear as chinese characters (for me). Can you fix that.

Regards
Michel



Re: Different between ROLLBACK and COMMIT [message #287526 is a reply to message #287521] Wed, 12 December 2007 09:05 Go to previous messageGo to next message
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> 
Re: Different between ROLLBACK and COMMIT [message #287528 is a reply to message #287526] Wed, 12 December 2007 09:08 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but with column aligned.

Regards
Michel
Previous Topic: Assigning privilege
Next Topic: Query with Logminer
Goto Forum:
  


Current Time: Sat Dec 03 21:59:23 CST 2016

Total time taken to generate the page: 0.26968 seconds