June 8, 2018 at 1:21 pm
Hi, we are experiencing locking issues recently. We use Stored Procedures extensively for our database calls. We have a stored procedure that inserts into a table. This SP has transaction wrapping around the INSERT. we have another stored procedure to read records from the same table. we are noticing that the read SP is timing out in our application. we are thinking it is because the SQL insert SP has transaction wrapping. so, it is placing locks on the table.
since the application that calls the read SP is the ONLY application that updates the table, can we use (nolock) option on read SP or does it return dirty data as well? What are some of the alternatives for this problem?
Thank you in advance.
June 8, 2018 at 4:24 pm
Sridhar-137443 - Friday, June 8, 2018 1:21 PMHi, we are experiencing locking issues recently. We use Stored Procedures extensively for our database calls. We have a stored procedure that inserts into a table. This SP has transaction wrapping around the INSERT. we have another stored procedure to read records from the same table. we are noticing that the read SP is timing out in our application. we are thinking it is because the SQL insert SP has transaction wrapping. so, it is placing locks on the table.since the application that calls the read SP is the ONLY application that updates the table, can we use (nolock) option on read SP or does it return dirty data as well? What are some of the alternatives for this problem?
Thank you in advance.
You can use nolock anywhere as long as you don't care if the read results are accurate. Yes you can read data that ends up being rolled back.
An insert will take the lock whether it's wrapped in a transaction or not. It may or may not be locking the entire table depending on lock escalation or locking hints. You would want to see what's actually going on with the locks.
One (first?) alternative is to look at the insert stored procedure and see what you can do to improve that. If it is escalating due to the number of rows, if you can batch the inserts, if you have unnecessary things in the transaction. You want those as quick as possible. Even if you use any alternatives to nolock you still want the transactions to be fast, just have the actual transaction.
Without knowing your database it's hard to say as there are cases where you can implement something for the read only data or caching data. It really depends on a lot of factors. Another alternative is to look at using another isolation level. But it's not a magic fix where you flip the switch, change it and all is well and good. You really need to research the effects of going this route, changes to how it may impact your application, etc. Read committed snapshot isolation level is often looked into as an alternative to using nolock. The following article discusses some of the changes with isolation levels you may want to test:
Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide
Sue
June 9, 2018 at 4:40 pm
This was removed by the editor as SPAM
June 9, 2018 at 4:42 pm
This was removed by the editor as SPAM
June 11, 2018 at 9:03 am
Sridhar-137443 - Friday, June 8, 2018 1:21 PMHi, we are experiencing locking issues recently. We use Stored Procedures extensively for our database calls. We have a stored procedure that inserts into a table. This SP has transaction wrapping around the INSERT. we have another stored procedure to read records from the same table. we are noticing that the read SP is timing out in our application. we are thinking it is because the SQL insert SP has transaction wrapping. so, it is placing locks on the table.since the application that calls the read SP is the ONLY application that updates the table, can we use (nolock) option on read SP or does it return dirty data as well? What are some of the alternatives for this problem?
Thank you in advance.
And in addition to Sue's advice, perhaps answer yourself this question... Why do you want to read from a table that you are busy inserting into? Also, does your application potentially lock the table for the entire time it takes the user to input the data? That would be particularly bad, and almost impossible to get a good result with. Fix that, and everything else may well fix itself.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 11, 2018 at 12:42 pm
Thank you for the solutions. I will look into them.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply