Ttykuu

What properties make a magic weapon befit a Rogue more than a DEX-based Fighter?

Comic-book: Kids find a dead female superhero in the woods

Why doesn't Gödel's incompleteness theorem apply to false statements?

Has the laser at Magurele, Romania reached the tenth of the Sun power?

Make a Bowl of Alphabet Soup

Toggle window scroll bar

A seasonal riddle

Why didn't Voldemort know what Grindelwald looked like?

Not hide and seek

I keep switching characters, how do I stop?

Reason why a kingside attack is not justified

Relations between homogeneous polynomials

Center page as a whole without centering each element individually

Why didn’t Eve recognize the little cockroach as a living organism?

Magnifying glass in hyperbolic space

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

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

Why would five hundred and five same as one?

Calculate Pi using Monte Carlo

Mortal danger in mid-grade literature

What is this high flying aircraft over Pennsylvania?

What should be the ideal length of sentences in a blog post for ease of reading?

Do people actually use the word "kaputt" in conversation?

Weird lines in Microsoft Word

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 3 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
  3 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 3 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
  3 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 3 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 3 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 3 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 3 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 3 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');

var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);

;
$window.on('scroll', onScroll);

);

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