queuing records - ensuring one record per user

Hello all...

I have an MS Access front end that provides call information to a call center. There are 40 users who access only about 40,000 records. The users are split into different call groups.

Call groups pull up different records from the same master table based on criteria in the record (region, dollar amount, special codes, etc.)

It is possible for a given record to end up in more than one call group based on its criteria.

Here is the basic process:

User enters call system

Opens form which queries for the "next" record (next, uncalled, unresolved record);

A unique key field is pulled out of the database and then set as part of the recordset property for the form.

The record is stamped in a time/date field and also with the user's ID to signify that it is open. This ensures that it does not appear in future queries on the database - so that the "next" users receives the next record.

However, if multiple users in the same call group hit "next record" simultaneously, it is possible that the same unique key field, hence, the same record will be returned and then opened. This results in a "Write Conflict" error when the second user to open the record goes to save their changes.

How can I ensure that when a user goes to get the next record, that record is locked from the time they query the DB - write their stamp to the record - open the record, and finally save it.

If they open the record, my stamp ensure it drops off any call queues. It is specific to a request for the next record simultaneously.

I hope I was clear. Any help is appreciated.

Thanks,

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

Hi ya,

do the whole operation in a stored proc, then you can

begin a transaction

update the record, copying the id into a local variable
update blah set @lv = id = id, userid = user_id(), etc.
select the record just updated using the local variable
select xxx from blah where id = @lv

commit transaction (or rollback if there is an error)

Cheers
Twan

This is more of an underlying process question:

How does this ensure that the record identified in the transaction will not be affected by the same stored procedure started by another user?

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

The transaction puts any subsequent request by a parallel process into hold, until the transaction is committed - then the parallel process gets its turn.

What about just using an auto_increment field ?

Hi ya,

if I understand the problem right, there is a table of rows that need to be processed and you want to pick a row out of this 'queue'. To ensure that the same row isn't picked up by two processes, you flag the row to say that is being processed.

so an auto-inc field isn't going to help in this case, since the rows are already there.

options really are:
do the select and then the update but make sure that a transaction is wrapped around it and either the select uses xlocks or isolation level is read repeatable

do the update and then the select, again within a transaction

as Adriaan mentioned the effect of the transaction is to serialise access to that row until both the select and the update have taken place. There is no way that another process can get in between

Cheers
Twan

Ah Twan, youre quite right. I was under the impression the users were creating the records at the same time, but after rereading this isnt the case <img src='/community/emoticons/emotion-1.gif' alt='' />

Thanks Twan.. You've got it right.

I played around with it yesterday and will be testing it today.

Thanks for your assistance.

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

Twan and others...

I am just getting back to this. Is this the basic idea? I am fairly new to the stored-procs but have created some simple select sp's.

The stored procedure will return a unique key based on a record being available. It will stamp the record with the current date/time, removing it from other queries, and put the user's ID in the record as well. The reason I do this, is that if the timestamp is more than 5 minutes old, the record becomes available again and will not be locked in the case of a system crash on the part of a user.


=================================
Stored procedure (sp_lockandreturn)

Received a variable which will specify the query criteria (I have several call groups that have different record criteria);

Begin a transaction
- return the first record matching the criteria;
- stamp the record with the date, time, and user;
- if update is successful, return unique key, if not successful, rollback trans and return a 0
End the transaction
- return the unique key for this record
=================================

Once returned, the form will open the record specified by the unique key returned.

When the form is open, it re-stamps the record every 2 minutes to ensure it stays any queries being run by other users.

So, here is a related question...

I am only interested in returning a single record. If the transaction fails, it returns a 0 and I re-call the stored procedure. However, can I loop within the procedure until I get a record or until no records match the criteria? I would imagine that this would keep the processing at the server - rather than return to the client.

Should I return multiple records, being the transaction, attempt an update, rollback on failure - move to the next record, update again, etc. inside of the stored procedure.

This way, I am not querying the DB to return records multiple times - only once. Once the transaction succeeds I have my number and don't need to attempt any other updates. I leave the procedure.

Am I on the right track? Does this make sense?

Thanks.

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

Hi Matthew,

yep along the right track. I would update the record first and then return the value if it succeeds. You can set local variables to the user, date and time which will also give you a unqieu key to select the record back on

i.e.

set @date = getdate()
set @user = user_name() -- or passed in
set @return = null

begin transaction

update ...
set date = @date, user = @user
where ...

select @error = @@error, @rowcount = @@rowcount

if @error = 0
if @rowcount > 1 -- not sure if you want to handle this?
else if @rowcount = 1
begin
select @return id from ... where date = @date and user = @user

select @error = @@error, @rowcount = @@rowcount
end
else

if @error = 0
begin
commit tran
select @return
end
else
rollback tran

return @error

Cheers
Twan

Okay, upon some study but still in a psuedo code format:

Begin Trans:
- Return some records matching criteria;
- if no records
return (value indicating no records in this call list)
- else
set a record id to a variable (@recordid)
Update tblCallList Set LockDateTime = (currentdatetime)
If @@ERROR <> 0
ROLLBACK TRAN
Return (value indicating could not write lock)
END

Return @recordid (set to the unique key)
COMMIT TRAN
=====================

Or something similar. The idea is to return the next available record, stamp it, if that fails inform the application so it can get another record. If it succeeds, return the record id value.

The same question applies (above) about looping through multiple records inside of the stored procedure before returning to the client application. Thanks.

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

We posted across each other. What you posted was very helpful. Sorry that I am belaboring this point but I want to make sure I understand...

I'll begin the transaction by updating the record so that it potentially cuts down on the amount of work SQL does. In affect, I am assuming my update will work but will catch the exception - rather than return records and then perform my update.

The idea is that if it works, only one record will have that unique time, date, and user stamped.

Also, can I ensure that it updates only the next record. Multiple records should virtually never be updated at the same time. It is imperative that I return a single record (there is actually an exception but we can discuss that later).

I know I can use a top 1 select statement. Can I do a top 1 on an update statement?

UPDATE Top 1 tblCallList Set LockDatetime = DAte yada yada yada...

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

Now my post are more like spam... sorry.

No top in the update statement and I need to ensure only one record does get updates.

However, It would seem that I can return the top 10 records and loop through it until one of the updates commits properly - and then return the value to my client application.

If an end of file occurs, I could trap and return that as well.

If none of my commits work, and it is not the eof, I could then re-call the stored procedure.

I don't see a way around first retrieving records and then attempting to lock them with an update.

As stated, it must always be a single record, not multiple records.

And the query, at least to start, would include thousands of records. I can't risk updating the LockDateTime field on mutiple as this would result in all the other users retrieving 0 records.

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

hi ya

'set rowcount' is your friend. set it to 1 before the update and then set it back to 0 before the proc commits. This has the same affect as select top, but works for all DML

Cheers
Twan

Interesting!!! In the SQL Server documentation, looking at Set Rowcount, it says...

===========================
It is recommended that DELETE, INSERT, and UPDATE statements currently using SET ROWCOUNT be rewritten to use the TOP syntax.
===========================

But when you look at the update command, it does not indicate that TOP can be used?

I'll try the rowcount.

Did you give thought to the idea of returning multiple records, attempting an update on them sequentially until successful, and then committing the transaction? Forcing it to eval a single record would mean the client would have to recall the stored procedure until it is successful.

Thanks.

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

Hi Matthew,

I'd probably do the error handling in the calling app and yes calling the proc again if needed. There are some errors which you can't trap in the SQL side (e.g. deadlock, server gone down, etc.) So you'd have to deal with those in the front-end app anyway

also the likelyhood of an error is going to be very very small, so there is unlikely to be a subsequent call to the proc from the app

Cheers
Twan

Oh great SQL Swami, I am learning a thing or two about stored procedures - and I like what I am reading..

However, I used your code above - with some changes, and was first receiving errors about declaring the variables I am using. Now the syntax checks out but I receive an error when I call this from the query analyzer...

==================== MESSAGE ======
Server: Msg 266, Level 16, State 2, Procedure wmspGetNext, Line 34
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
==================== END MESSAGE ======

Here is the stored procedure...

I am using the row count and am forcing the @user variable. Once it is working, I will pass the variable. The return value s/b the record ID number.

I am assuming, based on what I am reading, that it is a nesting problem. What I cannot determine is exactly how you next if..then statements in transact-sql for stored procedures. I am researching/learning this now.

Any insight is appreciated.

====================
CREATE PROCEDURE wmspGetNext AS
declare @date as datetime, @user as integer
declare @return as integer, @error as integer
declare @rowcount as integer
set @date = getdate()
set @user = 25
set @return = null
Set ROWCOUNT 1

begin transaction
update tblCallList
set LockDateTime = @date, user_cd = @user

select @error = @@error, @rowcount = @@rowcount

if @error = 0
begin
select @return CallList_uno from tblCallList where LockDateTime = @date and user_cd = @user

select @error = @@error, @rowcount = @@rowcount
end
else

if @error = 0
begin
commit tran
select @return
end
else
rollback tran

set ROWCOUNT 0

return @error

GO


Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

Here is what is going on:

The update works properly - no error - but the query to return records that have just been changed (1 record) returns nothing. At least not until after the commit statement.

It would appear that I need to run the update, check for an error, commit the transaction, return the record number, and then exit the procedure.

I'll test it and let you know.

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

Final: It's working!

The select @return is now
set @return = (Select calllist_uno....)

I commit the transaction prior to returning the value from the db to return.

Thanks for getting me started down this road.

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

Hi ya,

the problem is that you have one else too many



if @error = 0
begin
select @return CallList_uno from tblCallList where LockDateTime = @date and user_cd = @user

select @error = @@error, @rowcount = @@rowcount
end
else

if @error = 0
begin
commit tran
select @return
end
else
rollback tran

Cheers
Twan

Thanks. I've now been adding several enhancements and created several other procedures for some other items in the system.

Here is a question...

Can I call CDO mail and other system objects using stored procedures. I have a client who was using SQL Mail but that requires a MAPI client on the SQL Server. They are moving the sql server (actually, putting up two) and I would rather not use SQL Mail and the SQL client as it requires an older (non-security patched) version of a MAPI client.

I know this is a new topic but a quick reference would be fine. I am just starting to research this so maybe I'll have the answer shortly.

Thanks again for your assistance.

Matthew Moran
The IT Career Builder's Toolkit
http://www.cbtoolkit.com

Hi Matthew,

yeah you'll find plenty of samples on this site and on others on how to send cdosys emails from SQL, no need to install any MAPI client on the server

here is one example from a search for CDOSYS on this site
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4931&SearchTerms=CDOSYS

Cheers
Twan