SEO

HI All,
I have got struck in a situation where 2 or more processes appear
simultaneously read the data from a table and each one of them insert
a new row into the same table. The table has column, similar to an
identity. The query gets the max of the column adds one to it and then
inserts it.
When parallel quering is happening then the column will have an non
unique values which is not expected.
Please note that, there is already a unique clustered index on some
other column and identity column cannot be used in this scenario.

So i was looking for some support from sybase where i can allow only
one select operation at one time, gets the new value by adding one and
then inserting it into the table. Then allow others one by one in a
queue. By doing this it will sure that there are no duplicate values
in the column.

Please let me know is there any way of achieving this.

BR.//
-Zia

Zia wrote:

> HI All,
> I have got struck in a situation where 2 or more processes appear
> simultaneously read the data from a table and each one of them insert
> a new row into the same table. The table has column, similar to an
> identity. The query gets the max of the column adds one to it and then
> inserts it.
> When parallel quering is happening then the column will have an non
> unique values which is not expected.
> Please note that, there is already a unique clustered index on some
> other column and identity column cannot be used in this scenario.

> So i was looking for some support from sybase where i can allow only
> one select operation at one time, gets the new value by adding one and
> then inserting it into the table. Then allow others one by one in a
> queue. By doing this it will sure that there are no duplicate values
> in the column.

> Please let me know is there any way of achieving this.

> BR.//
> -Zia



Hi,

Have a look at "isolation level" in the SQL Utility Guide. By default, it
is set to 1, you should launch your stored procedure with the level 3
(SQL92 default) to meet your needs.

BR,
Laurent

moh_muj@yahoo.com (Zia) wrote in
news:f5db6e5c.0310302009.5d545c12@posting.google.c om:

> HI All,
> I have got struck in a situation where 2 or more processes appear
> simultaneously read the data from a table and each one of them
> insert a new row into the same table.

Depending on the version of Sybase you have, you can solve this one of
two ways:

1) Use the IDENTITY type to generate the next ID
2) UPDATE my_table SET @my_val = max(id)+1, id = max(id) + 1 ...
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com

Hi,
>
> Have a look at "isolation level" in the SQL Utility Guide. By default, it
> is set to 1, you should launch your stored procedure with the level 3
> (SQL92 default) to meet your needs.
>
> BR,
> Laurent

Hi Laurent,

I thaught of of using isolation level 2, which in turn use isolation
level 3 since we have all pages locked tables. But this will also
allow select on tables but prevent updates or deletes. Since multiple
selects are permitted, as i understand from the sybase documents i
don't think i can try this.
Please Correct me if iam wrong.

Solution??? Still thinking of it

Thanks and BR.//
-Zia

moh_muj@yahoo.com (Zia) wrote in message news:<f5db6e5c.0310302009.5d545c12@posting.google. com>...
> HI All,
> I have got struck in a situation where 2 or more processes appear
> simultaneously read the data from a table and each one of them insert
> a new row into the same table. The table has column, similar to an
> identity. The query gets the max of the column adds one to it and then
> inserts it.
> When parallel quering is happening then the column will have an non
> unique values which is not expected.
> Please note that, there is already a unique clustered index on some
> other column and identity column cannot be used in this scenario.
>
> So i was looking for some support from sybase where i can allow only
> one select operation at one time, gets the new value by adding one and
> then inserting it into the table. Then allow others one by one in a
> queue. By doing this it will sure that there are no duplicate values
> in the column.
>
> Please let me know is there any way of achieving this.
>
> BR.//
> -Zia

I'll call the column you are referring to "id". If you can stand to
look up the id, add one (or whatever) to it, and perform the insert,
all in one motion, enclose the whole thing inside a transaction (if
you're not already), and perform a "LOCK TABLE <db>..<table> in
exclusive mode" as the first line of the transaction. This will
prevent other selects while this transaction is processing.

This will work, but if you are encountering this on a regular basis,
then it's possible that the table has too much activity and you would
create too many blocking problems.. this all depends on your server
activity. If it's a simple transaction it shouldn't affect
performance much though.

A solution I've seen for higher activity tables is to create a new
table with a "seed" column. A stored proc is created, using the above
LOCK TABLE logic, that retrieves the number from this "seed" table and
updates it to the next available value. This takes the initial select
away from the table you're concerned with, and the update can take
place at the application's leisure since it knows it has a unique id
to use. This causes a small amount of extra I/O (one extra update to
the seed table), but it takes this transaction away from the main
table and locks it up for only the insert, which is no more than how
much it's being locked now. Only these insert transactions would find
themselves contending for the "seed" table. Add an "id type" column
to the seed table, and you can use a single seed table to drive unique
id inserts for any number of tables.

I use the former approach since I have a table that is not frequently
used (~ 10 inserts/min at the most) so it doesn't slow it down at all.
You can give it a try and monitor the Lock Page Contention Context
Switches in sp_sysmon; if this percentage jumps up from a
pre-implementation benchmark, you may have too much activity on the
table and should try the second approach.

You might wish to read this white paper from Sybase.
http://www.sybase.com/detail?id=860