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

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

<p>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.</p>

<p>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.</p>

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

[{'answer_id': 46054, 'author': 'Jimmy', 'author_id': 4435, 'author_profile': 'https://Stackoverflow.com/users/4435', 'pm_score': 0, 'selected': False, 'text': '<p>add </p>\n\n<pre><code>with (rowlock)\n</code></pre>\n\n<p>to your SQL query</p>\n\n<p><a href="http://www.sql-server-performance.com/articles/per/lock_contention_nolock_rowlock_p1.aspx" rel="nofollow noreferrer">SQL Server Performance article</a></p>\n\n<p><strong>EDIT:</strong> ok, I misunderstood the question. What you want is transaction isolation. +1 to Joel :) </p>\n'}, {'answer_id': 46089, 'author': 'minty', 'author_id': 4491, 'author_profile': 'https://Stackoverflow.com/users/4491', 'pm_score': 2, 'selected': False, 'text': '<p>If you are using Oracle you would <strong>Select for update</strong> on the rows you are locking.</p>\n\n<p>here is an example</p>\n\n<pre><code>SELECT address1 , city, country\nFROM location\nFOR UPDATE;\n</code></pre>\n'}, {'answer_id': 46127, 'author': 'Joel Coehoorn', 'author_id': 3043, 'author_profile': 'https://Stackoverflow.com/users/3043', 'pm_score': 1, 'selected': False, 'text': '<p>You probably want to set an isolation level for the entire transaction rather than using <em>with (rowlock)</em> on specific tables.  </p>\n\n<p>Look at this page:<br>\n<a href="http://msdn.microsoft.com/en-us/library/ms173763.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms173763.aspx</a></p>\n\n<p>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.</p>\n\n<p><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.</p>\n'}, {'answer_id': 52304, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 0, 'selected': False, 'text': '<p>wrap it in a tran use an holdlock + updlock in the select</p>\n\n<p>example</p>\n\n<pre><code>begin tran\nselect * from\nSomeTable (holdlock,updlock)\nwhere ....\n\n\nprocessing here\n\ncommit\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/46034', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']