Hfrxdjt

A seasonal riddle

"Marked down as someone wanting to sell shares." What does that mean?

Error in master's thesis, I do not know what to do

Do native speakers use "ultima" and "proxima" frequently in spoken English?

Why would five hundred and five same as one?

Why is participating in the European Parliamentary elections used as a threat?

Friend wants my recommendation but I don't want to give it to him

Weird lines in Microsoft Word

Reasons for having MCU pin-states default to pull-up/down out of reset

What is the tangent at a sharp point on a curve?

Why is indicated airspeed rather than ground speed used during the takeoff roll?

Did I make a mistake by ccing email to boss to others?

What is it called when someone votes for an option that's not their first choice?

Exposing a company lying about themselves in a tightly knit industry (videogames) : Is my career at risk on the long run?

I keep switching characters, how do I stop?

How are passwords stolen from companies if they only store hashes?

Can you take a "free object interaction" while incapacitated?

What do the positive and negative (+/-) transmit and receive pins mean on Ethernet cables?

Offset in split text content

PTIJ: Which Dr. Seuss books should one obtain?

What (if any) is the reason to buy in small local stores?

Put the phone down / Put down the phone

How would a solely written language work mechanically

Showing mass murder in a kid's book

Checking @@ROWCOUNT failing

Using SSIS to get record counts for tables that may not existHandling exceptions in stored procedures called using insert-exec blocksSQL 2005 Unused proceduresDuplicate records in primary key during big selectHandling exceptions with INSERT-EXEC with linked server stored proc and table resultsBulk insert and fast ordered search large data 3B+ recordsExpensive Insert Operation pause Other Insert OperationProblems with insert trigger and primary keyImplicit conversion causing error part of the timeINSERT/SELECT xml column from one table to another

1

I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
 insert into table1(id, value1,value2...)
 select (@id, @value1, @value2...)
 end 

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?

sql-server primary-key

share|improve this question

asked 2 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  5
  

  
  
  
  
  
  

  
  
  Have you tried wrapping this code inside a transaction? I'm wondering if some other process is sliding in between the update and the insert.
  
  – Scott Hodgin
  2 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  There is no transaction. My proc is like below, create procedure my_procname AS BEGIN set nocount on -- sp code here as in the question end
  
  – blue
  2 hours ago
  

  
  
  
  

add a comment | 

1

I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
 insert into table1(id, value1,value2...)
 select (@id, @value1, @value2...)
 end 

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?

sql-server primary-key

share|improve this question

asked 2 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  5
  

  
  
  
  
  
  

  
  
  Have you tried wrapping this code inside a transaction? I'm wondering if some other process is sliding in between the update and the insert.
  
  – Scott Hodgin
  2 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  There is no transaction. My proc is like below, create procedure my_procname AS BEGIN set nocount on -- sp code here as in the question end
  
  – blue
  2 hours ago
  

  
  
  
  

add a comment | 

I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
 insert into table1(id, value1,value2...)
 select (@id, @value1, @value2...)
 end 

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?

sql-server primary-key

share|improve this question

asked 2 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
 insert into table1(id, value1,value2...)
 select (@id, @value1, @value2...)
 end 

share|improve this question

asked 2 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 2 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 hours ago

blueblue

1061

1 Answer
1

active

oldest

votes

5

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

blue is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232558%2fchecking-rowcount-failing%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

5

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

add a comment | 

5

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

add a comment | 

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490