SEO

Hello,

I have one table "message" 2 processes post data into.
To address concurrency issues, I have one table "counter" and one table
"get_counter".

Basically to post data, every process does this:
1- my_id = get_counter()
2- INSERT INTO message VALUES(my_id, my_message)

a. my_message is a TEXT variable. I cannot use a stored_procedure to do step
1 and 2 together, because ASE refuses TEXT variables as stored procedure
parameters.
b. Now my company, because of a kind of "holy" belief does not trust
transactions. I cannot use transactions to do step 1 and 2.
c. Last requirement, I want to know which my_id was used after the insertion
took place.

I came up with this solution. Executing the single query:
BEGIN
DECLARE @my_id INT
EXEC @my_id = get_counter()
INSERT INTO message VALUES(@my_id, my_message)
SELECT @my_id
END
and getting the result.

This mainly works. Because of database drivers problems, I am not always (ie
it fails on same machines) able to catch the variable selected by the query.
I know there are multiple recordsets, but some drivers just seem to fail in
this case.

I am running out of ideas, I do not know what to try next...
Any suggestsions?

Thanks in advance,

SerGioGioGio

Hey Sergio,

I guess you mean "explicitly defined" or "user transactions" because
transactions exist implicitly by definition ... they may be "chained"
(multiple DML statements per transaction) or "stand-alone" (each DML
statement is its own transaction). The constructs "transaction level"
and "set chained" control the type and scope of transactions as well as
the explicit commands "begin tran", "commit tran", and "rollback tran".


I assume that "my_id" is the PK of the "message" table ... You really
DO NEED a short, explicit transaction to guarantee the uniqueness of
the "my_id" column which can be something like the following (I'm
making an assumption of the structure of the "counter" table):

Begin Tran
Update counter set my_id = my_id + 1
select @my_id = my_id from counter
Commit Tran
return (@my_id)

I can't address why the drivers fail ... is it because of a "unique"
constraint?

Hope this rambling helps, Sergio!

Cheers,

Sara ...

"SerGioGio" <nospam> wrote:

> a. my_message is a TEXT variable. I cannot use a stored_procedure to do
> step 1 and 2 together, because ASE refuses TEXT variables as stored
> procedure parameters.

Is your text greater than, let say, 16000 characters ? If not, you can use a
varchar() datatype, and directly store it in your TEXT column.

This will only work on ASE 12.5 and later.

>
> I assume that "my_id" is the PK of the "message" table ... You really
> DO NEED a short, explicit transaction to guarantee the uniqueness of
> the "my_id" column which can be something like the following (I'm
> making an assumption of the structure of the "counter" table):
>
> Begin Tran
> Update counter set my_id = my_id + 1
> select @my_id = my_id from counter
> Commit Tran
> return (@my_id)

I have a get_counter stored procedure that does this. But I think you are
right, I really need explicit transactions here, when doing the INSERT. The
PK must be gapless.

>
> I can't address why the drivers fail ... is it because of a "unique"
> constraint?

The drivers fail to capture all the recordsets generated by a command.
For example, the query "BEGIN SELECT 1 SELECT 2 END" returns 2 recordsets,
and I can catch them both successfully.
But if I do
"BEGIN
DECLARE @my_id INT
EXEC @my_id = get_counter()
SELECT 1
END"
I cannot catch any recordset. I actually can using the ODBC Sybase drivers
4.50, but cannot with drivers 3.50, which are used by the production
machine...

>
> Hope this rambling helps, Sergio!
>

Thanks!

> Begin Tran
> Update counter set my_id = my_id + 1
> select @my_id = my_id from counter
> Commit Tran
> return (@my_id)

Usually, this can be converted in

UPDATE counter
SET my_id = my_id + 1, @my_id = my_id + 1

RETURN (@my_id)

A couple of notes:

I would suggest, based on your explanations, that you place the entire
insert (both the my_id retrieval and the insert) into a single
transaction. Here's why:

If you absolutely must guarantee uniqueness, then you have to somehow
prevent two processes from attemping to do an update at the same time;
i.e. process 1 reads from the counter table, and process 2 also reads
from the counter table, but does so before process 1 commits its
update. You then end up with both processes attempting to insert the
same value for my_id.

