Locking Records so others can't read 
Author Message
 Locking Records so others can't read

I have a database for a CallCenter is populated from a SP with the following
in it:

Alter Procedure spSelectTop1

As

SELECT TOP 1 *

FROM dbo.customer, dbo.pcodelisting

WHERE (dbo.customer.cselected = 0) AND

(dbo.customer.ccalled = 0) AND

(dbo.customer.cpcode LIKE N'%' + dbo.pcodelisting.pcode + '%')

return

This SP checks to make sure that the customer it is looking for has not been
called yet and that the customer has never been selected (More on this
below) and that the Postal Code conforms to the area we are calling.

When the form opens, in the OnCurrent Event it sets the CSelected to 1
(true) and that will hopefully keep the next person from getting the same
person.  Although, It seems to be happening that if two people click on
'Next Caller' at the same time (or within enough time before the record is
saved with the CSelected = 1 (true) they will both get the same person and
then a 'Write Conflict' box comes up.

How can I fix this?  I need to make sure that the same two people don't get
the same name at the same time?  I thought about possibly randomizing the
select string but don't know how to select random records that will also
conform to my criteria.

Any help on this would be greatly appreciated as I am stuck in a big way
right now.

Thanks!!!!
Shawn
--
~~~~~~~~~~~~~~~~~
ACS Systems
4315 Drummond Road
Niagara Falls

V: 905-374-0028 ext. 26
F: 905-374-6689
W: www.acssystems.net
W: www.viprewards.ca
~~~~~~~~~~~~~~~~~



Wed, 06 Oct 2004 04:22:29 GMT
 Locking Records so others can't read

I used something like the following code in a stored procedure that is
called to both assign and return the next available task. I used
heavy-handed table locking, which still worked fine with 50 users, so I
never experimented with less severe locking.
Paul Shapiro

--First, assign the highest-priority open task to the current user
  Update dbo.Task With (TablockX Serializable)

   taskAssignedDate = getDate()

  Where taskID In
   (
    Select TOP 1 taskID

    From dbo.Task A2T  With (TablockX Serializable)
    Where
     --Task is not yet assigned to anyone
     A2T.taskAssignedTo Is Null
... And other criteria
    Order By (appropriate priority criteria)
    )

--Now return the assigned task(s)
  SELECT ...
  FROM dbo.Task A2T
  WHERE

    and A2T.taskCompletedDate Is Null


Quote:
> I have a database for a CallCenter is populated from a SP with the
following
> in it:

> Alter Procedure spSelectTop1
> As
> SELECT TOP 1 *
> FROM dbo.customer, dbo.pcodelisting
> WHERE (dbo.customer.cselected = 0) AND
> (dbo.customer.ccalled = 0) AND
> (dbo.customer.cpcode LIKE N'%' + dbo.pcodelisting.pcode + '%')
> return

> This SP checks to make sure that the customer it is looking for has not
been
> called yet and that the customer has never been selected (More on this
> below) and that the Postal Code conforms to the area we are calling.

> When the form opens, in the OnCurrent Event it sets the CSelected to 1
> (true) and that will hopefully keep the next person from getting the same
> person.  Although, It seems to be happening that if two people click on
> 'Next Caller' at the same time (or within enough time before the record is
> saved with the CSelected = 1 (true) they will both get the same person and
> then a 'Write Conflict' box comes up.



Thu, 07 Oct 2004 22:28:18 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Extracting 'The', 'And', etc.from lists

2. Base vidé'o'tek et disk'o'tek

3. maintenance.exe error: SELECT permission denied on object 'sysdbmaintplans', database 'msdb', owner 'dbo'.

4. if oneof('camel','yak','llama')

5. SELECT permission denied on object 'mytable', database 'mydb', owner 'me'. to run query string from asp web page

6. EXECUTE permission denied on object 'sp_sdidebug', database 'master', owner 'dbo' [from ASP.NET]

7. Changing 'Internal''s password

8. DBA's DBA's DBA's VA-MD-DC

9. Oracle's 'mysql_insert_id'?

10. message d'erreur à l'exécution d'un état

11. Can't login as 'sa'?

12. ExecuteNonQuery doesn't like 'GO'


 
Powered by phpBB® Forum Software