Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Application starts before SQL is running.

Another option is to have a vbscript with a timer to wait 5 mins or so on
boot, THEN fire off the executable for the app.
TheSQLGuru
President
Indicium Resources, Inc.
"Graham." <me@.privacy.com> wrote in message
news:4686ab60$1_3@.mk-nntp-2.news.uk.tiscali.com...
> Very basic question.
> OS Windows XP pro
> On bootup, an application that starts automatically in the start-up
> folder, starts before SQL server is running
> This only happens on slower hardware, It must be a common problem, what's
> the usual solution?
> --
> Graham.
> %Profound_observation%
>
you can also check on whether the SQL server service is running
before your application starts trying to connect to the SQL server.
Change the Application to wait and try for 4-5 times(or based on the
customer experience) to connect to the MSSQL service.

Application starts before SQL is running.

Very basic question.
OS Windows XP pro
On bootup, an application that starts automatically in the start-up folder,
starts before SQL server is running
This only happens on slower hardware, It must be a common problem, what's
the usual solution?
Graham.
%Profound_observation%I just have a loop in my app where I have exception handling on logon and re
try...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Graham." <me@.privacy.com> wrote in message news:4686ab60$1_3@.mk-nntp-2.news.uk.tiscali.com.
.
> Very basic question.
> OS Windows XP pro
> On bootup, an application that starts automatically in the start-up folder
,
> starts before SQL server is running
> This only happens on slower hardware, It must be a common problem, what's
> the usual solution?
> --
> Graham.
> %Profound_observation%
>|||Another option is to have a vbscript with a timer to wait 5 mins or so on
boot, THEN fire off the executable for the app.
TheSQLGuru
President
Indicium Resources, Inc.
"Graham." <me@.privacy.com> wrote in message
news:4686ab60$1_3@.mk-nntp-2.news.uk.tiscali.com...
> Very basic question.
> OS Windows XP pro
> On bootup, an application that starts automatically in the start-up
> folder, starts before SQL server is running
> This only happens on slower hardware, It must be a common problem, what's
> the usual solution?
> --
> Graham.
> %Profound_observation%
>|||you can also check on whether the SQL server service is running
before your application starts trying to connect to the SQL server.
Change the Application to wait and try for 4-5 times(or based on the
customer experience) to connect to the MSSQL service.

Application starts before SQL is running.

Very basic question.
OS Windows XP pro
On bootup, an application that starts automatically in the start-up folder,
starts before SQL server is running
This only happens on slower hardware, It must be a common problem, what's
the usual solution?
--
Graham.
%Profound_observation%I just have a loop in my app where I have exception handling on logon and retry...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Graham." <me@.privacy.com> wrote in message news:4686ab60$1_3@.mk-nntp-2.news.uk.tiscali.com...
> Very basic question.
> OS Windows XP pro
> On bootup, an application that starts automatically in the start-up folder,
> starts before SQL server is running
> This only happens on slower hardware, It must be a common problem, what's
> the usual solution?
> --
> Graham.
> %Profound_observation%
>|||Another option is to have a vbscript with a timer to wait 5 mins or so on
boot, THEN fire off the executable for the app.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Graham." <me@.privacy.com> wrote in message
news:4686ab60$1_3@.mk-nntp-2.news.uk.tiscali.com...
> Very basic question.
> OS Windows XP pro
> On bootup, an application that starts automatically in the start-up
> folder, starts before SQL server is running
> This only happens on slower hardware, It must be a common problem, what's
> the usual solution?
> --
> Graham.
> %Profound_observation%
>|||you can also check on whether the SQL server service is running
before your application starts trying to connect to the SQL server.
Change the Application to wait and try for 4-5 times(or based on the
customer experience) to connect to the MSSQL service.