Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CorpDirect
Yak Posting Veteran
92 Posts |
 Posted - 2005-11-18 : 14:45:35
|
We have been experiencing some process deadlock errors on our production MSSQL instance. Though I've done some googling on deadlocks, and and seen suggestions to do things such as "use trace flag 1204 to get more information about locking", I don't know enough about the condition or the MSSQL DBMS environment to investigate and determine the cause. I would be very thankful for any feedback or suggestions anyone can offer.Our environment:-Windows 2000 Server, 2GB RAM, OS and MSSQL installed on 1 RAID5 array, datafiles and logs on 2nd RAID5 array~ server also provides Exchange, file, print, and AD DC services-Accounting application written in FoxPro uses MSSQL as backend app-MSAccess apps use MSSQL linked tables-web server (apache+PHP) uses MSSQL data for reporting, and some transactions; more transactional applications under developmentI know this is not ideal, and we're going to have to separate some of these services onto other boxen soon; but in the meantime that's what we've got; if I can identify the cause of the locking issue it will give us time to make infrastructure changes.Specific areas of concern -- please tell me if you think any of these could be the cause of any problems:1) Accounting app has its own Foxpro table structure and temp tables; an operation (such as saving a new order) on an older, slower computer (like a Pentium III 700MHz) can take several seconds to complete. Is it possible for FoxPro (or any outside application) to lock tables in MSSQL during its transaction, and not rely on MSSQL's built-in record-locking safeguards? If so, this may be happening; the vendor has not said but it would not surprise me at all. The app uses FoxPro temp tables in memory, temp files on the user machine, FoxPro datafiles on a network share, and a temp database in MSSQL while processing transactions, reports, etc.2) We access some data from the accounting application's tables using views. Some of those views include data from tables built for our custom apps, and some are used to write data to those other tables. The application does not permit data to be written to the accounting tables, and the users have been granted only PUBLIC and db_datareader permissions on the accounting database, but I have to wonder if inserting or updating records through a view that includes tables from the accounting database could cause problems, or at least cause locks on those tables even though nothing is being written to them. The user applications are written in MSAccess 2000 and in PHP.3) Our reporting applications hit the database a dozen or more times during each report generation to execute stored procedures. Each of these selects data and returns it to the web server, which generates HTML or PDF reports on the fly. Each SP is granted EXECUTE by the web user created for this express purpose. The actual amount of data returned is not huge, maybe a few hundred records at most, but the number of procedures run is necessary due to the reporting requirements. It's not just header and data; there are several sets of data which may contain one or many records, so a stored procedure is used for each potential multi-record result. These reports sometimes report a process deadlock error, which doesn't make sense to me as none of them are trying to write ANYTHING back to the data. Shouldn't locking only occur during writes, i.e. insert or update?There are some necessarily ugly joins and unions between tables in the accounting database, because that application splits data into current and history tables; also, orders whose processing overlaps a period close are maintained in both current and history tables, with inconsistent handling of record updates across current and hitory tables throughout the application. So, curr/hist records must be compared using UNION to ensure distinct records. What's worse, some of the columns are text type and have to be converted to VARCHAR to be used in UNION. Messy! ...but we've no control over the accounting app's behavior.Here's an example of a deadlock error that was returned via the web reporting interface while attempting to generate a PDF report. (Other PHP and PDFgen errors were also returned, having to do with being unable to complete the script because data is missing, but I left them off as irrelevant.)Your transaction (process ID #90) was deadlocked with another process and has been chosen as the deadlock victim. Again, any suggestions on how to investigate the error further, educated guesses on the cause of the error, or recommendations on how to improve the environment or (controllable) design and configuration would be appreciated.Thanks,Daniel |
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2005-11-18 : 14:58:39
|
| Oh and, Madhivanan, I have read the article you linked (http://sql-server-performance.com/deadlocks.asp) on a recent post about deadlocks, but I'm not sure it helps me; the reports generated from the website get this error most often, but I can't think of any way to use (for example) UPDLOCK to help with the "whole transaction" -- the reporting process executes a number of stored procedures in sequence to collect the information needed to generate the report.Daniel |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-20 : 22:02:48
|
| Have you tried running profiler?This will help you target the problem, read more about profiler in BOL and see which areas will help you.This is resource intensive and I suggest you notify your users of this procedure so you can avoid the unnecessary calls that the server is slow.HTH--------------------keeping it simple... |
|
|
|
|
|
|
|
|
transaction process deadlocked