concurrency issues on insert

[vadimg]

hmmmm... There has to be a good solution to this, but i can't find it. Hope you guys can help me.

Application inserts rows into a table. My code has to assign a new primary key to every row. Ideally the primary key is INTEGER, and every new row gets PK = max + 1.

I know i can use GUID's (newid())or IDENTITY for the primary key, but they are my last resort (for many reasons -- don't need to get into them now).

here is the problem

Table:

CREATE TABLE test (pk INTEGER PRIMARY KEY, v VARCHAR(10))

now there are 2 ways that i can think of to assign PKs:

1. max(pk) + 1 on insert
2. have a separate counter table with max(pk) value which gets +1 on every insert.

both have these problems, but for the description i'll go with # 2 -- counter table

CREATE TABLE counter(i INTEGER)
INSERT COUNTER SELECT 1

insert procedure:

CREATE PROCEDURE insert_test @value varchar(10)
AS
DECLARE @i INT

SET NOCOUNT ON
BEGIN TRANSACTION
        SELECT @i = i FROM counter
        UPDATE counter
        SET i = i + 1
        
        INSERT test (pk, v)
        SELECT @i, @value
COMMIT TRANSACTION
go

Load generation script -- runs the procedure 1000 times:

set nocount on
declare @i int
set @i = 1
while @i < 1000
begin
	exec insert_test 
	set @i = @i + 1
end

If you run load generation code from 3 different query analyzer windows, you'll get PK violation errors....
If transaction isolation level is raised to repeatable read or serializable, you'll get deadlocks.

What's a good solution?

 

[mrdenny]

The best method will be identity insert. That or raise the isolation level. The blocking is there for a reason. It's doing exactly what it's supose to.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[vadimg]

raising isolation level causes deadlocks.
guid's or identity are not an option (yet).

Blocking is not happening in this case (that's why PK violation errors). I mean, if i'm being locked out by something, my query is queued until the lock is released... That doesn't seem to happen here. i'm trying to understand why.

 

[SQLSister]

WEll you could run profiler to see exactly what is happening. Or you could put a table lock on the counter table until the item is committed. What it looks like to me just as a gues is that the select statements are returning the same number becasue the transaction is not locking the selcts just further row inserts. So when it tries to add the number in the second proceudre after waiting for the first to release the lock, it has the same number as the first one.

Questions about posting. See faq183-874

 

[SQLSister]

Meant to add that I understand that your company for whatever reason doesn't want to use identity fields but they really are the best and most efficient way to do this.

Questions about posting. See faq183-874

 

[vadimg]

thanks guys. i know this problem is easily solved by Guid's or Identity.

Now here's an interesting development.
I changed the procedure code update the value first, and this does not generate PK errors.... HMMMMMM... I'll think about this and maybe post the answer when i find it....

thoughts?

CREATE PROCEDURE insert_test @value varchar(10)
AS
DECLARE @i INT

SET NOCOUNT ON
BEGIN TRANSACTION
        UPDATE counter
        SET i = i + 1
        SELECT @i = i -1 FROM counter
        
        INSERT test (pk, v)
        SELECT @i, @value
COMMIT TRANSACTION
go

 

[donutman]

I have a VB app that requires (maybe because of poor design) that I have the PK prior to actually committing it to the table. My solution to the problem was to generate a table of several thousand PKs along with a date field for when the PK gets used. I call a SP that returns a PK randomly (using Order By NewID() ) drawn from the thousands and changes the date field from null to GetDate(). Code in that SP also checks to see if it needs to refill the table after a fair percentage of the PKs get used and removes used ones and adds new ones.
I admit this is a bit weird and would be inefficient in very high volume situations, but there was some sort of intermittent bug in SQL Server 7.0 and VB in regard to getting an Identity column value returned to VB. So my solution works quite nicely. If I were designing from scratch, I'd use the identity approach, but with a separate table of PKs so that I could get the appropriate value returned to the VB app prior to inserting the new row in the data table.
Why not make a test run of the identity approach and see if you get PK violations. Instead of updating the Counter table let it return the next Identity value inserted into it. I assume you have no reason to not use an Identity column in a separate table. Don't forget to use @@Scope_Identity().
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

We cross-posted. Very interesting, but I wouldn't rely on it. It sure looks like a timing issue rather than a logical correction.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vadimg]

karl,

agreed that it's probably a timing issue. But the results are 100% repeatable.... i'm still investigating.

thanks for an interesting solution.

As i've mentioned: GUIDs, and identity work! I know that. I tested them. But i can't use them.

but since everyone is suggesting them, i'm going to elaborate:

GUIDs
1.(most important) overhead on join, sort, compare performance.
2.there will be a clustered index on this PK. Large volume of inserts of newid() (which is random) will generate a ton of page splits, forwarded records, and other fun things that make index access crawl. I do not have a daily maintenance window to run defrags, that's why i want a sequential integer.
3.they are ugly.

IDENTITY
the lead dba in this company is screaming bloody murder whenever i mention identity as a solution for this because of identity replication issues that he's had to deal with for the past 5 years..... Now I don't know if they are real issues. He's competent, but he seems like an old dog refusing to learn new tricks, you know? But management trusts his judgement, and gave a 100% NO on identity.

 

[donutman]

My app had the issue of replication too, but now that I've got T1's between our locations I don't use replication. Do you have to worry about Inserts being performed at various sites (separate servers)? Or are the remote sites not allowed to add rows?
If not then the Counter table doesn't have to get replicated, so his reason doesn't cut it.
-Karl