Creating 2nd transaction form inside db ?

We have a table that holds last inserted key values for several tables. 
These keys are alphanumeric with a fixed format, so we can't just do Max +1 
on them.   So we have a procedure that gets the current key, increments it 
as necessary, updates the table and returns the key to the caller.. be it 
from TSQL or from app code.      This is fine when called from app code as 
we can control the connection used for the call so we don't get lock 
problems, but from TSQL it's currently a problem as it resides inside the 
transaction of the caller, so the updates don't happen until the caller 
transaction is complete.

Is there any way of telling the DB to use an atominc operation for a 
procedure ?  i.e. take a copy of the current connection and use the copy to 
perform an operation ?

Thanks.
-- 
Adrian Parker
Ingenuity At Work Ltd 

Adrian Parker ( XXXX@XXXXX.COM ) writes:

Not really correct. The update does happen, but row remains locked until 
the transaction commits. (Of course, if other processes that read this 
table, uses snapshot isolation, they will see the old value.)


The only way to do that is to have a loopback operation. That is, the
procedure would call a CLR procedure that makes a second connection to
retrieve the key. If you are using SQL 2000, that would have to be an
extended stored procedure.

I need to add that this is a solution that I want in no way to encourage.
It is not likely that it will scale well. There is also a question, what if 
a caller rolls back after having a got a value. Do you accept that that 
key will not be used?

-- 
Erland Sommarskog, SQL Server MVP,  XXXX@XXXXX.COM 

Books Online for SQL Server 2005 at
 http://www.**--****.com/ 
Books Online for SQL Server 2000 at
 http://www.**--****.com/ 

Erland, thanks for replying..






| Not really correct. The update does happen, but row remains locked until
| the transaction commits. (Of course, if other processes that read this
| table, uses snapshot isolation, they will see the old value.)

What I mean is that yes, it happens, but as we use read committed, no other 
process sees the updated value.  Also, some transactions are quite long and 
would cause horrible waits for the locks to be cleared.   Currently, we have 
to pre-allocate a range of keys to use inside the trans, but that is 
wasteful.

| The only way to do that is to have a loopback operation. That is, the
| procedure would call a CLR procedure that makes a second connection to
| retrieve the key. If you are using SQL 2000, that would have to be an
| extended stored procedure.

We're using 2000,  how do you create a second connection ?

Thanks
Adrian

Hi Adrian,
I'm not sure I better understand your issue, but how about the following 
structure:
DECLARE @vId varchar(20)
exec proc_getID(@vId)
Go
BEGIN TRAN
..
..
COMMIT TRAN

If all the statements are in one transaction, you can reference the 
following:
BEGIN TRAN
DECLARE @vId varchar(20)
exec proc_getID(@vId)

SAVE TRAN pt_rollback
..
..
--If it's necessary to rollback, you can call
ROLLBACK TRAN pt_rollback
COMMIT TRAN

Anyway, I'm interested in your control of your app. Could you tell me more 
about the logic and what is your intent to get the ID by starting a new 
connection?

If you want to ensure the ID's unique value, you can set the transaction 
Serializable.

Sincerely,
Charles Wang
Microsoft Online Community Support

> would cause horrible waits for the locks to be cleared.   Currently, we have
Adrian,

Why is it wasteful? Anyway, it sounds like message queues do exactly
what you are looking for. If you are on SQl Server 2005, use the
Broker.
Makes sense?

Charles,

We have transactions that can take several seconds to run (they're doing a 
lot of work).

Each transaction performs inserts into several tables, some of which 
required the production of an alphanumeric key.  These keys are held in a 
key_allocation table which holds the last key assigned, so for example 
TR000123 would be incremented to TR000124 and returned to the caller.

Because the transactions can be long, we don't want to call the routine to 
get the next key and leave that key_allocation record in an uncommited state 
as that would stop other processes from getting other keys from that record 
for inserts into their own transactions.  Because we don't know how many 
keys to obtain before we start the transaction, we would have to 
pre-allocate a lot keys to ensure we didn't run out during the transaction, 
which would be expensive on available keys.

begin tran
    get key1
    insert record into table1
    get key2
    insert record into table2
    lots of other work
commit

So by the time we got to the comit, several seconds could have gone by with 
key1 and key2 records still locked.

-Adrian






| Hi Adrian,
| I'm not sure I better understand your issue, but how about the following
| structure:
| DECLARE @vId varchar(20)
| exec proc_getID(@vId)
| Go
| BEGIN TRAN
| ..
| ..
| COMMIT TRAN
|
| If all the statements are in one transaction, you can reference the
| following:
| BEGIN TRAN
| DECLARE @vId varchar(20)
| exec proc_getID(@vId)
|
| SAVE TRAN pt_rollback
| ..
| ..
| --If it's necessary to rollback, you can call
| ROLLBACK TRAN pt_rollback
| COMMIT TRAN
|
| Anyway, I'm interested in your control of your app. Could you tell me more
| about the logic and what is your intent to get the ID by starting a new
| connection?
|
| If you want to ensure the ID's unique value, you can set the transaction
| Serializable.
|
| Sincerely,
| Charles Wang
| Microsoft Online Community Support
| 

Adrian Parker ( XXXX@XXXXX.COM ) writes:


As I said, you write an extended stored procedure (XP) which reconnects
to SQL Server. But XPs are not for the faint of heart. You have to write
them in C++ and what's really bad: if you XP crash because of for instance
an access violation, this brings down the entire SQL Server. So this is
definitely a way that I would encourage.

One alternative would be to pre-generate lots of keys, and any process
that needs a key would do something like:

   SELECT MIN(keyvalue)
   FROM   tbl WITH (READPAST)
   WHERE  takenby IS NULL

I think a better option would be to look into is to have seperate
series for the batch-oriented stuff.

-- 
Erland Sommarskog, SQL Server MVP,  XXXX@XXXXX.COM 

Books Online for SQL Server 2005 at
 http://www.**--****.com/ 
Books Online for SQL Server 2000 at
 http://www.**--****.com/ 

| Adrian Parker ( XXXX@XXXXX.COM ) writes:
| >| The only way to do that is to have a loopback operation. That is, the
| >| procedure would call a CLR procedure that makes a second connection to
| >| retrieve the key. If you are using SQL 2000, that would have to be an
| >| extended stored procedure.
| >
| > We're using 2000,  how do you create a second connection ?
|
|
| As I said, you write an extended stored procedure (XP) which reconnects
| to SQL Server. But XPs are not for the faint of heart. You have to write
| them in C++ and what's really bad: if you XP crash because of for instance
| an access violation, this brings down the entire SQL Server. So this is
| definitely a way that I would encourage.
|
| One alternative would be to pre-generate lots of keys, and any process
| that needs a key would do something like:
|
|   SELECT MIN(keyvalue)
|   FROM   tbl WITH (READPAST)
|   WHERE  takenby IS NULL
|
| I think a better option would be to look into is to have seperate
| series for the batch-oriented stuff.

Hmm.. I think we're going to have to bite the bullet and change the keys to 
use identity cols (huge job).

We have no idea how many new records are going to be created during some of 
the transactions, it could be a few or it could be thousands.. it depends on 
how complex the varying data is.

Thanks for trying to help !
-Adrian

I did this in a couple of applications with a routine in my application that 
handed out numbers as they were required.  This routine would get a bunch of 
numbers from the "LastNumberUsed" table by incrementing the value by some 
amount (I used 100 but made it tunable).  When the internal count reached 
the number in the database, I got 100 more.  Since only one thread actually 
updated the database, there were no contention issues.   Because this 
routine wasn't part of any transaction, there was no blocking.  The routine 
obviously used a critical section to deal with multi-threading issues but 
since it just incremented a number and returned it, there wasn't much 
overhead.  When the application shut down, I set the "LastNumberUsed" table 
to the last number I really used.  If the application crashed, I lost up to 
100 numbers but That wasn't an issue for me.  Identity wouldn't have worked 
for me but that's another story.  I don't know if this helps but I thought I 
would throw it in.

-- 
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
 http://www.**--****.com/ 

Dear Adrian,
Thanks for your email response. I got an idea and hope it's helpful to you.

This method requires a table to store the generated IDs. The table's 
structure is as following:
CREATE TABLE TheTable
(
  TheID int,                           -- computed value by your business 
rules
  TheRequestID uniqueidentifier  -- a random unique number representing 
each request
)

Assume that you have a stored procedure "addid" to generate the ID.
 
Then you can create a procedure for getting the ID like:
Create procedure usp_getID
 (@requestId uniqueidentifier, --represent a unique request 
@nId int output)
AS
declare @vstrAddId varchar(200)
SET @vstrAddId = 'osql /Sinstancename /E /Q"USE <databasename>;exec addid 
@requestno=''' + requestId + ''';"'
Exec xp_cmdshell @vstrAddId
Select @nId = TheID from TheTable Where TheRequestId = @requestId
DELETE TheTable WHERE TheRequestId=@requestId

If you have any other questions or concerns, please feel free to let me 
know. It's my pleasure to be of assistance.

Sincerely,
Charles Wang
Microsoft Online Community Support