SQL Server 2005 DeadLock/Timeout

Our team has developed application by .NET for import data into SQL Server 2005 Database (around 500 records/per times) . But some times database have deadlock or timeout issue occur when users import data in office hour . So cause system seems hang & users can't use system . We need to stop this process then system can be resume . Of course, i guess some users may edit importing record at the same time to cause error occur . Import data procedure as below : -

I believe you're asking this in the wrong place, you need to ask over on Stack Overflow. See, the problem is in your code, not the server. Deadlocks occur because two people request the same resources and they refuse to get out of each others way. SQL Server does have a way to handle this, it's the timeout, where it kills both operations and forces them to wait a random amount of time to stay again. I've spent my whole career with bad programmers trying to blame this on the server, but it's the responsibility of the programmer to handle a deadlock situation gracefully. It is impossible to avoid deadlocks, they're a fact of life when dealing with a database. Sorry, but no amount of tweaking on the server will change it.

Hi BorisLI,
I assume the problem is with an open transaction that your code have, you
see without committing the transaction SQL Server will return the timeout
response especially when its threshold is a fixed one..check for this type
of timeouts in your code for any long running query
or uncommitted transaction..

My first concern would be whether sequence of updates matters. Posting a deposit and a withdrawal in the wrong order might incur or avoid charges incorrectly. Posting the balance resulting from those transactions in the wrong order could be far worse.

It may be better to feed all of the speadsheet data into a process that applies the updates sequentially. SSIS does this very well if that is an option for you.

If you have online transactions concurrent with the batch updating, you must consider the possibility of out of sequence processing from that as well.

Many thanks, every expert replied this post first.
Let's try to reply to each one of the below.

1) Have checked thru profiler to trace. The reason is Users editing record during import/updating data. Please note that editing user doesn't know somebody to import/update data into his/her record.
But they can't allow schedule import/update data because that will affect their efficient.
3) Sure have enough index.
4) Is #TempTable
5) I think not is open transaction or uncommited issue. As per point 1, User need to editing data, but another user needs to import/update data in same record at the same time.
6) No table index rebuild periodically.
7) SSIS can do this same function and will not have deadlock / timeout issue occur?
8) Can't create app to look like Excel because the Excel data sent from other party, not our user update it.
9) Can't schedule it because that will affect their efficient.
10) Around 300 users and table must have index.

In fact, I would like to know how do you to solve these problem if you face with your users ?
In database technology, is it impossible to control this one ? New SQL server have function to control that ?

What kind of function so you think any database has to deal with multiple users editing the same record? Basically, the database will block and it's up to your application to figure out if through record has hanged by someone else since the last time you read it (usually time stamp or a hash) and the proceed accordingly. How would the database know which users update is correct?

This (record locking conflicts) is more of a conceptual issue than a technical one. Think of it this way - if you had a document and two people wanted to make changes to it at the same time, how would you be able to resolve those changes if both people changed the same sentence? Say one of them wanted to change the phrase "Ben caught a blue fish" to read "Ben caught a red fish" while the other was busily changing it to read "John caught a blue fish." Is the resulting sentence "John caught a red fish?" That isn't what either of the two editors wanted, and may well be as or more inaccurate than the original in addition to not being what either of the two people making changes expects. The only way to resolve this conflict is if one person makes a change, then the next person makes their change after the first change has been applied so that they are seeing the revised document as their starting document.

This is basically how record locking works. There is no technical way around this, because it is *desired* behavior. You want databases to behave this way, because otherwise you risk getting nonsensical and inaccurate results. If you have an application that isn't able to deal with this, or a business process that conflicts with it, then it is the application or business process that is broken and needs to be fixed.

I worked in a portal at one time. We allowed users to upload Excel files to be processed. There were two approaches:
A) the user uploads data to a intermedia table in another database, later is inserted in the live data.
B) data is uploaded only in specific times directly to the live data.

All the solutions came down to administrative procedure and business policies.