begin try
  begin transaction
  set lock_timeout 20000
  update Warehouse ...
  if @id is null raiserror ...
  insert into TransactionLogQuery ...
  commit transaction
end try
begin catch
  if @@trancount > 0 
    rollback transaction
  insert into ErrorLog ...
end catch
return @response

set nocount off
select @timeouttime = dateadd(ss, 20, getdate())
begin try
	set rowcount 1

	select @merchant_id = merchant_id
	from client 
	where name = @name

	if @merchant_id is null 
		raiserror('no such service...')

	set rowcount 0

	begin transaction

	set lock_timeout 20000

	-- retrieve one record from warehouse
	-- be careful about the serial order
	update warehouse
	set 
		@stock_id = wh.id, 
		[status] = 1
	from warehouse wh 
	inner join (
		select top (1) id
		from warehouse 
		where 
			merchant_id = @merchant_id and
			[status] = 0
		order by serial
	) w on w.id = wh.id
	where [status] = 0

	if @stock_id is null
		raiserror('no stock...')

	-- log the transaction
	insert into transactionlogquery
		(
			[name],
			merchant_id,
			stock_id,
			created
		)
	values 
		(
			@name,
			@merchant_id,
			@stock_id,
			getdate()
		)

	if @@rowcount <> 1 
		raiserror('error writing log...')

	if getdate() > @timeouttime 
	begin
		raiserror('timeout')
	end

	commit transaction
end try
begin catch
	if @@trancount > 0 
		rollback transaction
	insert into ErrorLog 
		(
			[name],
			created
		)
	values
		(
			@name,
			getdate()
		)
end catch
	set rowcount 0
	select @stock_id stock_id

quote:

---

Originally posted by tkizer

If the transaction was committed, then it would be in the database. If it did commit and the id=2 is missing, then someone deleted it.

The only way to have the gaps is for a rollback or a delete. Well unless someone is modifying the id values, which is unlikely.

Could you post your actual code, so that we can ensure there is no transaction bug in it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

quote:

---

Originally posted by AndrewMurphy

is there a possibility some trigger "tidied up" the data after it was committed?

---

quote:

---

Originally posted by Kristen

"the application would detect such case and do a proper log entry. "

Unless that was itself rolled back, or was itself in error - an error in your CATCH block would not be logged - hard to say whether that is possible, and whether such an error would show up on the client's application (e.g. as some sort of error that a user MIGHT! remember seeing)

You are dealing with something you don't expect (and if you are telling me that COMMIT failed then I'm starting from a position of 99.99999% not believing you and 0.00001% chance of a Microsoft bug!)

So you should broaden your mind and not say "X did not happen". Something must (99.99999%) have happened. User may have made a mistake in what they remember. Error Log may have failed. Someone may have hacked into the system. Someone with access may have changed something (and either has not been asked yet, or is not prepared to admit it)

"At this moment I cannot give full copy-paste of the stored procedure"#

If we are looking at some made up code that represents the actual code then more than likely we are wasting our time. The devil will be in the detail.

If you have a problem with confidentiality around the code then do Find & Replace on columns / passwords / whatever

---

quote:

---

Originally posted by Kristen

"Also there must was failure in catch block which caused no-resetting of variables that are returned to application and no-inserting into ErrorLog table"

I can't verify that as you haven't posted the code.

My code is 100% bullet-proof - like you describe yours. We have error logs at every level - all sorts of stuff to be able to reverse engineer what caused a problem (our sites are all for web customers, so we have 0% chance of asking a user "what happened")

I still mistrust them and assume we goofed up somewhere when anything goes wrong.

Clearly something has gone wrong ... but without seeing your code its not possible to give you second-pair-of-eyes for anything that might occur to me / others reading here.

You could restore to point-in-time and restore-forwards over the time in question to see what appears - tedious job, and I'm sure what your error logs are design to avoid you having to do. A Log Reader/Decoder might help with that.

I suppose if no triggers you don't have an "audit table" connected to this that would log any insert/update/delete? (benefit of that is that it is within the transaction, so harder to have a failure that does not also log in the audit table, and requires quite a lot more cover-your-tracks for a hacker)

---

IF getdate() > @timeouttime

quote:

---

Originally posted by tkizer

My only suggestion would be to open a case with Microsoft on this if you think that your code is bulletproof, that the user didn't do something to cause it, or if a delete didn't happen. If it's a SQL bug, then only Microsoft can assist with this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---