Solved

Standard locking question

Posted on 1998-06-23
6
207 Views
Last Modified: 2010-03-19
Okay, I know this must have been asked a 1000 times on this page. But here it is anyway.

I have multiple processes that will be accessing the same MS SQL table. Their first access will be to see what the next unique id (dbint) is so it can be assigned to their record. I have the logic working for a single access point. But I am worried that when multiple accesses occur, each one might get the next id in order (1, 2, 3, 4...the next one would be 5) and think it is theirs. How do I prevent multiple accesses to the same table from thinking they should get the next unique id?
0
Comment
Question by:flfmdll
6 Comments
 
LVL 7

Expert Comment

by:spiridonov
Comment Utility
What do you use to access  MSSQL?.
Have you thought about using IDNETITY property - if you specify your column with  this property SQL Server takes care of unique autoincrementing number automatically.
0
 
LVL 1

Accepted Solution

by:
juliocoelho earned 30 total points
Comment Utility
Hi!

Like spiridonov said you can use the Identity type for doing incremental values in you column, whoever this brings you a problem whenever you DB server goes down, because the sequencial numbering starts to begin lags.

If you want to control yourself the numbering, it's better to build the following stored procedure

CREATE PROCEDURE spGetValue
    @i_CounterID char(30),
    @i_NewValue int OUTPUT
AS
BEGIN
    BEGIN TRANSACTION
        SELECT   @i_NewValue = <TABLE_FIELD_COUNTER_VALUE> + 1
        FROM   <TABLE> (holdlock)
        WHERE   <TABLE_FIELD_COUNTER_VALUE> = @i_CounterID

        UPDATE <TABLE> SET
             <TABLE_FIELD_COUNTER_VALUE> = @i_NewValue
        WHERE   <TABLE_FIELD_COUNTER_VALUE> = @i_CounterID
    COMMIT TRANSACTION
END

You must substitute the values that are in <> with corresponding  values.

I allways use a table with only two fields: One for the Counter ID and the other for the counter value.

With kind regards
0
 

Author Comment

by:flfmdll
Comment Utility
Will other accesses to the table be suspended or terminated while this store procedure executes? For instance, will another instance of a program calling this procedure return immediately with an error or will the function wait until it can run the stored procedure?

Thanks for the very good help
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:flfmdll
Comment Utility
Okay, I'm slightly confused. I understand the basics of it. However, I think my table is a little different than what you are imagining. It looks like the following. QueueID, your 'i_newValue', is the unique number and ProcessName might be your 'i_CounterID'. I'm not sure. ProcessName is not unique in my setup. Did I misunderstand you? How will selecting ProcessName give me the next QueueID?

CREATE TABLE dbo.ProcessQueue (
      QueueID int NOT NULL ,
      ProcessName varchar (64) NOT NULL ,
      ProcessParam varchar (255) NULL ,
      TimeRequested datetime NOT NULL ,
      TimeRetry datetime NULL ,
      RetryCount int NOT NULL ,
      Status int NOT NULL
)

0
 
LVL 4

Expert Comment

by:tomook
Comment Utility
This is precisely what to do in SQL Server and works quite well. Note that Oracle has system level counter objects which do the same thing, one of the few places where Oracle makes it easier.
0
 
LVL 1

Expert Comment

by:juliocoelho
Comment Utility
Hello!

When you put the clause (holdlock) this means for the MS SQLServer that nodody else can access that table while the current transaction is taking place. So you do not have a problem with other users accessing the same data as you.
When another user try to access the data the SQLServer is going to generate an error for the second user.

When you say that ProcessName it's not unique what do you mine by that? What does ProcessName Keep?

For my algoritm to work you need a name or an ID to represent uniquely each counter an less that you need to keep a history of the numbers given, for that you need to change the select to get the MAX(<TABLE_FIELD_COUNTER_VALUE>) +1 and change the update to an insert.

But I prefer not keeping a history list for each counter.

I suggest that you change you table to the following:


CREATE TABLE dbo.Queue (
QueueID varchar(30) NOT NULL ,
QueueValue int NOT NULL
TimeRequested datetime NOT NULL ,
TimeRetry datetime NULL ,
RetryCount int NOT NULL ,
Status int NOT NULL
)

CREATE TABLE dbo.ProcessQueue (
QueueID varchar(30) NOT NULL ,
ProcessName varchar (64) NOT NULL ,
ProcessParam varchar (255) NULL ,
)


Take the following example:

you need three different counter, so you need three diferent records in the table each one representing a counter.

QueueValue         QueueID                .......
1                  Counter1
1                  Counter2
1                  Counter3

When you use spGetValue 'Counter1', @v_NewValue OUTPUT

the variable will get @v_NewValue the value 2 and the table it's going to like this:

QueueValue         QueueID                .......
2                  Counter1
1                  Counter2
1                  Counter3

And so on


Like I said I need to understand a little better what you need to do and what each field in the table represent's

I have a long delay in my e-mail and I'm in the other side of the globe, so your time doesn't match. But I alway's write back.

With kind regards,

Júlio Coelho
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now