Understood...
So rewriting the error thrown by MSSQL from
/Transaction was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
to
Transaction was suspected to be deadlocked on .....
would probably be a better explanation of what is going on?
/John / Alan Wolfe wrote:
I read in a CS book (computational beauty of nature, a good read hehe) that knowing whether or not a program will terminate is an NP complete problem, so is very very difficult to know if any given program will ever finish terminating or not (most often you can only know by running it and waiting...which you wont know if it will end or not until it does end, which doesnt help much for programs which execute for a long time or that dont end)
so, it sounds like when detecting deadlocks, it just uses a timeout value and if that's exceeded it's considered deadlocked since it has no way of really knowing if it's deadlocked or not since knowing that is a huge mathematical problem.
lower timeouts would make it detect faster but give more inacurate detections and higher timeouts would take longer to detect a deadlock but would also be more accurate in detecting them.
----- Original Message ----- From: "John McGowan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, June 03, 2004 6:16 AM
Subject: Re: Witango-Talk: BeginTransaction - EndTransaction section: does
it really work?
I think that part of the confusion here is the use of the term "deadlock."
I was trained in school that deadlock was a bad thing and that it meant you had a problem in your code that allowed a situation where two threads are waiting on each other and they will NEVER finish because of this deadlocked condition.
It sounds like MSSQL server is using the term deadlock in a way that doesn't match what some of us feel the appropriate definition is...
Definition of deadlock From Dictionary.com
1. _/Computer Science./_ A failure or inability to proceed due to two programs or devices both requiring a response from the other before completing an operation.
Robert, what you're saying is that if a sql query waits for more than 2 seconds inside of a transaction that the SQL server will throw a "deadlock" error and roll back that transaction.
I suggest that since the first thread might just be taking a bit longer to complete, The second thread is not deadlocked and Microsoft is misusing the term.
Is there something wrong with my analysis?
/John
Robert Shubert wrote:
________________________________________________________________________Mihai,
I can add some thoughts, which might help clarify things for you.
First off, everything you are discussing here is a function of the SQL server. Witango is simply sending the SQL statements BEGIN TRANS and COMMIT TRANS when you use the BT and ET actions, respectively.
What your database does, how it handles locking, how and when it reports a deadlock is completely up to the database.
My MS SQL setup considers a deadlock in about a second or 2, I’d have to look up the actual setting (@@lock_timeout). I use row locking and implicit transactions as is the default setup for MS SQL 7 & higher.
What you are doing is telling your SQL server to lock a row(s), enact a series of statements on the locked items, inserting, searching etc. and then releasing the rows. While that it happening, you have another process (you must think in the preemptive terms of Witango) do exactly the same thing. Your deadlock is occurring because MS SQL determines that it doesn’t have the time to complete the 2+ requested tasks, again as per the settings of your SQL server.
You should read the section on deadlocks in the SQL BOL, it’s helpful, and perhaps start a discussion on a SQL list.
I can assure you that Witango is dealing with, and responding to, your SQL server correctly, and it is the SQL db where the ‘fault’ lies. It would be possible to have Witango trap the deadlock error and re-attempt the transaction; that logic would allow you to ensure that Witango requests would /eventually /be processed, even if it took a few tries for the db to get it right.
Also note that if you are going to use row-locking, which is preferred, think of it this way:
Insert your new record with a unique identifier, such as the userreference + timestamp + random number. Then, to find that record’s ID, search for it explicitly. Because you will be adding one row, and searching for one (the same) row in your transaction, you will never have a deadlock. Of course, using a pre determined unique ID also removes the need for the transaction in the first place.
For heavy load applications, I like the stored procedure. I can put my transaction controls, inserts, identity selects all in a SP that runs many times faster (perhaps a magnitude) than Witango can execute against the SQL server, the reasons here should be obvious.
I will agree with one point, that the Witango insert action should have a means by which to execute additional SQL to retrieve the identity in a convenient “I don’t know how to use transactions properly” way, but this is more of a wish for newcomers to programming, and people who want to use Witango because it hides the scary SQL business.
A final thought, if you want to continue to use actions similar to what you have now, is to create an index which will inherently return the highest identity of the table, this will greatly increase the responsiveness of the identity and reduce your deadlocks.
One more thing for people reading this: deadlocks are a “good” thing. They are, in fact, the way an SQL server rids itself of a transaction which it feels it can not complete in a timely manner. If you think about it, if the SQL server did not do this, Witango would wait until its querytimeout and then stop the execution of the thread. This would essentially leave a transaction running on the SQL server with no way to know if it ran, or what it responded. It would essentially cause data corruption. As the Witango Support post pointed out, most SQL errors, including deadlocks, should be solved with smart(er) SQL programming.
Robert
-----Original Message----- *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *Sent:* Wednesday, June 02, 2004 11:56 PM *To:* [EMAIL PROTECTED] *Subject:* RE: Witango-Talk: BeginTransaction - EndTransaction section: does it really work?
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
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
