Solved

Standard locking question

Posted on 1998-06-23
6
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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