Search results matching tag 'Ado.Net'

After the timeout

Alexander Kuznetsov — Wed, 09 Jan 2013 18:23:00 GMT

After the timeout, we may sometimes, but not always, end up with an outstanding transaction. If this is the case, we need to rollback the transaction ourselves. Otherwise the consequent commands which use the same connection might not work as expected.

Let us debug through some client code and see what is happening in more detail.

Prerequisites

We shall need a few database objects, as follows:

CREATE TABLE dbo.Log1(InTran VARCHAR(20)); GO CREATE PROCEDURE dbo.TestTran AS BEGIN IF @@TRANCOUNT > 0 BEGIN INSERT dbo.Log1(InTran) VALUES('In Tran'); END ELSE BEGIN INSERT dbo.Log1(InTran) VALUES('Not In Tran'); END ; END ; GO CREATE FUNCTION dbo.IWillCompleteInNSeconds ( @NumSeconds INT ) RETURNS INT AS BEGIN ; DECLARE @StartTime DATETIME , @Toggle INT ; SELECT @StartTime = GETDATE() , @Toggle = 1 ; WHILE DATEDIFF(SECOND, @StartTime, GETDATE()) < @NumSeconds BEGIN ; SET @Toggle = 1 - @Toggle ; END ; RETURN 0 ; END ; GO CREATE PROCEDURE dbo.IWillTimeOut AS BEGIN ; BEGIN TRANSACTION ; SELECT dbo.IWillCompleteInNSeconds( 5 ) AS SomeNumber ; COMMIT ; END ; GO

Running the example

In the following test, add three breakpoints at the placeholder statements:

[Test] public void TimeoutTest() { for (var i = 0; i < 1000; i++) { using (var conn = new SqlConnection(localConnString)) { conn.Open(); try { const string iwilltimeout = "EXEC dbo.IWillTimeOut"; ExecuteSql(conn, iwilltimeout, 1); } catch (SqlException e) { System.Console.WriteLine(e.Message + " " + e.Number); } const string saveTrancount = "EXEC dbo.TestTran"; ExecuteSql(conn, saveTrancount); System.Console.WriteLine("Breakpoint placeholder 1"); } using (var conn = new SqlConnection(localConnString)) { conn.Open(); const string saveTrancount = "EXEC dbo.TestTran"; System.Console.WriteLine("Breakpoint placeholder 2"); ExecuteSql(conn, saveTrancount); System.Console.WriteLine("Breakpoint placeholder 3"); } } }

Let us start a profiling session that captures individual SP:StmtStarting and SP:StmtCompleted events. Also let us debug through this code.

First breakpoint - outstanding transaction

When we stop at the first breakpoint, let us run the following query:

SELECT * FROM dbo.Log1 WITH(NOLOCK)

It will return "In Tran", which means that after the timeout dbo.TestTran was called in the middle of an outstanding transaction.

Note: not all timeouts result in outstanding transactions. For example, later you can rerun this example using a different stored procedure:

ALTER PROCEDURE dbo.IWillTimeOut AS BEGIN ; BEGIN TRANSACTION ; WAITFOR DELAY '00:00:05' ; COMMIT ; END ; GO

You can see for yourself that when this procedure times out, there will be no outstanding transaction.

Second breakpoint - still outstanding transaction

Let us debug to the second breakpoint. Technically the first connection went out of scope and has been reused by the second using block, but we can still see the same uncommitted data:

SELECT * FROM dbo.Log1 WITH(NOLOCK)

The connection has not been reset yet - we do not see sp_reset_connection in the Profiler yet.

Third breakpoint - connection reset

Only when we actually get to run a command against the reused connection, the connection is actually reset. We can observe sp_reset_connection call in the Profiler, and the uncommitted data inserted by the first invocation of dbo.TestTran is gone.

Note: to my best knowledge, there is no guarantee that the same connection will be reused. It consistently happens on my box, but it could work differently on yours. As usual with closed source systems, one can never know for sure.

Consequences of continuing to use the same connection after the timeout

As we have seen, if we catch a timeout and later modify the database using the same connection, all the modifications done after the timeout may be eventually rolled back and lost, without us getting any error messages.

Conclusion

We need to rollback the outstanding transaction ourselves. We must also be aware that not all timeouts result in outstanding transactions. I have written two extension methods that implement it, and I will post it soon.

You may want to read more about connection pools here

Slides and demos from AD-401: ADO.NET 2.0 Enhancements for SQL Server 2005

Adam Machanic — Mon, 20 Nov 2006 16:04:00 GMT

I just got a request for my slides and demos from PASS, so I figured I would post a link to them here in case anyone else wants to check them out.

The demos cover protected data sections, SqlConnectionStringBuilder, MARS, SqlBulkCopy, and SqlDependency. Just for the record: I make no guarantees about the quality, usefulness, or lack of bugs in the code -- use at your own risk. I probably don't really need to say that, but I've seen some strange things lately and I'm getting paranoid!

Anyway, you can download here. Enjoy, and as always feel free to leave me a comment if you have any questions, feedback, or anything else.

SqlDataReader performance tips

Adam Machanic — Thu, 13 Jul 2006 01:42:00 GMT

I just posted a few SqlDataReader performance tips in response to a newsgroup post; I think they're some pretty good tips, so I'll repeat them here.

These tips were gleaned from using both Lutz Roeder's Reflector and Compuware's DevPartner Profiler Community Edition.

Both of these packages are free, excellent additions to the toolbox of anyone who wants to write high-performance .NET code. The Reflector will show you what all of those library routines are really doing under the covers (for instance, did you know that the DataView's FindRows() method does a linear search?) And the Profiler will show you how long each line of code takes to execute -- accurately, down the microsecond. Very useful for figuring out where your bottlenecks are!

Anyway, on to the tips... Pretty basic stuff, but good to keep in mind:

A) Use an indexer with the ordinal position:

Instead of:

while (reader.read())
    object x = reader.GetValue("ColX");

while (reader.read())
    object x = reader["ColX"];

do:

int ColX = reader.GetOrdinal("ColX");
while (reader.read())
    object x = reader[ColX];

Under the covers, the string-based indexer and GetValue both call GetOrdinal -- so if you're looping through a large rowset GetOrdinal might be called every single time. Instead, only call it once.

B) Avoid the Get (e.g. GetInt32) methods at all costs, and use static casts instead of converts:

Bad:

int x = reader.GetInt32(ColX);

Better:

int x = Convert.ToInt32(reader[ColX]);

Best:

int x = (int)reader[ColX];

For maximal performance return the SQL Server equivalent of whatever datatype you'll be casting to (so that you can avoid the Convert methods.) And avoid NULLs so that you don't have to check for DBNull on the client.

C) Finally, just like in classic ADO, when using a SqlDataReader, "open late and close early" is the way to go. Open your connection at the last possible moment and close it as soon as you're done reading the data -- this will maximize connection pool availability.