59673

How do I lock certain SQL rows while running a process on them?

Question:

My work has a financial application, written in VB.NET with SQL, that several users can be working on at the same time.

At some point, one user might decide to Post the batch of entries that they (and possibly other people) are currently working on.

Obviously, I no longer want any other users to <strong>add</strong>, <strong>edit</strong>, or <strong>delete</strong> entries in that batch <strong>after</strong> the <strong>Post process</strong> has been initiated.

I have already seen that I can lock <strong>all</strong> data by opening the SQL transaction the moment the Post process starts, but the process can be fairly lengthy and I would prefer not to have the Transaction open for the several minutes it might take to complete the function.

Is there a way to lock just the records that I know need to be operated on from VB.NET code?

Answer1:

If you are using Oracle you would <strong>Select for update</strong> on the rows you are locking.

here is an example

SELECT address1 , city, country FROM location FOR UPDATE;

Answer2:

You probably want to set an isolation level for the entire transaction rather than using <em>with (rowlock)</em> on specific tables.

Look at this page:<br /><a href="http://msdn.microsoft.com/en-us/library/ms173763.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms173763.aspx</a>

Specifically, search within it for 'row lock', and I think you'll find that READ COMMITTED or REPEATABLE READ are what you want. READ COMMITTED is the SQL Server default. If READ COMMITTED doesn't seem strong enough to you, then go for REPEATABLE READ.

<strong>Update:</strong> After reading one of your follow up posts, you definitely want repeatable read. That will hold the lock until you either commit or rollback the transaction.

Answer3:

add

with (rowlock)

to your SQL query

<a href="http://www.sql-server-performance.com/articles/per/lock_contention_nolock_rowlock_p1.aspx" rel="nofollow">SQL Server Performance article</a>

<strong>EDIT:</strong> ok, I misunderstood the question. What you want is transaction isolation. +1 to Joel :)

Answer4:

wrap it in a tran use an holdlock + updlock in the select

example

begin tran select * from SomeTable (holdlock,updlock) where .... processing here commit

Recommend

  • Why does opening a connection throw a distributed transactions error in MySQL? (.NET Connector)
  • uncommittable transaction is detected at the end of batch. the transaction is rolled back
  • Hibernate session.beginTransaction() call and Mysql Start Transaction
  • Exit from a loop that contains time delay immediately after a key is pressed
  • Access 2007 forms with parameterized RecordSource
  • Grails Cannot get property 'id' on null object
  • Opening links in a new tab and only the new tab
  • How can I sum two different columns at once where one contains Decimal objects in pandas?
  • Variant from android-autofittextview library : scaling makes strange behaviour
  • Using multiple input pipelines in TensorFlow
  • Issue with SVN Commit for certain File Extension
  • XBee Linux Serial Port on Rasberry Pi
  • Opening two instances of InAppBrowser (_system and _blank) prevents events from triggering
  • in batch how do i use taskkill properly
  • Local Development, Apache vs Developer - file permissions
  • Magento get URL before current
  • Prevent Tomcat from caching request during starup
  • How do I signal completion of my dataflow?
  • How to use JavaScript to determine whether a file exists in a directory?
  • Should I or shouldn't I use the CachingConnectionFactory with hornetq 2.4.1
  • Marklogic : Query response time is very high
  • print() is showing quotation marks in results
  • Illegal mix of collations for operation for date/time comparison
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Can I make an Android app that runs a web view in Chrome 39?
  • A cron job substitute?
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • KeystoneJS: Relationships in Admin UI not updating
  • trying to dynamically update Highchart column chart but series undefined
  • embed rChart in Markdown
  • LevelDB C iterator
  • Linking SubReports Without LinkChild/LinkMaster
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Binding checkboxes to object values in AngularJs
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?