I have the following table in my SQL Server 2008 database:

CREATE TABLE [dbo].[SomeTable]
   [Id] [bigint] IDENTITY(1,1) NOT NULL,
   [Column1] [varchar](30) NOT NULL,
   [Column2] [varchar](30) NOT NULL,
   [Column3]  [varchar](50) NOT NULL,
  [Id] ASC

One process inserts data into this table, another process reads all data from this table. Both processes work simultaneously.

one process:
INSERT INTO dbo.SomeTable VALUES ('col1', 'col2', 'col3')

another process:
SELECT Id, Column1, Column2, Column3 FROM dbo.SomeTable

I see that second query does a full table scan, but I need to get all data from this table for further processing(it won't be a very big table, because it will be cleaned periodically. it will contain a 1K-2K rows).

Are there any way to make sure that deadlocks will never happen for this two queries?

  • 5
    Worst case scenario: you get a dirty read. Deadlocks shouldn't happen. – alex Mar 5 '12 at 14:57
  • They are both different processes but both are talking to same sql server which is single process but multi threaded. SQL server will do the locking. – Shiplu Mokaddim Mar 5 '12 at 15:03
  • Why don't you post an answer? – Sergey Smelov Mar 5 '12 at 15:05
  • @SergeySmelov: because that answer doesn't answer your question and is irrelevant here – gbn Mar 5 '12 at 15:06
  • @alex - given default locking, how would a dirty read occur? You would have to specify that you're OK with dirty reads (aka NOLOCK). – RQDQ Mar 5 '12 at 17:46

Deadlocks will always happen at some point under the default locking strategy. However, it is unlikely in your given scenario because of the straight table scan.

However, it's more likely if you have several concurrent INSERTs and SELECTs.

Using NOLOCK means dirty reads and isn't best practice. Everyone seems to suggest them though...

The alternative is to use snapshot isolation modes: the SELECT will read the last committed data, rather than being blocked by the INSERT.

  • Yes, I can have several concurrent INSERTS and SELECTS. i heard that snapshot isolation might affect on performance. Are there any other ways to minimize deadlocks? Maybe add deadlock try-catch handler? – Sergey Smelov Mar 5 '12 at 15:10
  • And what should i do to see a potential deadlocks? Perform high load tests on database? – Sergey Smelov Mar 5 '12 at 15:12
  • @SergeySmelov: it's hard to generate deadlocks unless you have exclusive locks (that block). Selects aren't exclusive but shared. Exception handling is expensive too... – gbn Mar 5 '12 at 15:16
  • @SergeySmelov to reproduce deadlocks like the one you are encountering, use the scripts in this post: "Reproducing deadlocks involving only one table" – A-K Mar 5 '12 at 18:13
  • @AlexKuznetsov how much iterations enough to make sure that deadlock will never occur? 10000? 1000000? – Sergey Smelov Mar 5 '12 at 19:30

If you can live with dirty reads, you could specify the NOLOCK table query hint. If the only operations that are occurring are inserts, and you're not joining with data in other tables, this might be a viable solution.

Please note that the NOLOCK hint has been overused and abused in many scenarios. Also, note that it is a hint, and not a directive. In otherwords, I do not believe that it is guaranteed to prevent locking under all scenarios.

  • Do you think this two queries are deadlock free? – Sergey Smelov Mar 5 '12 at 15:03


But it may lead to other Data Consistency issue if we are using it in a complex environment . For example if Insert is the part of Transaction and you read out using select and Transaction get failed. Then in this scenario your insert statement may rollback and the selected result is corrupted. So if you want to use it make sure Insert isn’t the part of Transaction. For more understanding you can refer this page


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.