If you have uniqueness constraints on your message table, you would of
course encountered errors had this ever happened in the past. But if
you call your function and insert all within a single transaction, and
preface it all by locking the counter table in exclusive mode, you will
prevent any other process from accessing the counter table until the
transaction is committed or rolled back, thus guaranteeing unique
values.

Which provides a nice segue into the second issue... you mentioned your
my_id values must be gapless. If you create two seperate transactions
to get a my_id value and do the insert, you can't guarantee this. If
your update to counter commits, and then your insert fails for any
reason, you've just lost that my_id value and introduced a gap in your
pk values. By placing the entire thing into one transaction, if the
insert fails, the update to counter also gets rolled back, ensuring you
only 'burn' a value if the insert was successful.

Have you considered just placing the code to grab a value and update
the counters table within the larger transaction that also inserts,
instead of calling a stored procedure? This might help avoid your
result set issue.

I'm assuming this need to avoid gaps in the my_id column, and/or the
need to create a specific set of values or maybe add a check digit to a
unique value, are reasons you haven't considered using an identity
column here.

begin tran
declare @my_id int
lock table counter in exclusive mode
update counter set my_id = my_id + 1
select @my_id = my_id from counter
insert into message values (@my_id, my_message) (<-- wherever you
get the my_message value from)
select @my_id
end

I had to do something almost identical to this a while back for a
situation where we also could not use identity columns because we were
adding check digits.

-Mike

> I'm assuming this need to avoid gaps in the my_id column, and/or the
> need to create a specific set of values or maybe add a check digit to a
> unique value, are reasons you haven't considered using an identity
> column here.

Precisely. In addition, identity columns are not portable...

>
> begin tran
> declare @my_id int
> lock table counter in exclusive mode
> update counter set my_id = my_id + 1
> select @my_id = my_id from counter
> insert into message values (@my_id, my_message) (<-- wherever you
> get the my_message value from)
> select @my_id
> end
>

I do not understand the need for
> lock table counter in exclusive mode
In my understanding, the UPDATE statement is already locking the table.
Am I missing something?

> I had to do something almost identical to this a while back for a
> situation where we also could not use identity columns because we were
> adding check digits.
>
> -Mike
>

Thanks,

SerGioGioGio

SerGioGio wrote:
> > begin tran
> > declare @my_id int
> > lock table counter in exclusive mode
> > update counter set my_id = my_id + 1
> > select @my_id = my_id from counter
> > insert into message values (@my_id, my_message) (<-- wherever you
> > get the my_message value from)
> > select @my_id
> > end
> >
>
> I do not understand the need for
> > lock table counter in exclusive mode
> In my understanding, the UPDATE statement is already locking the table.
> Am I missing something?

No, you are correct, it does lock the table... when it happens.

Locking the table here guarantees* that you won't end up with a
duplicate number. Without this statement, you still might run the
possibility (albeit very small) of grabbing a duplicate value. If you
have very high activity, of course the probablility goes up.

It is probably even more remote that this would happen with this
particular transaction, however, as the update is the first real DML in
the transaction. In my situation, I had a few selects before the
actual update, and all of this was rolled into a proc. The proc was
called maybe 2-3000 times a day, and twice a week on average, we would
run into situations where both processes of the multi-threaded app
would fire the proc off so closely together that they would retrieve
the same value.

If this table has low activity, and/or there's no chance of two apps
executing this at the same time, and you're willing to assume the
remote risk that a duplicate happens, then you can probably leave out
the lock table statement. But since the update is designed to lock the
table anyway, the lock table statement carries no additional
performance impact or the possibility of blocking, and it guarantees
you a unique value, as opposed to 99.995% of the time. Depends on your
situation I guess.

It's been several years since we encountered this issue, so it's hard
to remeber the exact circumstances of what we were doing and how we
implemented it, but I do remember it being a situation almost identical
to yours. We too originally thought that the lock placed by the update
command was sufficient to avoid duplicates, but it turned out not to be
the case, as I mentioned.

This was our experience anyway... YMMV.

-Mike