February 28, 2006 at 11:34 am
Hi
The system I am working on/have inherited has a stored procedure which allows a system user to grab a record (which is a web enquiry) and update it to "claim" it for themselves and then add a customer record to the back end and associate the 2 items together.
There are a few problems occurring where two users try and grab the same enquiry at a similar time leading to the procedure failing and the original enquiry getting orphaned.
The existing procedure is as follows:
--get the details and check the enquiry is available
DECLARE @CustomerFirstName varchar(100)
DECLARE @CustomerSurname varchar(100)
DECLARE @FullName varchar(100)
DECLARE @TelephoneDay varchar(100)
DECLARE @TelephoneEve varchar(100)
DECLARE @EMailAddress varchar(100)
DECLARE @AcceptDate datetime
SELECT
@FullName = Enq_CustName,
@TelephoneDay = Enq_CustPhone1,
@TelephoneEve = Enq_CustPhone2,
@EMailAddress = Enq_CustEMail,
@AcceptDate = Enq_AcceptDate
FROM
tbl_hp_enquiry
WHERE
Enq_ID = @EnquiryID
SELECT @CustomerFirstName = SUBSTRING(@FullName, 1, NULLIF(CHARINDEX(' ', @FullName) - 1, -1)) ,
@CustomerSurname = SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName))
IF @AcceptDate IS NULL
UPDATE
tbl_hp_enquiry
SET
Enq_HWID = @HWID,
Enq_AcceptDate = GetDate()
WHERE
Enq_ID = @EnquiryID
INSERT into tbl_hp_customers
(
CustomerFirstName,
CustomerSurname,
TelephoneDay,
TelephoneEvening,
EMailAddress,
OwnerID
)
VALUES
(
@CustomerFirstName,
@CustomerSurname,
@TelephoneDay,
@TelephoneEve,
@EMailAddress,
@HWID
)
DECLARE @NewCustID int
SET @NewCustID=SCOPE_IDENTITY()
UPDATE
tbl_hp_enquiry
SET
Enq_CustomerID = @NewCustID
WHERE
Enq_ID = @EnquiryID
RETURN @NewCustID
Can anybody advise on a better way of approaching this procedure so that basically once a user starts the process of requesting an enquiry that no other user can enter the process?
Any advice greatly appreciated.
Simon
February 28, 2006 at 12:26 pm
It sounds like you want to implement your own locking control. There are a couple of ways to do so. First, you could wrap your stored procedure up in a transaction and use locking hints to put a table lock on the table/tables that you with to prevent other users from being able to update. Secondly, (and this is how our online application handles certain session operations) is to create a new table to manage your locks in. At the beginning of your SP, check that table for the lock, if it exists, exit the SP without updating. If no lock exists, place a lock row in the table and continue on with your update.
February 28, 2006 at 2:14 pm
I would think an easy method. Which would fail if hit 'exactly' the same time.
Would be to add a check on your first select
SELECT
@FullName = Enq_CustName,
@TelephoneDay = Enq_CustPhone1,
@TelephoneEve = Enq_CustPhone2,
@EMailAddress = Enq_CustEMail,
@AcceptDate = Enq_AcceptDate
FROM
tbl_hp_enquiry
WHERE
Enq_ID = @EnquiryID
AND Enq_AcceptDate is NULL
Note: this is an assumption that the logic for unassigned enq_id's always has date is null. Possible to add status to table and check for that instead
February 28, 2006 at 4:42 pm
Even easier:
INSERT into tbl_hp_customers
(CustomerFirstName,CustomerSurname,TelephoneDay,TelephoneEvening,EMailAddress,OwnerID)
SELECT
SUBSTRING(Enq_CustName, 1, NULLIF(CHARINDEX(' ', Enq_CustName) - 1, -1))
SUBSTRING( Enq_CustName, CHARINDEX(' ', Enq_CustName) + 1, LEN( Enq_CustName))
, Enq_CustPhone1, Enq_CustPhone2, Enq_CustEMail, @HWID
FROM tbl_hp_enquiry
WHERE Enq_ID = @EnquiryID
AND NOT EXISTS (select 1 from tbl_hp_customers
where CustomerFirstName = SUBSTRING(Enq_CustName, 1, NULLIF(CHARINDEX(' ', Enq_CustName) - 1, -1))
and CustomerSurname = SUBSTRING( Enq_CustName, CHARINDEX(' ', Enq_CustName) + 1, LEN( Enq_CustName))
)
-- Do you really care about uniqueness of customer records?
UPDATE
tbl_hp_enquiry
SET
Enq_HWID = @HWID,
Enq_CustomerID = C.CustomerId,
Enq_AcceptDate = ISNULL(Enq_AcceptDate , GetDate() )
FROM tbl_hp_customers C
WHERE Enq_ID = @EnquiryID
and C.CustomerFirstName = SUBSTRING(Enq_CustName, 1, NULLIF(CHARINDEX(' ', Enq_CustName) - 1, -1))
and C.CustomerSurname = SUBSTRING( Enq_CustName, CHARINDEX(' ', Enq_CustName) + 1, LEN( Enq_CustName))
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply