x

HOW WORKS COMMIT IN SQL 2000

Hi, I have here another question... I was asking me how works commit in SQL 2000? For example, you have an application, this applicantion is doing an insert, update or delete through a SP... not only a simple query.... and then it hangs up and force to close the application ... the last query commit itself or it does a rollback? or it corrupts the data?

How can I handel this?

Thanks a lot for your answeres... Regards,

Maripili
more ▼

asked Apr 28, 2012 at 02:50 AM in Default

Maripili gravatar image

Maripili
394 32 33 35

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

Transactions are either explicit or implicit. If there is not a begin tran or begin transaction with a corresponding commit or rollback then the transaction is implicit and SQL will commit the transaction for you under the covers.

An excellent article covering how transactions work can be found here
more ▼

answered Apr 28, 2012 at 03:04 AM

Tim gravatar image

Tim
36.4k 38 41 139

(comments are locked)
10|1200 characters needed characters left

As Tim said, there is always a transaction. All transactions are committed in conformance with [ACID][1] principles. SQL will log all changes as it goes to make it possible to roll back a transaction at any point up until it is finally committed.

As to your direct question about the last query in your stored procedure, it depends on when the application hangs up. If the application hangs up after the commit is finished, then it is committed and SQL Server will not make a further change. If the connection is broken before the transaction fully commits, it will roll back, using the log it made.

[1]: http://en.wikipedia.org/wiki/ACID
more ▼

answered Apr 30, 2012 at 08:09 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

This article on Simple-Talk http://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/ is somewhat related and well worth reading if you are interested in the transaction log and commit process.
May 02, 2012 at 05:55 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x3

asked: Apr 28, 2012 at 02:50 AM

Seen: 649 times

Last Updated: May 02, 2012 at 05:55 PM