transaction isolation

I think I see incorrect behaviour in the processing of transactions by a reasonably well-known relational database product, especifically in showing the contents of one transaction within another transaction environment. I 'd like to know whether you agree that this behaviour is not what it should be, that is: not ANSI-compatible. If you think it is ANSI-compatible, could you please explain where I have gone astray?

This is what I have done:

In session 1:

	Create a very simple table:
	create table test


	(id   numeric not null primary key


	description  varchar not null);
	
	and make it permanent:
	
	commit work; 


	(this is already surprising)



	Fill this table with two rows:
	
	insert into test
	values (1
	       ,'Something');
	
	insert into test
	values (2
	       ,'Another thing');
	
	Query these rows:
	
	select * from test:
	
	This returns:
	            ID DESCRIPTION
	            -- -----------
	            1. Something
	            2. Another thing


	(which is correct, as far as I can see)

Now in session 2:  

        Repeat the inserts:

	insert into test
	values (1
	       ,'Something');

	insert into test
	values (2
	       ,'Another thing');

	These produce a message: "Database Error 10033: Primary key unique constraint
violation".


	(again, this is what I expected)



	Now query the table:

	select * from test;
	
	This returns:
	            ID DESCRIPTION
	            -- -----------



	(this is correct, as the rows in session 1 whete not committed yet)

Return to session 1:

        Commit the two rows added:

        commit work;

Back to session 2:

        Query the table again:

        select * from test;

	This returns:
	            ID DESCRIPTION
	            -- -----------


	(this is not correct, as the rows were committed in session 1!)



	Now commit the work in session 2:

	commit work;

	And query the table again:
	
	select * from test;

	This results in:
	            ID DESCRIPTION
	            -- -----------
	            1. Something
	            2. Another thing



	(which is correct, but too late as far as I can see)

My conclusion is that rows that are committed are not correctly shown in another session.

Thanks in advance for any remarks,

Ruud.

-- 
--------------------------------------------------------------------------------------
Ruud de Koter                    HP OpenView Software Business Unit
Senior Software Engineer         IT Service Management Operation
Telephone: +31 (20) 514 15 89    Van Diemenstraat 200  
Telefax  : +31 (20) 514 15 90    PO Box 831
Telnet   : 547 - 1589            1000 AV  Amsterdam, the Netherlands
Email    : ruud_dekoter_at_hp.com

internet: http://www.openview.hp.com/itsm
          http://www.openview.hp.com/assetview
intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------------