Mihai ,
Just to clarify this issue. It is the database that is deadlocking and not the witango server. The error is being returned from the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Looking at the taf from a programming point of view and the db load, I think the actions get_latest_userID and get_this_index are rather inefficient and will become slower as more records are added to the database. This is not the type of query you want to add to a read/write exclusive transaction in a multi user system. Read/Write exclusive locks rows that are read as part of the transaction until a COMMIT or ROLLBACK command is issued to the database server. I would suggest rethinking how these action are implemented and reduce the load and locks in the db.
In these actions it appears that you are trying to get the userID of the row that you just added to the db. Most dbms have a simple efficient way of doing this. For instance in many dbs you can return the identity of the last record inserted with the following SQL or something similar:
select <@literal @@identity>
The method you have implemented in the actions retrieves every row of the database (as no criteria is set) and sorts it desc based on the modifiedTS. You then get the first row. Assuming that you are using record locking and not table locking on the db this in effect will lock every record in the table.
An alternative to the suggestion above, a simpler more efficient mechanism to retrieve the identity of the last record inserted would be if you used the "Summary of all Rows" option on the select action and use a MAX function to get the maximum userID also limit the number of records in the MAX() calculation by adding a criteria based on a value greater than a particular modifiedTS, e.g. a time stamp within the last 300 seconds.
________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
<<inline: SummaryOfAllRows.gif>>
This would lock less records and return less data to the witango server.
Witango Support
________________________________________________________________________Hi everybody,
While checking the timing needed for different database operations I noticed that repeatedly pressing (very fast) the reload button on my browser would generate a deadlock error (see below). How is this possible? Each database access is included into a BeginTransaction - EndTransaction section. I could not reproduce this problem for small and simple databases, like the Nortwind or Pubs DBs that come along with the MS-SQL. Still the problem is troublesome, because my DB is fairly complex and I depend on BT-ET to provide unique access to the DB and protect against multi-access while doing critical operations.
I have included the code, which is very simple, if somebody would be curious enough and had the inclination to find the problem. My configuration: Windows 2000 pro, MS-SQL 2000, Apache 2, Witango Server; all updated.
Is anybody else getting the same problem?
Many thanks for any suggestions you may have. Mihai Olariu
PS: A solution to this problem is to include my own semaphores around the BT-ET sections. This, I verified, solves the problem indeed. But this would be like wearing belt and suspenders on the same pair of trousers. Gosh, I hope I am wrong...
Error
An error occurred while processing your request:
File: dbTest1.taf Position: get_latest_userID Class: DBMS Main Error Number: 1205
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 40001
File: dbTest1.taf Position: get_latest_userID Class: Internal Main Error Number: -101
General error during data source operation.
File: dbTest1.taf Position: get_latest_userID Class: Internal Main Error Number: -101
General error during data source operation.
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
