Select the just-inserted record with a composite Primary Key

Question

I have a table with a composite Primary Key, arranged something like this:

CREATE TABLE [dbo].[mytable]
(
    [some_id] [smallint] NOT NULL,
    [order_seq] [smallint] NOT NULL,
    -- etc...
)

Both of these columns are part of the primary key (it's actually a 4-part PK on the real table, but I've simplified it for the sake of the example). None of the columns are identities. I'm writing a stored proc that inserts a new record at the next order_seq for a given some_id:

CREATE PROCEDURE some_proc
(
    @some_id smallint,
    @newSeq smallint OUTPUT
)
AS
BEGIN
    insert into mytable (some_id, order_seq)
    values 
    (
         @some_id, 
         (select max(order_seq) + 1 from mytable where some_id = @some_id)
    )

    set @newSeq = /* order_seq of the newly-inserted row */
END

I need to know how to set @newSeq. I'd like to avoid running a select query after the insert, because I don't want to run into concurrency issues -- I'm prohibited from locking the table or using a transaction (don't ask).

As far as I know, I can't use SCOPE_IDENTITY() because none of the columns is an identity. How can I set newSeq correctly?

score 5 · Accepted Answer · answered Apr 28 '11 at 22:02

5

First, if the PK contains four columns, then each insert must include all four columns. Second, you could look into the Output clause if you are using SQL Server 2005+

Declare @NewSeqTable Table( Order_Seq int not null )

Insert MyTable( some_id, order_seq, otherPkCol, otherPkCol2 )
Output inserted.order_seq Into @NewSeqTable
Select @some_id, Max( order_seq ) + 1, otherPkCol, otherPkCol2
From MyTable
Where some_id = @some_id

Select Order_Seq
From @NewSeqTable

OUTPUT Clause (Transact-SQL)

answered Apr 28 '11 at 22:02

Thomas

63,911
12
95
141

Perfect. Just found the Output clause mentioned in [another SO question](http://stackoverflow.com/questions/810829/best-way-to-get-pk-guid-of-inserted-row), too. Thanks for your help. – Justin Morgan - On strike Apr 28 '11 at 22:32

score 2 · Answer 2 · answered Apr 28 '11 at 22:14

The answer here depends on the size/concurrency issues in your system. If you are UNSURE as to as to the concurrency issues assume access is multi-threaded.

Single Threaded

If you have small system or you can be sure that only one thread will touch this function at a time, then something like the following will work :

CREATE PROCEDURE some_proc ( @KeyPart1 smallint, @newSeq smallint OUTPUT ) 
AS

DECLARE @KeyPart1 int
DECLARE @KeyPart2 int


SET @KeyPart1 = (SELECT <whatever your logic is here>)
SET @KeyPart2 =  select max(order_seq) + 1 from mytable where some_id = @KeyPart1

insert into mytable (some_id, order_seq)
values  ( @KeyPart1, @KeyPart2 )

set @newSeq = @KeyPart2

Multi-Threaded Access

If you cannot be assured that only a single thread will access the proc, then you need a transaction in your code. From what you've shared, it appears that you will need a SERIALIZABLE transaction. SERIALIZABLE is the least concurrent (and most protective) transaction available in SQL Server. Since you do a read that identifies a max you'll need serializable to prevent phantom inserts that would alter the result.

Although you would likely want error handling, a procedure like the following should work....

CREATE PROCEDURE some_proc ( @KeyPart1 smallint, @newSeq smallint OUTPUT ) 
AS

DECLARE @KeyPart1 int
DECLARE @KeyPart2 int

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

SET @KeyPart1 = (SELECT <whatever your logic is here>)
SET @KeyPart2 =  select max(order_seq) + 1 from mytable where some_id = @KeyPart1

insert into mytable (some_id, order_seq)
values  ( @KeyPart1, @KeyPart2 )

set @newSeq = @KeyPart2

COMMIT TRAN

In a sane world I would be allowed to use transactions and this would solve my problem. Alas, it isn't and I'm not. :-/ — Justin Morgan - On strike, Apr 28 '11 at 22:31
@JustinMorgan -- been there (also had to work without Foreign keys!) However, it might not be all bad. In many cases where the --threat-- of a concurrency issue is low you'll get better performance by just trying the insert and trapping the error. Just make sure you (1) have a unique index on your PK, (2) trap the error and retry up [5?] times, and (3) throw an error if you really don't succeed. Also, rolling everything into a single SQL statement & using output will likely reduce your threat window. — EBarr, Apr 28 '11 at 23:10

score 1 · Answer 3 · answered Apr 28 '11 at 22:05

Unless I'm mistaken, you already have concurrency issues because of the "select max(order_seq) + 1 from mytable" statement. I'd say the problem as you posed it (being unable to lock or do transactions) isn't possible.

If order_seq weren't a smallint, I'd say generate a very large random number as your order_seq, and regenerate on the (presumably rare) insert exceptions. But that is an extreme fix for a basically unworkable situation.

The only other alternative (and I warn you it's bizarre) is to make a small dummy table that DOES have an identity column, and then basically copy that generated id into newSeq.

Yeah, I know the select raises a concurrency issue; unfortunately I'm a little hobbled by the existing table design and the lock/transaction prohibitions. The best I can do is hope to reduce the risk of conflicts as much as possible. The random order_seq is a clever idea, but it's supposed to be roughly sequential; it's going to be used in Order-By clauses later on. — Justin Morgan - On strike, Apr 28 '11 at 22:23

score 0 · Answer 4 · answered Apr 28 '11 at 22:02

0

Why don't you just assign to @newSeq first, then use the @newSeq variable in the insert?

answered Apr 28 '11 at 22:02

LJM

6,284
7
28
30

I thought of that, but it could cause a concurrency issue if the table changes between setting @newSeq and inserting a row. – Justin Morgan - On strike Apr 28 '11 at 22:19

Select the just-inserted record with a composite Primary Key

4 Answers4

Single Threaded

Multi-Threaded Access