0

I've inherited a VB.NET application that INSERTs files into a varBinary(max) in SQL Server using System.Data.SqlClient.

It creates a transaction, uses SQLCommand to INSERT the record, then UPDATES that same record passing in the bytes, this cycle loops in 2Gb chunks until the entire file has been INSERTed. The Transaction is then Committed.

This process locks the table, so if two people try to INSERT documents at the same time, the application hangs for the second person in the queue waiting for it's turn.

I've looked at Does inserting data into SQL Server lock the whole table? There's no reference to TABLOCK anywhere in the code and I can't find what the "certain kinds of bulk load operations" are it refers to but this doesn't seem like a bulk operation.

Any help or ideas how to stop it locking or any or options would be appreciated.

Glyn
  • 316
  • 2
  • 20
  • 1
    You state *"It creates a transaction", so I *assume* it's declaring an explicit transaction, rather than an implicit one. – Thom A Jul 05 '20 at 13:23
  • 1
    We shouldn't even have to say this: show us the code. – jmcilhinney Jul 05 '20 at 13:43
  • Two things I can think of as factors here: Whether you are using some kind of incrementing clustered index on the table, and whether the table is configured to force off row storage. Could you query `sys.tables` for the table in question and tell us the value of the `large_value_types_out_of_row` column? And let us know whether the clustered index is an always-increasing value, like an identity column – allmhuran Jul 05 '20 at 13:46
  • Assuming you have ruled out .NET as being the problem. I suggest you dig a little deeper to find out exactly what locks are placed and on what resources: start [here](https://www.red-gate.com/simple-talk/sql/database-administration/the-dba-as-detective-troubleshooting-locking-and-blocking/) – Alex Jul 05 '20 at 13:47
  • 1
    Check the execution plan. Perhaps it's the `UPDATE` locking the table due to a scan because of missing indexes or non-sargable expression. – Dan Guzman Jul 05 '20 at 14:46
  • 1
    @jmcilhinney you'd complain if I dumped the 500 lines of code, complain if I didn't trim it down, complain if I *did* trim it down...thanks for exemplifying SO. Everyone else, thanks I'll look into it. – Glyn Jul 06 '20 at 08:43
  • 1
    You're trying to pretend that people will criticise you no matter what you do but that's what people do when they want to justify doing the wrong thing. If you do the right thing then you won't be criticised. SO requires that you provide a minimal, reproducible example. If you can't do that then you almost certainly haven't put enough effort into understanding the problem. Do that and you won't be criticised. Dumping 500 lines of code is obviously unacceptable because it can't all be relevant. Removing relevant parts is not smart either. Post enough to demonstrate the issue and no more. – jmcilhinney Jul 06 '20 at 09:01
  • Others managed to answer the question and were helpful, you weren't – Glyn Jul 08 '20 at 09:48
  • What was the answer? You should post it here in case others have a similar problem in the future. Answering your own question is [encouraged](https://stackoverflow.blog/2011/07/01/its-ok-to-ask-and-answer-your-own-questions/) – allmhuran Jul 08 '20 at 10:30
  • 2
    I haven't found the solution yet, but when I do I'll put it on here. – Glyn Jul 08 '20 at 12:55
  • @Glyn, just wondering if you had any luck with solving your problem, or have found additional information. – Alex Jul 12 '20 at 22:57

0 Answers0