Race Condition on Row Insert w/o Identity Field

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 2008 Forums

Transact-SQL (2008)

Race Condition on Row Insert w/o Identity Field

Author

Topic

sfortner
Yak Posting Veteran

63 Posts

Posted - 2011-03-30 : 08:38:53

Hi everyone, I read Dan Guzman's blog at http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx and http://stackoverflow.com/questions/698366/how-to-avoid-a-database-race-condition-when-manually-incrementing-pk-of-new-row in preparation to perform inserts on a Sage Timesheet database table without an identity field. This is for a custom time keeping app we're doing in-house. I have no control over this database, so I'm forced to handle inserts manually. Here's what I and others have done (and, yes, this is definitely in a try/catch/transaction block...):

declare @InsertedRows table(id int)
DECLARE @Nextid int

insert into tsp_TimeEntries (ID) 
output inserted.ID into @InsertedRows 
select max(id) + 1 from tsp_TimeEntries with (HOLDLOCK, UPDLOCK)

select top 1 @NextID = ID from @InsertedRows

update tsp_TimeEntries set ENTRY_DATE = @DTdtime, START_TIME = @DTstime, STOP_TIME = @DTstime, DURATION = @calcdur, 
IDS_0 = @Nids0, IDS_1 = @Nids1, IDS_2 = @Nids2, IDS_3 = @Nids3, IDS_4 = @Nids4, IDS_5 = @Nids5, IDS_9 = @Nids9, 
status_10 = @Status, status_11 = 3, IDS_6 = 0, IDS_7 = 0, IDS_8 = 0, has_notes = @nhasnotes, timer_running = 0, 
nostartstop = 1, status_0 = 0, status_1 = 0, status_2 = 0, status_3 = 0, status_4 = 0,status_5 = 0,status_6 = @NonBillable, 
status_7 = @lunch, status_8 = 0, status_9 = 0, change_date = @DTdtime, orig_id = @Nextid, entryflag = 0, 
audit_action = 0, rate = 0, timecardentry = 0, timerbased = 0, importstamp = 0, entrytype = 0, audit_emp = 0
where ID = @NextID

The question is - I'd like to insert one time instead of having to reserve/hold and store the ID, then update the table for the orig_id field which uses the same value as the ID field. Is there a way to insert both the ID and orig_ID at the same time with the same value without a second update statement? I don't think I can use the Output...Into clause here can I? Thanks in advance for any info - it's much appreciated.

--Steve

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-30 : 09:19:55

Try:


INSERT INTO tsp_TimeEntries
(
	ID,
	ENTRY_DATE, START_TIME, STOP_TIME, DURATION, 
	IDS_0, IDS_11, IDS_2, IDS_3, IDS_4, IDS_5, IDS_9, 
	status_10, status_11, IDS_6, IDS_7, IDS_8, has_notes, timer_running, 
	nostartstop, status_0, status_1, status_2, status_3, status_4,status_5,status_6, 
	status_7, status_8, status_9, change_date, orig_id, entryflag, 
	audit_action, rate, timecardentry, timerbased, importstamp, entrytype, audit_emp
)
SELECT
	MAX(ID) + 1,
	@DTdtime, @DTstime, @DTstime, @calcdur, 
	@Nids0, @Nids1, @Nids2, @Nids3, @Nids4, @Nids5, @Nids9, 
	@Status, 3, 0, 0, 0, @nhasnotes, 0, 
	1, 0, 0, 0, 0, 0, 0, @NonBillable, 
	@lunch, 0, 0, @DTdtime, MAX(ID) + 1, 0, 
	0, 0, 0, 0, 0, 0, 0
FROM tsp_TimeEntries WITH (SERIALIZABLE, UPDLOCK)

sfortner
Yak Posting Veteran

63 Posts

Posted - 2011-03-30 : 10:37:34

Okay, so that's more straightforward than I thought. Did you mean to use HOLDLOCK instead of SERIALIZALBE? It looks like they are equivalent, but I don't think that's the right syntax for using SERIALIZABLE is it? Thanks again for the response!

--Steve

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-04-01 : 05:18:03

HOLDLOCK and SERIALIZABLE are the same thing.

I find SERIALIZABLE more readable especially as here the important thing is the key range lock.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources