RE: Witango-Talk: BeginTransaction - EndTransaction section: does it really work?

[Robert Shubert]

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