May 3, 2010 at 12:50 pm
Hi everybody,
I inherited a SQL Server project, that contains a set of tables with primary ID fields, which (for several reasons) are not auto-incremented. Instead, the database includes an additional "Counters" table containing the currently allocated max-ids per table.
The ADO-clients "request" new counters/ids by incrementing the respective "Counters" table records, before using these numbers during the following record creations.
The current implementation uses LockOptimistic cursor recordsets to increment the counter records. In case of concurrent counter increment, the software catches update error -2147217864 (0x80040e38) to retry the operation until success.
This implementation ran fine for 6 years; retry operations occurred rarely.
Recently, the customer increased the number of ADO clients from 8 to roughly 30.
Now, I can trace a drastically increased number of retries, which slow down the client operation considerably.
Being new to SQLServer, I am looking for some mechanism to read/increment the Counters table without collisions. I assume, that even
UPDATE [<db>].[Counters] SET Value = Value + <count> WHERE Uniquename = <name>
may cause problems, if used concurrently by 30 clients ?
Additionally, this SQL statement does not return the modifed value.
I need some sort of (server based) "procedure", that
- receives parameters "table" and "required count"
- reads the corresponding Counters record and increments the value field by "required count"
- returns the PRE- or POST-change value to indicate the "reserved" id/count range
Is there any chance to implement such a procedure within the SQLserver environment ?
I'd really appreciate your opinion.
Thanks in advance
Peter
May 3, 2010 at 1:04 pm
stark 41816 (5/3/2010)
Hi everybody,I inherited a SQL Server project, that contains a set of tables with primary ID fields, which (for several reasons) are not auto-incremented. Instead, the database includes an additional "Counters" table containing the currently allocated max-ids per table.
The ADO-clients "request" new counters/ids by incrementing the respective "Counters" table records, before using these numbers during the following record creations.
The current implementation uses LockOptimistic cursor recordsets to increment the counter records. In case of concurrent counter increment, the software catches update error -2147217864 (0x80040e38) to retry the operation until success.
This implementation ran fine for 6 years; retry operations occurred rarely.
Recently, the customer increased the number of ADO clients from 8 to roughly 30.
Now, I can trace a drastically increased number of retries, which slow down the client operation considerably.
Being new to SQLServer, I am looking for some mechanism to read/increment the Counters table without collisions. I assume, that even
UPDATE [<db>].[Counters] SET Value = Value + <count> WHERE Uniquename = <name>
may cause problems, if used concurrently by 30 clients ?
Additionally, this SQL statement does not return the modifed value.
I need some sort of (server based) "procedure", that
- receives parameters "table" and "required count"
- reads the corresponding Counters record and increments the value field by "required count"
- returns the PRE- or POST-change value to indicate the "reserved" id/count range
Is there any chance to implement such a procedure within the SQLserver environment ?
I'd really appreciate your opinion.
Thanks in advance
Peter
Does the following code help you with this problem?
create table dbo.Counters (
UniqueName varchar(32),
Value int
);
insert into dbo.Counters
select 'Table1', 0 union all
select 'Table2', 0 union all
select 'Table3', 0;
select * from dbo.Counters;
declare @NewValue int;
update dbo.Counters set
@NewValue = Value = Value + 1
where
UniqueName = 'Table1';
select @NewValue;
select * from dbo.Counters;
May 3, 2010 at 2:40 pm
Hi Lynn,
thank you for your fast reply.
Looking at your code, you use async sql operations to update and re-read:
update ....
select ....
Without locking, what happens if several clients run this code at the same time ?
At the moment we use client cursor LockOptimistic recordsets, which means, we open/read a recordset with current values and update immediately the incremented value.
Since the clients access via network, the time interval between recordset open and update is large enough to allow other clients to do the same thing.
In this case, the first client wins, all others updates result in error 0x80040e38 (since the recordset had been modified inbetween).
The problem is the ADO client cursor LockOptimistic mode.
This lock mode locks the update operation only.
During the open/read operation, the record is not locked.
Thus, if 3 clients try to increment simultaneously, they all can open/read the record, but only the first updater succeeds.
Since these clients access via network, there is no chance to prevent those collisions.
My hope was, that a stored procedure or function, running on the server itself, would be able to perform the task.
The proc or function would have to run "non-interupted", otherwise the problem would just be moved from the ADO-clients to the server.
Greetings
Peter
May 3, 2010 at 2:54 pm
stark 41816 (5/3/2010)
Hi Lynn,thank you for your fast reply.
Looking at your code, you use async sql operations to update and re-read:
update ....
select ....
Without locking, what happens if several clients run this code at the same time ?
At the moment we use client cursor LockOptimistic recordsets, which means, we open/read a recordset with current values and update immediately the incremented value.
Since the clients access via network, the time interval between recordset open and update is large enough to allow other clients to do the same thing.
In this case, the first client wins, all others updates result in error 0x80040e38 (since the recordset had been modified inbetween).
The problem is the ADO client cursor LockOptimistic mode.
This lock mode locks the update operation only.
During the open/read operation, the record is not locked.
Thus, if 3 clients try to increment simultaneously, they all can open/read the record, but only the first updater succeeds.
Since these clients access via network, there is no chance to prevent those collisions.
My hope was, that a stored procedure or function, running on the server itself, would be able to perform the task.
The proc or function would have to run "non-interupted", otherwise the problem would just be moved from the ADO-clients to the server.
Greetings
Peter
You put the code into a stored proc, each client that calls the proc will get a unique value in return. The UPDATE will lock the row during the update and should provide the necessary fast lock/unlock needed to support numerous connections.
If there are multiple requests at the same time, the first will lock the appropriate record and the others will be blocked until one of them can gain access.
May 3, 2010 at 3:11 pm
Lynn Pettis (5/3/2010)
stark 41816 (5/3/2010)
Hi Lynn,thank you for your fast reply.
Looking at your code, you use async sql operations to update and re-read:
update ....
select ....
Without locking, what happens if several clients run this code at the same time ?
At the moment we use client cursor LockOptimistic recordsets, which means, we open/read a recordset with current values and update immediately the incremented value.
Since the clients access via network, the time interval between recordset open and update is large enough to allow other clients to do the same thing.
In this case, the first client wins, all others updates result in error 0x80040e38 (since the recordset had been modified inbetween).
The problem is the ADO client cursor LockOptimistic mode.
This lock mode locks the update operation only.
During the open/read operation, the record is not locked.
Thus, if 3 clients try to increment simultaneously, they all can open/read the record, but only the first updater succeeds.
Since these clients access via network, there is no chance to prevent those collisions.
My hope was, that a stored procedure or function, running on the server itself, would be able to perform the task.
The proc or function would have to run "non-interupted", otherwise the problem would just be moved from the ADO-clients to the server.
Greetings
Peter
You put the code into a stored proc, each client that calls the proc will get a unique value in return. The UPDATE will lock the row during the update and should provide the necessary fast lock/unlock needed to support numerous connections.
If there are multiple requests at the same time, the first will lock the appropriate record and the others will be blocked until one of them can gain access.
I'm using this code in a prod environement with a couple 1000s document # being generated daily on the server and I've not had a single problem (40 concurrent users and 100 different types of document #). We're talking about maybe 200-500 documents # per hour.
For my site only, I've had 16898 executions in the last few weeks with an average execution time of 1ms. So you can imagine how hard it is to have this fail with multiple users.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply