Mrthdrb

Is there a RAID 0 Equivalent for RAM?

If A is dense in Q, then it must be dense in R.

Do I have to take mana from my deck or hand when tapping a dual land?

Unable to disable Microsoft Store in domain environment

In One Punch Man, is King actually weak?

How to write Quadratic equation with negative coefficient

Isometric embedding of a genus g surface

Showing mass murder in a kid's book

Should I warn a new PhD Student?

Is there a reason to prefer HFS+ over APFS for disk images in High Sierra and/or Mojave?

The Digit Triangles

Deciphering cause of death?

PTIJ: does fasting on Ta'anis Esther give us reward as if we celebrated 2 Purims? (similar to Yom Kippur)

How do you justify more code being written by following clean code practices?

ContourPlot — How do I color by contour curvature?

Ways of geometrical multiplication

If the only attacker is removed from combat, is a creature still counted as having attacked this turn?

Why is the principal energy of an electron lower for excited electrons in a higher energy state?

Why do Radio Buttons not fill the entire outer circle?

Can I say "fingers" when referring to toes?

Air travel with refrigerated insulin

If Captain Marvel (MCU) were to have a child with a human male, would the child be human or Kree?

Pre-Employment Background Check With Consent For Future Checks

How to leave product feedback on macOS?

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

3

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 locking primary-key

share|improve this question

edited 7 hours ago

Aaron Bertrand♦

153k18296492

asked yesterday

blueblue

1192

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.

add a comment | 

3

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 locking primary-key

share|improve this question

edited 7 hours ago

Aaron Bertrand♦

153k18296492

asked yesterday

blueblue

1192

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.

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 locking primary-key

share|improve this question

edited 7 hours ago

Aaron Bertrand♦

153k18296492

asked yesterday

blueblue

1192

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

edited 7 hours ago

Aaron Bertrand♦

153k18296492

asked yesterday

blueblue

1192

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

edited 7 hours ago

Aaron Bertrand♦

153k18296492

asked yesterday

blueblue

1192

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.

edited 7 hours ago

Aaron Bertrand♦

153k18296492

edited 7 hours ago

Aaron Bertrand♦

153k18296492

asked yesterday

blueblue

1192

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 yesterday

blueblue

1192

1 Answer
1

active

oldest

votes

13

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 yesterday

Aaron Bertrand♦Aaron Bertrand

153k18296492

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Appreciate your answers! How about using IF EXISTS (select top 1 1 from table1 where id @id) then update and else Insert?
  
  – blue
  10 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @blue that pattern requires an extra access to the table (in the case where it exists). IF EXISTS essentially does the exact same thing the update does to try to locate the row - why do that twice?
  
  – Aaron Bertrand♦
  8 hours ago
  

  
  
  
  

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

13

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 yesterday

Aaron Bertrand♦Aaron Bertrand

153k18296492

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Appreciate your answers! How about using IF EXISTS (select top 1 1 from table1 where id @id) then update and else Insert?
  
  – blue
  10 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @blue that pattern requires an extra access to the table (in the case where it exists). IF EXISTS essentially does the exact same thing the update does to try to locate the row - why do that twice?
  
  – Aaron Bertrand♦
  8 hours ago
  

  
  
  
  

add a comment | 

13

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 yesterday

Aaron Bertrand♦Aaron Bertrand

153k18296492

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Appreciate your answers! How about using IF EXISTS (select top 1 1 from table1 where id @id) then update and else Insert?
  
  – blue
  10 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @blue that pattern requires an extra access to the table (in the case where it exists). IF EXISTS essentially does the exact same thing the update does to try to locate the row - why do that twice?
  
  – Aaron Bertrand♦
  8 hours ago
  

  
  
  
  

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 yesterday

Aaron Bertrand♦Aaron Bertrand

153k18296492

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 yesterday

Aaron Bertrand♦Aaron Bertrand

153k18296492

answered yesterday

Aaron Bertrand♦Aaron Bertrand

153k18296492

answered yesterday

Aaron Bertrand♦Aaron Bertrand

153k18296492