July 1, 2003 at 4:25 pm
I would love the developers to change the code so they use the @@Identity. This is a much better solution than using NOLOCK. Unforetunatly, for this move to SQL 2K, management wanted to not make any code changes. I ran the trace and found that the transaction isolation level is not being explicitly set. So it must be using the default. I also had the connection usage wrong. It actually is like this: Begin Tran, Insert into Table1 (on connection1), Select max() from table1 (on connection1) then insert into table2 (on connection2). The second connection is not intentional. The ado commands for these three steps are: 1) gadoConn.execute (Insert into Table1), 2) rsTemp.Open (select max()), gadoConn, adOpenForwardOnly,,adCmdText and 3) gadoConn.execute (Insert into Table2)
We are guessing that the rsTemp.Open statement is keeping the connection tied up so when the next gadoConn.execute fires it has no choice but to use a new connection. It turns out that the trigger is an Update trigger and of no consequence to this problem. We did discover two ways to make this problem go away. One was to change the rsTemp.Open statement to gadoCommand.execute which eliminates the second connection and the other was to drop the Foreign Key in Table2 pointing to Table1. FK's were added to this table and a number of others as part of this migration and when I removed the one on Table2, the 2nd insert statement was able to complete. Eventhough I personnally demonstrated that removing the FK resolved this isssue, I really have no idea why their presence would cause the hang. So. for the difference between SQL 7 and SQL 2k, the answer is the FK's.
Mark Hottinger
Mark
July 1, 2003 at 4:46 pm
I suspect that FK caused the problem because the new connection is waiting for the other connection to commit and release its locks so that the new conn. can check the inserted FK values for validity.
This ought to happen only with trans. iso. level set to 'repeatable read' or higher - UNLESS - ADO is holding locks, maybe by using adLockPessimistic server-side cursors.
I don't agree with your assessment of how the second connection is created, though - I've never encountered ADO 'branching' the connection object to use two separate SPIDs. This would be undocumented and bizarre behaviour - and as you have seen, there is a good reason for it's not doing so.
But if as your code shows, the you specify the same connection in your rs.Open call, I don't see how the rst could end up on a different connection...any ideas, anyone?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 1, 2003 at 4:51 pm
Could you not just rsTemp.Close after reading the max value?
Yes - I have seen the ADO objects keep information locked beyond the original intent. We had several lockups similar to this one. In our situation, we could use the NOLOCK because we were not updating that information.
Guarddata-
July 1, 2003 at 4:58 pm
Yes - agree with GuardData - closing the rocordset ought to release any locks it is holding - in some configurations of the lock- and cursor- type parameters, the rst will act like a scroll cursor, holding a lock on the current record, or even all the records, until you close it.
quote:
We did discover two ways to make this problem go away. One was to change the rsTemp.Open statement to gadoCommand.execute which eliminates the second connection
Was this not a satisfactory solution?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 2, 2003 at 12:13 am
"It actually is like this: Begin Tran, Insert into Table1 (on connection1), Select max() from table1 (on connection1) then insert into table2 (on connection2)." Because your Insert is still uncommitted, it is not the max-function (cursor or not) that holds your locks (it can give you a reliable result because it with the same connection, so as part of your transaction).
Two solutions : 1) avoid the second connection and perform all in a single connection/transaction.
2) commit your insert before you insert from your second connection.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 2, 2003 at 3:05 am
note : when use [ Select max() from table1 ] make it .. [ Select max() from table1 with (Nolock) ] .. to not take any lock.
it is very useful to use (With (Nolock)) when getting data (select) ..
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
July 2, 2003 at 4:22 am
Using nolock means that transactions are no longer honoured. You can read data which has not been (and might never be) committed. Equally you might be unable to see data which has been deleted but is subsequently rolled back. You don't want to use nolock without a thorough investigation of the consequences.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 2, 2003 at 8:04 am
We also have seen differences between SQLServer 7 and 2000. We looked for the differences with the profiler on both servers (And sqlserver 2000 with 70 compatibility) looking using sql statements, stored procedures , RPC's (used by our centura application and I have heard also by ADO) and locks.
Save the results to text files and use windiff.
If you are curious, in our case there were no statement differences but was SQLServer 2000 with 80 compatibility much slower than SQLServer 7 and SQLServer 2000 with 70 compatibility, resulting in long held locks.
July 2, 2003 at 8:56 am
Sometimes I overlook things. You indicated that "The ado commands for these three steps are: 1) gadoConn.execute (Insert into Table1), 2) rsTemp.Open (select max()), gadoConn, adOpenForwardOnly,,adCmdText and 3) gadoConn.execute (Insert into Table2)"
Where is it that you see the second execute happening on a different connection? Is there a transaction started on the connection before these commands are executed? If not - each one should be independant of the others anyway and this routine would be unsafe for multi-user environments. If so - something else must be there to cause the second execute to be a separate connection since it is the same variable.
I know the manager doesn't want to change code - but if it is not working, seems like you have justification. Is it possible to wrap this into a stored procedure instead?
There must be something else hidden - this code by itself would not execute on two separate connections.
Wish we could be there to provide a second pair of eyes - seems like it must be something subtle.
Guarddata-
July 2, 2003 at 9:47 am
In a previous reply I did indicate that there was a BEGIN TRAN on the first connection before the first Insert. And I believe you are right that just closing the recordset might be enough to prevent the second connection from being created. I observered the second connection in Query Analyzer while the app is waiting for a timeout to occur. It is also evident in the profile trace. The 2nd connection has to be outside the transaction of the 1st connection and therefore waiting for the lock to check the foreign key as Stax68 indicated.
It is amazing how many wats bad coding can get you.
Thanks to everyone for their help. : )
It may be a few days before a decision is made but I'll let eveyone know which way we go.
Mark Hottinger
Mark
July 2, 2003 at 10:09 am
I (and Guarddata, it appears) still don't believe that the code as you describe it would open a new connection. Something else is going on in the code.
You might want to track this down, as the same thing might be happening elsewhere, leading to blocking and corrupt 'transactions'.
The only thing I can think of would be use of the adAsynchFetchNonBlocking enum, but it's not in your code - could try a search through the VB source code?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 23, 2003 at 3:34 am
ADO spawns additional connections! May not be the cause of your problem, but is interesting anyway...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;194979
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 23, 2003 at 10:47 am
Notice that the article says two connections are generated when two "firehose" queries are used. Closing the recordset would assure that this would not be the case.
Guarddata-
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply