RE: ODS Concurrency question
I've done more experimenting. My table is receiving inserts from a
trigger. My table also has a serial column. The trigger wants to use the
serial value that was just inserted and, because a trigger is not permitted
to have variables, the trigger is using a subquery with an aggregate
function select max(seq)...where <cond>. This is what is blocking all
subsequent users.
Without that subquery, my user transactions do get interleaved. The unique
index and serial columns have no affect on concurrency.
Thanks for all your help,
--
wanda
spal@scotch.den.csci.csc.com (Sujit Pal) on 08/28/97 02:41:08 PM
To: dtaylor@octa.net ("'Diane Taylor'") @ smtp
cc: wbeck@sybase.com> \\"'junet@informix.com'\\"
<junet@informix.com> \\"'wbeck@sybase.com'\\"" <wbeck@sybase.com
("\\"'informix-list@rmy.emory.edu'\\") @ smtp (bcc: Wanda Beck/SYBASE)
Subject: RE: ODS Concurrency question
Diane
Absolutely right. I just tried this and look what I got.
create table sltest (
slid serial,
sldesc char(8));
insert into sltest values (0, "AAAAAAAA");
insert into sltest values (0, "BBBBBBBB");
insert into sltest values (4, "CCCCCCCC");
insert into sltest values (10, "DDDDDDDD");
insert into sltest values (4, "EEEEEEEE");
select * from sltest; slid sldesc
1 AAAAAAAA
2 BBBBBBBB
4 CCCCCCCC
10 DDDDDDDD
4 EEEEEEEE
Also I tried two concurrent processes inserting multiple rows into a single
table within a transaction and the data from the 2 processes WERE
interleaved (as seen from the inserted timestamp as well as rowid). The
lock mode on the table was ROW and I tried with both with and without a
unique index on the SERIAL column.
My mistake. I guess one keeps learning all the time, huh?
Thanks for your input.
Sujit Pal
----------
From: Diane Taylor[SMTP:dtaylor@octa.net]
Sent: Thursday, August 28, 1997 9:46 AM
To: 'informix forum'
Subject: <No Subject Supplied>
There is a thread running regarding serial numbers.
I believe there is a misconception that serial numbers assume uniqueness.
Where this is logical, I do not believe it is correct. When using serial
values you must set a unique index or constraint. For instance, I could
manually add a duplicate number.
Informix assigns a serial value based upon the highest last value. If you
have the values 1, 4, 100 the next value Informix assigns will be 101. By
using integer values in a load, I could assign any number. Again, Informix
would pick up it's assignment based on the highest value.
I have a programmer that uses serial values heavily because an Informix
techy told him they were faster. Personally, I think they are much more
trouble than any performance you gain. I mean, let's get a little bit
twiddly......