Hi,

Multi user ASP.Net website, SQL Server backend.

When using transactions to insert multiple rows into a table and then commiting them once you're happy that everything looks good, the table(s) gets locked before commiting.

Basically, I want to perform updates and inserts under a transaction but still allow other users to carry on using the site and potentially the tables that are being used with the transaction.

Question: Is there a way to stop the table lock occurring and for SQL to just do a record lock?, thus avoiding blocking other users?

I know that a possibility is to use Snapshot replication, but am a little worried about turning it on due to the small team we have for testing.

Many thanks,

Julian

You want to lock the data waiting for the end user?  In that case you should not use database locking for this purpose.  It's something you would build into your application. 

One option is to add a "last update timestamp" column to your tables.  And when the user saves the data you open a transaction, check that the last update statement is still the same as when the data has been loaded, and then update the data and close the transaction.

• Edited by Pygoc 15 hours 36 minutes ago

Hi,

Hmmm, not sure I made my self clear then. 

The specific situation is this:

User 1: Uploads a file. Application extracts the data from the file and inserts it into the DB (under a Transaction). The application then validates the data that is stored in the DB (in various ways) to confirm that all is OK. Then the Commit happens if all is indeed OK.

User 2: Whilst User 1 is doing the above, User 2 should be able to carry on inserting, selecting, updating or whatever it is that they are doing.

Using normal Transactions, the tables will be locked whilst uploading, stopping User 2 from doing what they need to do.

User 2 will not have access to any of User 1 data. Just the same tables. Think - multiple Employers/Employees.

Julian

You want to lock the data waiting for the end user?  In that case you should not use database locking for this purpose.  It's something you would build into your application. 

One option is to add a "last update timestamp" column to your tables.  And when the user saves the data you open a transaction, check that the last update statement is still the same as when the data has been loaded, and then update the data and close the transaction.

• Edited by Pygoc Thursday, September 03, 2015 3:30 PM

You want to lock the data waiting for the end user?  In that case you should not use database locking for this purpose.  It's something you would build into your application. 

One option is to add a "last update timestamp" column to your tables.  And when the user saves the data you open a transaction, check that the last update statement is still the same as when the data has been loaded, and then update the data and close the transaction.

• Edited by Pygoc Thursday, September 03, 2015 3:30 PM

Hi GiantJulian,

In the default isolation level, read committed, your requirement is a typically scenario how this isolation level works, as long as your table has a unique index and not under a lock escalation.

User 1: Uploads a file and processes other actions within a transaction and that transaction won't commit or rollback  until everything is done or any error occurs.

User 2: While the User1's transaction is processing,User 2 should be able to carry on selecting, updating via a where like unqiueColumn=? and inserting a row that doesn't violate the unique constraint.

Using normal Transactions, the tables will be locked whilst uploading, stopping User 2 from doing what they need to do.
This might be due to nonexistence of a unique index ,a lock escalation or not specifying a where clause forementioned.

If you have any feedback on our support, you can click here.