Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to create "unique ID" that may appear in several rows

3 posts in General Discussion Last posting was on 2010-07-23 08:12:48.0Z
F.Schroedl Posted on 2010-07-22 15:52:09.0Z
Sender: 117b.4c486090.1804289383@sybase.com
From: F.Schroedl
Newsgroups: sybase.public.ase.general
Subject: How to create "unique ID" that may appear in several rows
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c486929.1338.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Jul 2010 08:52:09 -0700
X-Trace: forums-1-dub 1279813929 10.22.241.41 (22 Jul 2010 08:52:09 -0700)
X-Original-Trace: 22 Jul 2010 08:52:09 -0700, 10.22.241.41
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29387
Article PK: 78620

I'm using a openclient library connecting to sybase 12.1
server.

By changing the lock scheme of a table from page to data row
locking following mechanism doesn't work any longer: I have
to insert rows into a table with an unique identifier, but
several rows may have the same identifier for grouping these
rows
e.g. row1,row2,row3 are in group with ID1, row4,row5 are in
group with ID2
row1 ID1
row2 ID1
row3 ID1
row4 ID2
row5 ID2
With page locking I could do it within a transaction that
created an ID and then checked for existence of unique ID.
If ID didn't exist the rows are inserted with this ID.
begin tran
//get an ID
select ID=...
//check if ID already in use, if already exists try
another ID
SELECT 1 WHERE EXISTS (SELECT * FROM mytable WHERE
myID=ID))
insert into mytable ... (row1, ID)
insert into mytable ... (row2, ID)
commit tran

Now with data row locking two clients may create the same ID
simultaneously, the check doesn't recognize that the same ID
has been inserted in another transaction as long as
transaction is not committed. As result I have the same ID
used for different groupings.

Is there a possible solution to create such "group unique"
IDs ?
Changing back to page locking is beyond my influence :-(

Thanks for any advice - and forgive me if this issue is
off-topic for this group.


Bret Halford Posted on 2010-07-22 17:24:09.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.10) Gecko/20100512 Thunderbird/3.0.5
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to create "unique ID" that may appear in several rows
References: <4c486929.1338.1681692777@sybase.com>
In-Reply-To: <4c486929.1338.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c487eb9@forums-1-dub>
Date: 22 Jul 2010 10:24:09 -0700
X-Trace: forums-1-dub 1279819449 10.22.241.152 (22 Jul 2010 10:24:09 -0700)
X-Original-Trace: 22 Jul 2010 10:24:09 -0700, vip152.sybase.com
Lines: 65
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29388
Article PK: 78622


On 7/22/2010 9:52 AM, F.Schroedl wrote:
> I'm using a openclient library connecting to sybase 12.1
> server.
>
> By changing the lock scheme of a table from page to data row
> locking following mechanism doesn't work any longer: I have
> to insert rows into a table with an unique identifier, but
> several rows may have the same identifier for grouping these
> rows
> e.g. row1,row2,row3 are in group with ID1, row4,row5 are in
> group with ID2
> row1 ID1
> row2 ID1
> row3 ID1
> row4 ID2
> row5 ID2
> With page locking I could do it within a transaction that
> created an ID and then checked for existence of unique ID.
> If ID didn't exist the rows are inserted with this ID.
> begin tran
> //get an ID
> select ID=...
> //check if ID already in use, if already exists try
> another ID
> SELECT 1 WHERE EXISTS (SELECT * FROM mytable WHERE
> myID=ID))
> insert into mytable ... (row1, ID)
> insert into mytable ... (row2, ID)
> commit tran
>
> Now with data row locking two clients may create the same ID
> simultaneously, the check doesn't recognize that the same ID
> has been inserted in another transaction as long as
> transaction is not committed. As result I have the same ID
> used for different groupings.
>
> Is there a possible solution to create such "group unique"
> IDs ?
> Changing back to page locking is beyond my influence :-(
>
> Thanks for any advice - and forgive me if this issue is
> off-topic for this group.

Use a table with an IDENTITY column.

create table id_generator(id numeric(12,0) identity) lock datarows
go
-- optional: you may want to configure
-- the identity_gap value for the table now


To generate a new ID:

declare @newid numeric(12,0)
insert id_generator values (null)
select @newid = @@identity
delete id_generator where id = @newid

insert into mytable ... (row1, @newid)



-bret


F.Schroedl Posted on 2010-07-23 08:12:48.0Z
Sender: 117b.4c486090.1804289383@sybase.com
From: F.Schroedl
Newsgroups: sybase.public.ase.general
Subject: Re: How to create "unique ID" that may appear in several rows
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c494f00.122a.1681692777@sybase.com>
References: <4c487eb9@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Jul 2010 01:12:48 -0700
X-Trace: forums-1-dub 1279872768 10.22.241.41 (23 Jul 2010 01:12:48 -0700)
X-Original-Trace: 23 Jul 2010 01:12:48 -0700, 10.22.241.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29389
Article PK: 78623


>
> Use a table with an IDENTITY column.
>
> create table id_generator(id numeric(12,0) identity) lock
> datarows go
> -- optional: you may want to configure
> -- the identity_gap value for the table now
>
>
> To generate a new ID:
>
> declare @newid numeric(12,0)
> insert id_generator values (null)
> select @newid = @@identity
> delete id_generator where id = @newid
>
> insert into mytable ... (row1, @newid)
>
>
>
> -bret
>

Seems to be the perfect solution - thanks a lot