What is the different between transaction in SQL Server and ADO.NET

Question

What is the different between transaction in SQL Server and ADO.NET

Vladimir Sim

Ten Centuries

Points: 1310
More actions
August 28, 2006 at 1:42 am

#90359

Hi,
1. What is the main different in transaction when apply in SQL Server stored
procedure and ADO.NET using code ?
2. Are they the same in what way and
different in what way.
3. When should we use them ?
4. What is their cons and pros, which is the best ?
Please advise as I am

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Paul Rubel Ten Centuries Points: 1047 More actions · Answer 1

My first reply was lost, I'll try again.

ADO.NET is a DB API. I view this as a layer on top of TSQL. Since SQL Server always logs changes to a transaction log, the transaction rollback and committ functions are always available. The transactions can be implicit or explicit. Explicit means the programmer put Begin Tran and Commit tran in the program. Implicit means the DBMS will either commit or rollback the logical unit of work based on the success or failure of the calls. Transactions are always maintained via the connection.

From a transaction view point ADO.NET and TSQL are equivalent.

Application code that wraps the DB API can be multithreaded, so the developer needs to know if thread safety is maintained, and the inheritance behavior of the threads and sub processes. Also the DB API class libraries are alway trying to keep up with the latest DBMS features so the developer needs to be aware of the DB API version and the features of the DBMS that are supported in that version.

Usualy Stored Procedures are the recommended method of codeing the data access layer. This give you plan reuse and limited plan recompiling. Security is more rigorous with Stored Procedures. You can grant a role the priviledge of executing the stored procedure without giveing them access to the underlying table.

In the Microsoft realm, transaction behavior is usualy not an evaluation criteria.

Vladimir Sim Ten Centuries Points: 1310 More actions · Answer 2

Hi Paul,

1. Thank for your advise.

2. BTW, during transaction, all the table involve will in some way be hold, is there any way to only lock those affected row without locking the whole table, is it using SET TRANSACTION ISOLATION LEVEL command, please advise.

Thank you

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Vladimir

If SQL Server is locking the whole table, it is because it judges that it is more efficient to do so than to issue multiple row or page locks, for example. It is possible to use hints in your query, but you should only do so if you understand the implications. Likewise, you can set a different transaction isolation level to increase concurrency at the possible expense of data integrity. Once again, make sure you know what you are doing and why you are doing it.

There are other actions you can take that may reduce the duration that locks are held, for example breaking your batch into smaller transactions, or making sure that indexes and queries are well tuned so that they run as fast as possible. Better to take this approach, in my opinion, than to try to outguess the query optimiser.

John

Vladimir Sim Ten Centuries Points: 1310 More actions · Answer 4

Hi John,

1. Thank for your advise.

2. Actually, my web application need this feature. It will let user update some data which will affect other data. This might go on for a few level like 4-5 levels. Anytime along the updating by user, they can choose to revert back their changes. Hence, before their updating, we (developer) thought of using transaction to save their changes, so that we can revert back their change. However, with transaction, it will affect tables that are updated and hold other up. Do you have any other suggestion for my issue.

Thank you

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Vladimir

Without knowing more about your application, it's impossible for me to offer any specific advice. For example, I can't think of any reason why you would want users to be able to change their mind in the middle of a transaction. But I think your choice is between setting a lower transaction isolation level, and risking the integrity of your data, and using lock hints, which could bring your server to a grinding halt. This is because each lock takes up 96 bytes of memory, and if you start issuing row locks for a large update then you may use memory that would be better used for other things. In these situations, there is the possibility that SQL Server will escalate the lock to a table lock, for example, and this would affect your concurrency.

John

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

Vladimir,

The best way to do this is to collect all the data from the user first even if it's for 4 or 5 levels, as you claim. Then, do a single insert or update proc that will blast the updated info into the db. You may not even need a transaction if you have preverified that all of the data will work correctly. Never ever wait on a user (human, proc, or app) with an open transaction. It's "death by SQL".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladimir Sim Ten Centuries Points: 1310 More actions · Answer 7

Hi Jeff,

Thank for the advise. I know that having open transaction is bad. However,

for my application. There is many route for user to choose. Each level have different

router for them to choose which will trigger other action and affect what route they

can choose. Hence, I can't let user choose first as it depend on the individual action to trigger subsequence route.