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.
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
