Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 5 '12 at 17:20

This question came from our site for professional and enthusiast programmers.

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 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
add comment

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
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" –  AlexKuznetsov 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
show 2 more comments

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.

share|improve this answer
    
Do you think this two queries are deadlock free? –  Sergey Smelov Mar 5 '12 at 15:03
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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