• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Standard locking question

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
flfmdll
Asked:
flfmdll
1 Solution
 
Victor SpiridonovCommented:
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
 
juliocoelhoCommented:
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
 
flfmdllAuthor Commented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
flfmdllAuthor Commented:
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
 
tomookCommented:
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
 
juliocoelhoCommented:
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now