Insert fail | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Insert fail

Hi there
I use the following stored procedure to increment a call ID. Here is my problem, the calling application is throwing an exception with the message "Primary key constraint ‘PK_CALL’ cannot insert duplicate key in object ‘Call’. However the stored procedure inserts a new row as a transaction. Can someone point out what I am doing wrong? This problem only occurs intermittently. There can be up to 30 instances of calling application running. The calling application is running on dual Processor system. CREATE PROC up_call_start (
@pserverchar(10),
@pport_numbertinyint,
@pstart_timedatetime,
@pcall_idint OUTPUT)
AS
BEGIN
DECLARE @tcall_idint,
@terrorint,
@trowcountint SET NOCOUNT ON
begin tran insert_call_tran SELECT @tcall_id =ISNULL(MAX(call_id) + 1, 1) FROM call SELECT @terror= @@error, @trowcount = @@rowcount IF @terror <> 0
begin
rollback tran insert_call_tran
RETURN (@terror)
end INSERT into call (call_id, server, port_number, start_time)
VALUES (@tcall_id, @pserver, @pport_number, @pstart_time) SELECT @terror= @@error, @trowcount = @@rowcount IF @terror <> 0
begin
rollback tran insert_call_tran
RETURN (@terror)
end commit tran insert_call_tran
SELECT @pcall_id = @tcall_id
RETURN (0) END GO
Many Thanks
Dipendra

There’s nothing in your call to guarantee transactional consistency. If you want to use ID columns like this, you will need to: 1. Create a seperate table to produce the ID field with an identity column. Just insert into that and capture the SCOPE_IDENTITY() each time. 2. Or, do the INSERT and SELECT in the same statement instead of using VALUES to insert.
— INSERT call(call_id, server, port_number, start_time)
— SELECT ISNULL(MAX(call_id) + 1, 1), @pserver, @pport_number, @pstart_time FROM call WITH(HOLDLOCK) Basically, what’s happening is the fact there is a very small amount of time between your select and the actual insert. If this were an extremely high transaction system, you would have a nightmare on your hands. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thanks derrickleggett for you reply. Is there any risk of deadlock using HOLDLOCK. Dipendra
HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction, so no rik of having a deadlock situation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>