Any ideas on how to reproduce these deadlock errors without a tool?
The error message is as follows
Run-time error '-2147467259 (80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction(process ID #110) was deadlocked with another process and has been chosen as the deadlock victim. Reeun your transaction.
I have tried accesing the same database procedure from different machines at the same time but with no results.. any ideas??????
I have not failed. I've just found 10,000 ways that won't work.
- Thomas Alva Edison (1847-1931)
I have had this problem with SQL Server 2000. It's really something that you have to find in your application. You have to find a reproducable test case using your application, apply a fix, and rerun the test case.
Our problem turned out to be the cursors setting in our jdbc properties file that was used by the data direct jdbc driver. I think we removed the cursors setting and it worked.
We found a lot of helpful information in the Microsoft knowledge base.
What you need to do is to monitor the SQL traffic coming into the database. Once the event has passed, there really isn't a way to know what's deadlocked, unless you have some form of logging enabled. Look through the Microsoft's knowledge base, and see if you can find a log. If not, then you have to manually reproduce it.
If you can reproduce it, the easiest way to trace (though it's cumbersome), is to enable the odbc trace, which I am assuming you are using. This will generate a large odbc trace file in the desginated area. Step through the SQL commands and see which call returned an error and you should be able to see the error, as well as the sql statement it got stuck on. From there, you should be able to figure out which table is locked.
I haven't used SQL server for a long time, but I'm assuming that MS's admin UI should provide a tool that will tell you who's locking the table. If this is a multi-access database design, then the there should only be one process handling a particular table. Often, this is done through usage of stored procedure calls run at a particular interval. You can suggest using 'side tables', which is populated (without a lock), then a stored procedure is used to spool the task given time/date/id and so forth. Anyhow, a developer who's experienced will know what to do to avoid a race condition.
NOTE: ODBC file will be large, so make sure you stop it after the operation will be done. You will also see substantial performance loss, which might make reproducing this problem more difficult.