Basic locking question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Basic locking question

Author

Topic

jhoge123
Starting Member

8 Posts

Posted - 2007-02-07 : 06:54:09

Forgive me if this is too basic.

I have a sproc that records results from an email campaign:

create proc usp_EmailOpenLogInsert(@email varchar(255),@campaign varchar(255))
as
if not exists(select email from emailOpenLog where email = @email and campaign = @campaign)
insert EmailOpenLog(email,campaign) values(@email,@campaign)
else
update EmailOpenLog set openCount = openCount + 1 where email = @email and campaign = @campaign

Pretty simple, right.

Email and Campaign are the primary key fields, so I want to check to see if a record exists before inserting. If ther eis one, I just add to the openCount field.

Somehow I'm getting primary key violation errors in my logs, so I think I need to study a bit of locking.

Any hints on the best way to get around this?

Thanks,
John

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-07 : 07:28:08

You can make use of Transactions to avoid such situation.

create proc usp_EmailOpenLogInsert
(@email varchar(255),@campaign varchar(255)) 
as 
begin
begin transaction

if not exists(select email from emailOpenLog where email = @email and campaign = @campaign) 
	insert EmailOpenLog(email,campaign) values(@email,@campaign) 
else 
	update EmailOpenLog set openCount = openCount + 1 where email = @email and campaign = @campaign

if @@error <> 0
	Rollback Transaction
Else
	Commit Transaction

End

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jhoge123
Starting Member

8 Posts

Posted - 2007-02-12 : 13:25:19

Harsh,

I'm not sure the transaction is the way to go. I would rather use a technique that will eliminate the chance of errors than one which handles them after the fact.

If the two statements in the query can be guaranteed to run in sequence with no modification of the table, there could theoretically be no primary key duplication.

What I really want to do is make sure that this batch runs together no matter what is happening on other threads.

Thanks,
John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources