quote:

---

Originally posted by rockmoose

Having an InUse column should not cause deadlocks.
The deadlocks are a different issue, and depend on other things;
transactions, isolation level, order of read/insert/update, do you close the connections after reading etc...

The flow for the clients should be;
0. connect, begin transaction
1. Lock the records that are to be retrieved (update)
2. Retrieve the records
3. commit, disconnect

And do the above in a stored procedure!

Edit:
Maybe You should let the client only lock a certain amount of records,
and only for aspecified amount of time.
Use a lockdate column,
and all records where datediff(minute,lockdate,getdate()) > 9 -- 10 minute locks allowed
are considered unlocked.

That way they will not be locked indefinetely if the client never releases them.

rockmoose

---

        BEGIN TRANSACTION

	update fetching set fetching.CapturedDateTime = getdate()
		,fetching.CapturedBy = @UserName
	from tblFetch as fetching
	where fetching.tID in (select top 1 tID 
				from tblFetch 
				where CapturedBy = '0' 
					or datediff(minute,CapturedDateTime,getdate()) >= 15
				order by tID asc 
				)	

	-- Show the record for picking.
	SELECT top 1 
		  s.SubscriberName as SubscriberName  	
		  ,t.TransAmount
		  ,t.tID
		  ,s1.SubscriberName as upline
	FROM tblFetch as fetching
	INNER JOIN tblTransactions as t on t.tID = loading.tID
	INNER JOIN tblArrivals as sa on sa.aid = t.aid
	LEFT JOIN tblSubscribers as s on s.CellphoneNo = t.CellphoneNo
	LEFT JOIN tblSubscribers as s1 on s.refCellphone = s1.CellphoneNo	
	where fetching.CapturedBy = cast(@UserName as numeric(18)) and fetching.CapturedDateTime is null
	order by t.sysDateTime

	-- Rollback transaction if error occurs.
	if @@error <> 0
	   begin
		ROLLBACK TRANSACTION
	   end


	COMMIT TRANSACTION

quote:

---

Originally posted by jonasalbert20

quote:

---

Originally posted by rockmoose

Having an InUse column should not cause deadlocks.
The deadlocks are a different issue, and depend on other things;
transactions, isolation level, order of read/insert/update, do you close the connections after reading etc...

The flow for the clients should be;
0. connect, begin transaction
1. Lock the records that are to be retrieved (update)
2. Retrieve the records
3. commit, disconnect

And do the above in a stored procedure!

Edit:
Maybe You should let the client only lock a certain amount of records,
and only for aspecified amount of time.
Use a lockdate column,
and all records where datediff(minute,lockdate,getdate()) > 9 -- 10 minute locks allowed
are considered unlocked.

That way they will not be locked indefinetely if the client never releases them.

rockmoose

---

Sorry for multiple posting. here's my final edited post.

I've seen it with my two eyes. Same record was fetch by the client. Do we have an issue of simultaneous pick and update?

Heres my code...

	BEGIN TRANSACTION

	-- Update that the record has been picked by user for viewing.
	update fetching set fetching.CapturedDateTime = getdate()
		,fetching.CapturedBy = @UserName
	from tblFetch as fetching
	where fetching.tID in (select top 1 tID 
				from tblFetch 
				where CapturedBy = '0' 
				order by tID asc 
				)	
	-- Rollback transaction if error occurs.
	if @@error <> 0
	   begin
		ROLLBACK TRANSACTION
	   end

	-- Show the record for picking.
	SELECT top 1 
		  s.SubscriberName as SubscriberName  	
		  ,t.TransAmount
		  ,t.tID
		  ,s1.SubscriberName as upline
	FROM tblFetch as fetching
	INNER JOIN tblTransactions as t on t.tID = loading.tID
	INNER JOIN tblArrivals as sa on sa.aid = t.aid
	LEFT JOIN tblSubscribers as s on s.CellphoneNo = t.CellphoneNo
	LEFT JOIN tblSubscribers as s1 on s.refCellphone = s1.CellphoneNo	
	where fetching.CapturedBy = cast(@UserName as numeric(18)) and fetching.CapturedDateTime is null
	order by t.sysDateTime

	-- Rollback transaction if error occurs.
	if @@error <> 0
	   begin
		ROLLBACK TRANSACTION
	   end


	COMMIT TRANSACTION

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

---