Maximum number of transaction. (Newby question).

At the moment I am playing around with an Oracle database.

I have set The number of transactions for a test table
initial to 1 and maximum to 1.
I enter 3 small rows into a table.
(All rows end up in the same block, when selecting with rowid
all is the same except that the last digit becomes A or B or C).

Now I open a session and do an update on the first row.
I open a second session and do an update on the second row.

Both updates succeede and I can commit both.
Because of the only one transaction in a block I would expect that
one transaction would be blocked by the first.

What am I missing or understanding wrongly.

ben brugman

ben brugman wrote:

>At the moment I am playing around with an Oracle database.
>
>I have set The number of transactions for a test table
>initial to 1 and maximum to 1.
>I enter 3 small rows into a table.
>(All rows end up in the same block, when selecting with rowid
>all is the same except that the last digit becomes A or B or C).
>
>Now I open a session and do an update on the first row.
>I open a second session and do an update on the second row.
>
>Both updates succeede and I can commit both.
>Because of the only one transaction in a block I would expect that
>one transaction would be blocked by the first.
>
>What am I missing or understanding wrongly.
>
>ben brugman
>
>
>
You are assuming that your interpretation of MINTRANS and MAXTRANS is as
you
think it is. I'd suggest going to http://tahiti.oracle.com and
researching the subject more.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan-at-x.washington.edu
(replace 'x' with a 'u' to reply)

ben brugman wrote:

>At the moment I am playing around with an Oracle database.
>
>I have set The number of transactions for a test table
>initial to 1 and maximum to 1.
>I enter 3 small rows into a table.
>(All rows end up in the same block, when selecting with rowid
>all is the same except that the last digit becomes A or B or C).
>
>Now I open a session and do an update on the first row.
>I open a second session and do an update on the second row.
>
>Both updates succeede and I can commit both.
>Because of the only one transaction in a block I would expect that
>one transaction would be blocked by the first.
>
>What am I missing or understanding wrongly.
>
>ben brugman
>
>
>
You are assuming that your interpretation of MINTRANS and MAXTRANS is as
you
think it is. I'd suggest going to http://tahiti.oracle.com and
researching the subject more.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan-at-x.washington.edu
(replace 'x' with a 'u' to reply)

Hello Ben,

The behaviour you're observing is explained by Oracle 9i's creating
_two_ ITL slots instead of one despite your specifying 'initrans 1'.
It can be easily verified by dumping the data block.

If you perform an update #3 on row #3 in addition to the two you've
already initiated, you'll get the desired effect.

Rgds.


"ben brugman" wrote in message news:<3fa0f04d$0$245$4d4ebb8e@read.news.nl.uu.net>...
> At the moment I am playing around with an Oracle database.
>
> I have set The number of transactions for a test table
> initial to 1 and maximum to 1.
> I enter 3 small rows into a table.
> (All rows end up in the same block, when selecting with rowid
> all is the same except that the last digit becomes A or B or C).
>
> Now I open a session and do an update on the first row.
> I open a second session and do an update on the second row.
>
> Both updates succeede and I can commit both.
> Because of the only one transaction in a block I would expect that
> one transaction would be blocked by the first.
>
> What am I missing or understanding wrongly.
>
> ben brugman

Hello Ben,

The behaviour you're observing is explained by Oracle 9i's creating
_two_ ITL slots instead of one despite your specifying 'initrans 1'.
It can be easily verified by dumping the data block.

If you perform an update #3 on row #3 in addition to the two you've
already initiated, you'll get the desired effect.

Rgds.


"ben brugman" wrote in message news:<3fa0f04d$0$245$4d4ebb8e@read.news.nl.uu.net>...
> At the moment I am playing around with an Oracle database.
>
> I have set The number of transactions for a test table
> initial to 1 and maximum to 1.
> I enter 3 small rows into a table.
> (All rows end up in the same block, when selecting with rowid
> all is the same except that the last digit becomes A or B or C).
>
> Now I open a session and do an update on the first row.
> I open a second session and do an update on the second row.
>
> Both updates succeede and I can commit both.
> Because of the only one transaction in a block I would expect that
> one transaction would be blocked by the first.
>
> What am I missing or understanding wrongly.
>
> ben brugman

"VC" wrote in message
news:31e0625e.0310300645.4c908003-at-posting.google.c om...
> Hello Ben,
>
> The behaviour you're observing is explained by Oracle 9i's creating
> _two_ ITL slots instead of one despite your specifying 'initrans 1'.
> It can be easily verified by dumping the data block.
>
> If you perform an update #3 on row #3 in addition to the two you've
> already initiated, you'll get the desired effect.

This must be some new definition of the word 'desired' with which I am
unfamiliar.


--
Niall Litchfield
Oracle DBA
Audit Commission Uk

"VC" wrote in message
news:31e0625e.0310300645.4c908003-at-posting.google.c om...
> Hello Ben,
>
> The behaviour you're observing is explained by Oracle 9i's creating
> _two_ ITL slots instead of one despite your specifying 'initrans 1'.
> It can be easily verified by dumping the data block.
>
> If you perform an update #3 on row #3 in addition to the two you've
> already initiated, you'll get the desired effect.

This must be some new definition of the word 'desired' with which I am
unfamiliar.


--
Niall Litchfield
Oracle DBA
Audit Commission Uk

I forgot to say that , although you are correct, I think that for someone
who has 'begun playing around with an Oracle database' the statement that
"it can be easily verified by dumping the datablock" is a little bit
misleading. For what its worth I did the experiment you describe

create table test (id number,col1 char(10)) initrans 1 maxtrans 1;
insert 3 rows and commit;
session 1 update where id=1,
session 2 update where id=2,
session 3 update where id=3 -- this session hangs.

session 4 ! run

select header_file,header_block+1 from dba_segments where
segment_name='TEST';

get 1,52858 (oops created in system tablespace bad boy Niall).

run alter system dump datafile 1 block 52858; get the following in the trace
file

*** SESSION ID11.20) 2003-10-30 16:06:46.403

Start dump data blocks tsn: 0 file#: 1 minblk 52858 maxblk 52858

buffer tsn: 0 rdba: 0x0040ce7a (1/52858)

scn: 0x0000.13e26d0c seq: 0x01 flg: 0x00 tail: 0x6d0c0601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x0040ce7a

Object id on Block? Y

seg/obj: 0x7c54 csc: 0x00.13e26d0c itc: 2 flg: O typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01


Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0006.010.00000177 0x0080183b.0064.1e ---- 1 fsc 0x0000.00000000

0x02 0x0003.004.0000017b 0x00801dd0.0073.11 ---- 1 fsc 0x0000.00000000

data_block_dump,data header at 0x665105c

===============

tsiz: 0x1fa0

hsiz: 0x18

pbl: 0x0665105c

bdba: 0x0040ce7a

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f6d

avsp=0x1f55

tosp=0x1f55

0xeti[0] nrow=3 offs=0

0x12ri[0] offs=0x1f8f

0x14ri[1] offs=0x1f7e

0x16ri[2] offs=0x1f6d

block_row_dump:

****** etc etc

Which may or may not tell you a hell of a lot. Yes it does say that I have 2
interested transactions but it isnt the clearest file one has ever seen, nor
is it obvious that header_block+1 is the first datablock in a segment.

--
Niall Litchfield
Oracle DBA
Audit Commission Uk

"VC" wrote in message
news:31e0625e.0310300645.4c908003-at-posting.google.c om...
> Hello Ben,
>
> The behaviour you're observing is explained by Oracle 9i's creating
> _two_ ITL slots instead of one despite your specifying 'initrans 1'.
> It can be easily verified by dumping the data block.
>
> If you perform an update #3 on row #3 in addition to the two you've
> already initiated, you'll get the desired effect.
>
> Rgds.
>
>
> "ben brugman" wrote in message
news:<3fa0f04d$0$245$4d4ebb8e@read.news.nl.uu.net>...
> > At the moment I am playing around with an Oracle database.
> >
> > I have set The number of transactions for a test table
> > initial to 1 and maximum to 1.
> > I enter 3 small rows into a table.
> > (All rows end up in the same block, when selecting with rowid
> > all is the same except that the last digit becomes A or B or C).
> >
> > Now I open a session and do an update on the first row.
> > I open a second session and do an update on the second row.
> >
> > Both updates succeede and I can commit both.
> > Because of the only one transaction in a block I would expect that
> > one transaction would be blocked by the first.
> >
> > What am I missing or understanding wrongly.
> >
> > ben brugman

I forgot to say that , although you are correct, I think that for someone
who has 'begun playing around with an Oracle database' the statement that
"it can be easily verified by dumping the datablock" is a little bit
misleading. For what its worth I did the experiment you describe

create table test (id number,col1 char(10)) initrans 1 maxtrans 1;
insert 3 rows and commit;
session 1 update where id=1,
session 2 update where id=2,
session 3 update where id=3 -- this session hangs.

session 4 ! run

select header_file,header_block+1 from dba_segments where
segment_name='TEST';

get 1,52858 (oops created in system tablespace bad boy Niall).

run alter system dump datafile 1 block 52858; get the following in the trace
file

*** SESSION ID11.20) 2003-10-30 16:06:46.403

Start dump data blocks tsn: 0 file#: 1 minblk 52858 maxblk 52858

buffer tsn: 0 rdba: 0x0040ce7a (1/52858)

scn: 0x0000.13e26d0c seq: 0x01 flg: 0x00 tail: 0x6d0c0601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x0040ce7a

Object id on Block? Y

seg/obj: 0x7c54 csc: 0x00.13e26d0c itc: 2 flg: O typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01


Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0006.010.00000177 0x0080183b.0064.1e ---- 1 fsc 0x0000.00000000

0x02 0x0003.004.0000017b 0x00801dd0.0073.11 ---- 1 fsc 0x0000.00000000

data_block_dump,data header at 0x665105c

===============

tsiz: 0x1fa0

hsiz: 0x18

pbl: 0x0665105c

bdba: 0x0040ce7a

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f6d

avsp=0x1f55

tosp=0x1f55

0xeti[0] nrow=3 offs=0

0x12ri[0] offs=0x1f8f

0x14ri[1] offs=0x1f7e

0x16ri[2] offs=0x1f6d

block_row_dump:

****** etc etc

Which may or may not tell you a hell of a lot. Yes it does say that I have 2
interested transactions but it isnt the clearest file one has ever seen, nor
is it obvious that header_block+1 is the first datablock in a segment.

--
Niall Litchfield
Oracle DBA
Audit Commission Uk

"VC" wrote in message
news:31e0625e.0310300645.4c908003-at-posting.google.c om...
> Hello Ben,
>
> The behaviour you're observing is explained by Oracle 9i's creating
> _two_ ITL slots instead of one despite your specifying 'initrans 1'.
> It can be easily verified by dumping the data block.
>
> If you perform an update #3 on row #3 in addition to the two you've
> already initiated, you'll get the desired effect.
>
> Rgds.
>
>
> "ben brugman" wrote in message
news:<3fa0f04d$0$245$4d4ebb8e@read.news.nl.uu.net>...
> > At the moment I am playing around with an Oracle database.
> >
> > I have set The number of transactions for a test table
> > initial to 1 and maximum to 1.
> > I enter 3 small rows into a table.
> > (All rows end up in the same block, when selecting with rowid
> > all is the same except that the last digit becomes A or B or C).
> >
> > Now I open a session and do an update on the first row.
> > I open a second session and do an update on the second row.
> >
> > Both updates succeede and I can commit both.
> > Because of the only one transaction in a block I would expect that
> > one transaction would be blocked by the first.
> >
> > What am I missing or understanding wrongly.
> >
> > ben brugman

>
> This must be some new definition of the word 'desired' with which I am
> unfamiliar.
>


In trying to prevent certain events, it is always 'nice' that you can create
such
events.
Specific in lock and deadlock situations which occure rarely, it can be
helpfull
if you can reproduce the effect.

Lot of DBA's go and alter something until the 'event' does not happen
anymore.
(See a lot of books especially on describing the initrans setting, if a lot
of
locks or deadlocks occur increase this number).

This running a Oracle database by trial and error is not the way I am used
to
working with databases.

If something goes wrong I like to find what was at 'fault' and then try to
repair
the fault and not just alter something and hope for the best.

So reproducing errors, locks and deadlocks on command can be 'desired'

I'll still have to read your other mail with the 'dump' example. Although I
am
not familiar with the Oracle 'format' I'll try to learn from it. So thanks
for
producing the example.

THANKS,

ben brugman.


>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission Uk
>
>