Solved

Standard locking question

Posted on 1998-06-23
6
215 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
ID: 1091608
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
ID: 1091609
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
ID: 1091610
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:flfmdll
ID: 1091611
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
ID: 1091612
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
ID: 1091613
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

813 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

18 Experts available now in Live!

Get 1:1 Help Now