Solved

Locking SQL row query

Posted on 2013-01-28
9
358 Views
Last Modified: 2013-01-28
Hello,

I know there is a great detail of information on locking but I'm struggling to do something which, to me, should be quite straightforward.

My application assigns an order id from a sequence held in a parameter table.  I need to be able to select the current 'nextorderid' from the table 'parameters' and then increment the value by 1 ready for the next order.

I can select and do the update with no problem but I need to understand how to lock the row for the split second between the select and the subsequent update so that another user cannot be assigned the same id. I've tried the following:-

 Dim daParameters = New SqlDataAdapter("SELECT * from Parameters WITH (ROWLOCK)", oSQLConnection)
        Dim tblParameters As New DataTable
        daParameters.Fill(tblParameters)

but the management studio still allows me to edit the row.  I'm obviously missing something.

Any help gratefully received.

Thanks
0
Comment
Question by:Petermcg001
[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
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 11

Expert Comment

by:SThaya
ID: 38826428
can you try the below


SELECT * from Parameters WITH (NOLOCK)
0
 
LVL 11

Expert Comment

by:SThaya
ID: 38826435
have you tried with SQL transaction

http://www.informit.com/articles/article.aspx?p=26657
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 38826438
Hi Petermcg001,

To answer your question, you need to do a ROWLOCK of your existing column and release the lock once it is successfully inserted into a table. Concurrent users will be held under waiting till the time you release this particular record.

But a more elegant solution for your issue would be to use an Identity datatype column to avoid these lockings and other concurrency issues.
once a record is inserted into the first table, an Identity value would be generated which can be fetched using SCOPE_IDENTITY() function and the concurrent user also will get another primary key value without any issues.
For more detailed info about IDENTITY datatype and SCOPE_IDENTITY() functions, you can see the below links:
http://msdn.microsoft.com/en-us/library/ms189838.aspx
http://msdn.microsoft.com/en-us/library/ms190315.aspx
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 1

Author Comment

by:Petermcg001
ID: 38826461
Hello,

Thanks for looking at this.  I'm including the "WITH (ROWLOCK)" in my SQL command so I am wondering why this doesn't seem to be locking the record?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 38826668
Petermcg001,

kindly post the complete set of commands you are using to confirm whether there is something wrong in the way you are doing it..
0
 
LVL 18

Expert Comment

by:deighton
ID: 38826790
increment the value and return the value within a transaction

Dim cmdParameters as New SqlCommand("BEGIN TRAN; DECLARE @VAL int;UPDATE Parameters SET YourParamaeter = YourParamaeter + 1  WHERE <your where clause here>; SELECT @VAL = YourParamaeter from Parameters WHERE <your where clause here>;SELECT @VAL - 1;Commit tran;", oSQLConnection)
dim iVal as integer = cmdParameters.executeScalar
0
 
LVL 1

Author Comment

by:Petermcg001
ID: 38826847
Hello deighton,

thanks for that, it does the increment of the id fine.

Can I just confirm that it is the fact that the commands are wrapped up in a transaction which means that the row ( there will only be one row in the table ) will be locked whilst the transaction is in process?  I could therefore use this approach anywhere to lock rows?

Thanks
0
 
LVL 18

Accepted Solution

by:
deighton earned 500 total points
ID: 38826983
hi, that's my theory.

The theory is that once a value is updated in a transaction, the record should remain locked to prevent the change being usurped by any other process.  Then having incremented the value and having that value, you can proceed to use it.

If any other process comes along and increments the value, it is already incremented, so they get the next value.
0
 
LVL 1

Author Closing Comment

by:Petermcg001
ID: 38827006
Thanks for your help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

697 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