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 a simple application.This application update data of a table (B) from another table(A) which is changed every 60 sec. User may read data at any time so creating deadlock. How is this sort of problem solved in real world. Considering maximum 100 users how could I avoid deadlock. How would web application handling thousand of request avoid deadlock?

Edit:

Select Query : Select * from tableB
Update Query : update table tableB set tableB.someCOl = tableA.someCol where tableA.col1 = tableB.col3

enter image description here

share|improve this question
2  
What is the structure of A and B including indexes? What do the update and select statements look like? –  Martin Smith Jan 14 '12 at 12:03
add comment

2 Answers

up vote 2 down vote accepted

The simplest way to reduce deadlocks - proper indexing. Make sure, that client's read requests not involving table/clustered index scans - if any - this is the open gates to deadlocks

And make sure, that your index has its allow_page_lock and allow_row_lock options ON

share|improve this answer
    
Could you please provide some illustration –  kinkajou Jan 16 '12 at 0:09
add comment

A deadlock involves two resources accessed in different order by different processes. For example:

  • Process 1 reads from table A and then updates table B
  • Process 2 reads from table B and then updates table A

This generates a deadlock when both processes are halfway done. Process 1 has a lock on table A and waits for a lock on table B, and process 2 has a lock on table B and waits for a lock on table A. SQL Server resolves this situation by killing one of the two processes and logging a deadlock event.

But in the situation you describe there is only one shared resource, table B. That is unlikely to generate a deadlock. When the update begins, existing reads will complete but future reads are put on hold. The future reads won't deadlock but wait.

share|improve this answer
    
SQL Server requires locks for reading from the database (SELECTs) –  kinkajou Jan 14 '12 at 12:33
1  
@Kitex: Yes, but it will put new selects on hold while an update is running, or waiting to run. A deadlock doesn't occur when a row is currently locked; it only occurs when multiple process block eachother. –  Andomar Jan 14 '12 at 13:07
3  
A SELECT and UPDATE can still deadlock each other at read committed on a single table. See the section One connection never acquires and never needs exclusive locks, still a deadlock. –  Martin Smith Jan 14 '12 at 13:59
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.