Yes, you're correct in all senses. There are two functions in MS SQL that controls locking. One is a time out for obtaining locks on data that is already locked. This can be a dangerous situation depending on what is happening in your database.
The other is a true deadlock, more by your definition. In the code examples that Mihai had posted, an insert command was inside of a transaction, followed by a select which required the entire table to be locked. When a second request comes in at the same time, there's contention in that each one wants to add to the table, as well as read the entire table. It becomes a true deadlock in that SQL can't complete executing either request without the other request completely processing, it's stuck. This doesn't use a timer as it's immediately obvious to the server. There are descriptions, suggestions and all kinds of good information in the SQL Server BOL, and other SQL programming books. MS points out in several places to not confuse timed blocking errors with deadlock errors, as both are similar. Sorry if my last post wasn't as clear as it should've been, locking, blocking, and deadlocking is certainly a deep topic. Robert -----Original Message----- From: John McGowan [mailto:[EMAIL PROTECTED] Sent: Thursday, June 03, 2004 9:17 AM To: [EMAIL PROTECTED] 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
