Managing concurrency in Stored Proc with Timestamp

Hello all,

I am trying to find the right way to get the correct error reported in a Stored Proc where the table contains a timestamp column. I gather the TSEqual is not the way to go (I'm using SQL Express, which uses Server 2005 syntax and TSEqual is deprecated).

Within an update procedure, given an ID, a TimeStamp, and a new value, I would like to specifically know if the update fails because the record was not found or because the timestamp indicated that the record was previously updated.

I can't figure out how to specifically raise the 532 error if the timestamps don't match, since I can't use RAISERROR.

Any thoughts would be much appreciated

Regards,

Flavelle

Will this do?

Since you are passing the Time stamp to the procecure follow these steps:

Exeucte an Update Statement and check the valuf of @.@.rowcount after the update statement. If the value is 0 execute a SELECT statement to check if the value exists for the given ID, once again check the value of rowcount if this is 1 then the daa was not updated because somebody else updated the record If the value is 0 then there exists no records.

Sample Code:

DECLARE @.TS TIMESTAMP

SELECT @.TS=@.@.DBTS

DECLARE @.CustomerID INT

SET @.CustomerID=9

UPDATE Customers

SET CustomerName='Nith'

WHERE CustomerID=@.CustomerID AND LastUpdate=@.@.DBTS

-- Instead of @.@.DBTS use your datetime value

IF @.@.ROWCOUNT=0

BEGIN

SELECT CustomerName FROM Customers WHERE CustomerID=@.CustomerID

IF @.@.ROWCOUNT=0

BEGIN

PRINT 'No Record Present for CustomerID'

END

ELSE

PRINT 'Some body else updated the data'

END

ELSE

PRINT 'Data Updated'

|||

An elegant and simple solution - many thanks. Now if only I could raise the 532 error directly using RAISERRROR - but it looks like it is going the way of the Dodo bird. Pity.

Regards,

Flavelle

|||

I think this is a good example for handling concurrency in stored procedure.

Does the sql server 2000 that contains some variables in order to indicate which records had been updated?

or any other method to do the same thing but did not use any programming or extra column in the tables?

Please kindly advice.

Thanks & regards,

Clara

Managing concurrency in Stored Proc with Timestamp

Hello all,

I am trying to find the right way to get the correct error reported in a Stored Proc where the table contains a timestamp column. I gather the TSEqual is not the way to go (I'm using SQL Express, which uses Server 2005 syntax and TSEqual is deprecated).

Within an update procedure, given an ID, a TimeStamp, and a new value, I would like to specifically know if the update fails because the record was not found or because the timestamp indicated that the record was previously updated.

I can't figure out how to specifically raise the 532 error if the timestamps don't match, since I can't use RAISERROR.

Any thoughts would be much appreciated

Regards,

Flavelle

Will this do?

Since you are passing the Time stamp to the procecure follow these steps:

Exeucte an Update Statement and check the valuf of @.@.rowcount after the update statement. If the value is 0 execute a SELECT statement to check if the value exists for the given ID, once again check the value of rowcount if this is 1 then the daa was not updated because somebody else updated the record If the value is 0 then there exists no records.

Sample Code:

DECLARE @.TS TIMESTAMP

SELECT @.TS=@.@.DBTS

DECLARE @.CustomerID INT

SET @.CustomerID=9

UPDATE Customers

SET CustomerName='Nith'

WHERE CustomerID=@.CustomerID AND LastUpdate=@.@.DBTS

-- Instead of @.@.DBTS use your datetime value

IF @.@.ROWCOUNT=0

BEGIN

SELECT CustomerName FROM Customers WHERE CustomerID=@.CustomerID

IF @.@.ROWCOUNT=0

BEGIN

PRINT 'No Record Present for CustomerID'

END

ELSE

PRINT 'Some body else updated the data'

END

ELSE

PRINT 'Data Updated'

|||

An elegant and simple solution - many thanks. Now if only I could raise the 532 error directly using RAISERRROR - but it looks like it is going the way of the Dodo bird. Pity.

Regards,

Flavelle

|||

I think this is a good example for handling concurrency in stored procedure.

Does the sql server 2000 that contains some variables in order to indicate which records had been updated?

or any other method to do the same thing but did not use any programming or extra column in the tables?

Please kindly advice.

Thanks & regards,

Clara

Managing Concurrency

I want to centralize my previous standalone application. Previous application was using VB.NET and Access XP. Now I want to keep a centralized database (SQL Server 2005) and VB.NET 2005. At this point of time thousands of concurrent users will connect to the database at the same time.

In my application, when a ticket is being issued to a tourist, an SQL query finds the max(ticketno) for the current month from the main table and reserves this number for the current ticket. When the operator has finished entering information and clicks SAVE button, the record is saved to the main table with this ticket no. Previously there was no issue since the database was standalone.

I want to know how to block the new ticket no so that other concurrent users are not using the same number for saving a record. How to better tune the database for thousands of concurrent users at the same time? I also want that the other user must not get an error message when he attempts to save a record. It should be automatically handled by the database.

I would NOT try to lock a process that first gets a ticket number, then waits until someone clicks the 'SAVE' button, and then updates the master table and then unlocks the process.

This will not scale and will be a MAJOR headache.

I recommend that you use an IDENTITY field for the 'ticketno', and let the 'system' take care of the updating/incrementation.

|||Can you please illustrate?|||

Arnie's suggestion is a good one...there doesn't seem to be a strong justification to pull in the complexity of some kind of key management system and there doesn't appear to be a need for the client application to have any knowledge of the ticketno value prior to saving the information. If you need to maintain referential integrity between tables, you can use the @.@.IDENTITY system function to get the last identity value that was entered in the master table. For example:

CREATE TABLE Tickets (TicketID INT IDENTITY, PurchaseDate datetime, CustomerID int) <NOTE: I'm leaving out the key relationship to the identity column on a customer table>

DECLARE @.TicketID AS int

INSERT INTO Tickets (PurchaseDate, CustomerID) VALUES (GETDATE(), @.SomeValue)

SET @.TicketID = @.@.IDENTITY

INSERT INTO Table2 ( @.SomeInfo, @.SomeInfo2, @.TicketID)

|||

Which part, how to define a 'TicketNo' column in a table as INDENTITY?

Look in Books Online, Topics: IDENTITY, CREATE TABLE

OR, the problems with scaleability?

It if is the later, consider that you have a choke point that every user must wait in line to access. And if some user takes a bit more time before clicking the [SAVE] button, well everyone else just has to wait because the number incrementing process is locked in the scenario you described.

|||

I recommend using SCOPE_IDENTITY instead of @.@.IDENTITY.

Under some situations, @.@.IDENTITY may provide inaccurate data.

|||Ticket No in our case is not a sequence number but in this format:

000123-0507-0101

The first part is the max(TicketNo) of current month. The second part contains month and year and the third part contains the code of the place. After each month end, the ticket no starts with 1 again.

Here I also want to know whether taking a string (like above) a primary key affects performance as opposed to a numeric primary key.