Creating 2nd transaction form inside db ?

4 Sep 2006 11:47 AM

Adrian Parker

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

4 Sep 2006 10:33 PM

Erland Sommarskog

Adrian Parker (apparker@nospam.nospam) writes:

> 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.

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.)

> 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 ?

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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

5 Sep 2006 7:47 AM

Adrian Parker

Erland, thanks for replying..

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns983559DF6D80Yazorman@127.0.0.1...
| 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

5 Sep 2006 1:39 PM

Charles Wang[MSFT]

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

5 Sep 2006 7:47 PM

Adrian Parker

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

Show quote

5 Sep 2006 1:47 PM

Alexander Kuznetsov

> 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.
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?

5 Sep 2006 10:23 PM

Erland Sommarskog

Adrian Parker (apparker@nospam.nospam) 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.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

5 Sep 2006 10:33 PM

Adrian Parker

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns98363FCF5923Yazorman@127.0.0.1...
| Adrian Parker (apparker@nospam.nospam) 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

6 Sep 2006 12:10 AM

Roger Wolter[MSFT]

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.microsoft.com/info/cpyright.htm

Show quote

"Adrian Parker" <apparker@nospam.nospam> wrote in message
news:%23LfCStT0GHA.4408@TK2MSFTNGP05.phx.gbl...
> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message
> news:Xns98363FCF5923Yazorman@127.0.0.1...
> | Adrian Parker (apparker@nospam.nospam) 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
>
>
>
>
>

11 Sep 2006 1:08 PM

Charles Wang[MSFT]

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