Mihai,
I believe that the assumption or request you suggested, that the code should be optimized inside a transaction so that deadlocks do not occur, is too strong and risky and in fact it denies the very concept it tries to implement. It's got to be a way of making sure that a transaction, no matter how long, takes place as a single operation.
The suggestion was that you should avoid locking every row in a table in a multi user application. We are not the only organisation to suggest this, Microsoft also write:
"Although deadlocks cannot be avoided completely, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:
• Rolled back, undoing all the work performed by the transaction.
• Resubmitted by applications because they were rolled back when deadlocked.
To help minimize deadlocks:
• Access objects in the same order.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use a low isolation level.
• Use bound connections."Still the un-answered question remains: is BT-ET supposed to block the DB as long as the transaction takes place? .... I definitely thought that BT-ET offers full exclusivity to the DB, during the transaction.
This is not correct. It does not block the db or provide exclusivity to the DB. The transaction stops multiple threads accessing locked tables or rows in the db.
So my questions are:
1- If BT-ET is supposed to block the DB while doing the transaction, how can the DB get deadlocked?
A transaction does not block the DB. The deadlock is in the db. It is the 2 threads in the server that are causing the deadlock, not 2 threads from the witango server. Yes the witango server may send multiple requests to the SQL server to begin a transaction and lock resources, but it is the SQL server that manages the session and the transaction. The issue you have is with MS SQL 2000 not witango. The error being returned by MS SQL is about locked resources and transactions in multiple threads trying to access the same locked resource.
Microsoft advise "When a transaction submitted by an application is chosen as the deadlock victim, the transaction is terminated automatically and rolled back, and error message 1205 is returned to the application. Because any application submitting SQL queries can be chosen as the deadlock victim, applications should have an error handler that can trap error message 1205". This is what the witango server is doing and we handling the error by allowing the programmer to catch it and fall into error html, clear the error and proceed.
Of course I appreciate the suggestions you made to improve my code. In this regard, I would like to make a suggestion of my own: since you provide an action item "Insert", why not provide an option to return the latest index in the DB? (I can do it using the DBMS-direct, of course, but Insert is more convenient, when possible.)
The reason is that all dbms implement this differently and there is no standard way to do this. Hence the direct dbms action is the most optimal and flexible solution.
Witango Support
On 03/06/2004, at 1:55 PM, <[EMAIL PROTECTED]> wrote:
Hello,
Thank you for your fast response.
I agree this is an error reported by MS-SQL and it is the DB that is deadlocked; actually I never implied it otherwise. But the functionality of BT-ET is provided by Witango; and this is the subject of my message.
I agree that my code was not the optimal. But that was never my intention; quite the opposite.
Of course I appreciate the suggestions you made to improve my code. In this regard, I would like to make a suggestion of my own: since you provide an action item "Insert", why not provide an option to return the latest index in the DB? (I can do it using the DBMS-direct, of course, but Insert is more convenient, when possible.)
Still the un-answered question remains: is BT-ET supposed to block the DB as long as the transaction takes place? You seem to suggest so, the documentation suggests that "BT-ET" implements a transaction as a single operation and I guess at least some of the WT users in this group thought so. I definitely thought that BT-ET offers full exclusivity to the DB, during the transaction.
So my questions are:
1- If BT-ET is supposed to block the DB while doing the transaction, how can the DB get deadlocked?
2- If that is not the purpose of BT-ET, what is its purpose?
I believe that the assumption or request you suggested, that the code should be optimized inside a transaction so that deadlocks do not occur, is too strong and risky and in fact it denies the very concept it tries to implement. It's got to be a way of making sure that a transaction, no matter how long, takes place as a single operation.
I thank you in advance for your answer.
Mihai.
-----Original Message-----
From: Customer Support [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 18:57
To: [EMAIL PROTECTED]
Subject: Re: Witango-Talk: BeginTransaction - EndTransaction section: does it really work?
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
_______________________________________________________________________ _
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
