7

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,
   CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED 
   (
  [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?

Improve this question
5
  • 5
    Worst case scenario: you get a dirty read. Deadlocks shouldn't happen.
    – alex
    Commented Mar 5, 2012 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
    Commented Mar 5, 2012 at 15:03
  • Why don't you post an answer?
    – Sergey Smelov
    Commented Mar 5, 2012 at 15:05
  • @SergeySmelov: because that answer doesn't answer your question and is irrelevant here
    – gbn
    Commented Mar 5, 2012 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
    Commented Mar 5, 2012 at 17:46

3 Answers 3

Reset to default
8

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.

Improve this answer
7
  • 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
    Commented Mar 5, 2012 at 15:10
  • And what should i do to see a potential deadlocks? Perform high load tests on database?
    – Sergey Smelov
    Commented Mar 5, 2012 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
    Commented Mar 5, 2012 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
    Commented Mar 5, 2012 at 18:13
  • @AlexKuznetsov how much iterations enough to make sure that deadlock will never occur? 10000? 1000000?
    – Sergey Smelov
    Commented Mar 5, 2012 at 19:30
1

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.

Improve this answer
1
  • Do you think this two queries are deadlock free?
    – Sergey Smelov
    Commented Mar 5, 2012 at 15:03
1

SELECT COUNT(*) FROM Student WITH(NOLOCK)

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

http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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