Solved

committed transactions

Posted on 2004-09-20
16
752 Views
Last Modified: 2008-03-10
I am confused about 'committed transactions' in the transaction log.  I was reading that when you backup a database it only takes the committed transactions.  But I also read that when you perform a transaction it writes it to the transaction log but does not committ to the database until a checkpoint.  However if i were to make a change to a users account and check the database I see the change.  I dont understand what it means by it doesnt write to the database until it is committed.  Also when does it committ?
0
Comment
Question by:gupshup
  • 6
  • 3
  • 3
  • +1
16 Comments
 
LVL 7

Expert Comment

by:natloz
ID: 12105820
When you are using a stored procedure....you start a transaction with

begin transaction
   insert into tblUser(varName) values('Bob Hope')

   update tblUser set varUserName = 'Bobby Hope' where varName = 'Bob Hope'

if @@error <> 0 --error has occured
   begin
       rollback transaction <---Do not commit the transaction log to the database (abort and restore original data)
   end
else
   begin
       commit transaction  <---Commit above 2 queries to the Database from the transaction Log because no errors
   end

Basically....you will not see the results of a stored procedure if ROLLBACK was used...
0
 

Author Comment

by:gupshup
ID: 12106020
what im trying to understand more has to do with that the when you backup the transaction log it only takes the committed transactions.....is this bascially saying that the transactions that are in the process of bieng rolled back are not backed up....if this is the case isint that very rare?...please explain
0
 
LVL 7

Expert Comment

by:natloz
ID: 12106164
This is taken from SQL Online books...your scenario would not be an issue due the following statement....
---------------------------

SQL Server uses an online backup process to allow a database backup while the database is still in use. The following list includes operations that cannot run during a database or transaction log backup:

File management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options; INSERT, UPDATE, or DELETE statements are allowed during a backup operation.

Shrink database or shrink file. This includes autoshrink operations.

LOOK AT THIS STATEMENT
 |
 V
If a backup is started when one of these operations is in progress, the backup ends. If a backup is running and one of these operations is attempted, the operation fails.

0
 
LVL 7

Expert Comment

by:natloz
ID: 12106173
Therefore...the non-commited Transactions are useless to be backed up....basically you cannot backup when un-committed transactions are in progress.
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12106343
most likely yes
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12112905
>> basically you cannot backup when un-committed transactions are in progress. <<

I don't think so:

>>  INSERT, UPDATE, or DELETE statements *are* allowed during a backup operation. <<

A statement is committed when:
1) If in auto-commit mode:
    the statement completes
2) If in transaction mode:
    the transaction is committed, either explicitly or implicitly

A statement is rolled-back when:
1) If in auto-commit mode:
    certain error(s) occur on a statement
2) If in transaction mode:
    certain error(s) occur on a statement or a rollback is explicitly or implicitly done
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:gupshup
ID: 12113155
let me give an example because i am not getting the response to exaclty waht i am looking for......however thanks for the input everyone.....

lets say you have a gui with first name and last name...... when you type in john doe and hit submit does this write to the ldf file? now if this writes to the ldf file and you now run a backup will this transaction get backed up .....   i am wondering if when transactions are written to the ldf file are they committed right away.....or does every transaction go to the ldf file and then you have to commit them?

first name: john
last name: doe


0
 
LVL 7

Expert Comment

by:natloz
ID: 12113232
depends on how you submit the statement to SQL...

If you simply use...

insert into tblUser(varFirstName, varLastname)
values('John', 'Doe')

and run this as ExecuteNonQuery....it will be Automatically committed unless an error is thrown due to invalid data, etc...

If you use a stored procedure where you use...

begin transaction
   insert into tblUser(varFirstName, varLastName)
   values ('John', 'Doe')
if @@error <> 0
  begin
    rollback transaction
  end
else
  begin
    commit transaction
  end

Then you control when the transaction is committed...

similar to what Scott mentioned...

A statement is committed when:
1) If in auto-commit mode:
    the statement completes
2) If in transaction mode:
    the transaction is committed, either explicitly or implicitly

A statement is rolled-back when:
1) If in auto-commit mode:
    certain error(s) occur on a statement
2) If in transaction mode:
    certain error(s) occur on a statement or a rollback is explicitly or implicitly done
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12113325
Yes, *every* update gets written to the ldf (log), otherwise SQL couldn't guarantee integrity of the db.  And, in fact, the log record must always be written/committed to disk (at least as far as SQL is concerned) before the insert/update/delete is considered "complete" and control is returned to the app/user.

Note that this does *not* mean that the mdf (data) must be written.  In fact, SQL will try to apply the changes only to a data block(s) in memory as long as possible until committed.  This helps prevent a physical rollback if a statement is rolled back.  Of course if there are a significant number of changes, SQL may be forced to write uncommitted data to the disk to free up buffers.  Then a rollback will require re-reading and re-writing many data blocks.


>> what im trying to understand more has to do with that the when you backup the transaction log it only takes the committed transactions <<

Where did you read this?  That's not my understanding.  As you stated earlier, a *db* backup does only backup committed trans, but I thought a log backup captured active trans also.
0
 
LVL 7

Accepted Solution

by:
natloz earned 20 total points
ID: 12113465
Transaction Log Backups
The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure.

When restoring a transaction log backup, Microsoft® SQL Server™ rolls forward all changes recorded in the transaction log. When SQL Server reaches the end of the transaction log, it has re-created the exact state of the database at the time the backup operation started. If the database is recovered, SQL Server then rolls back all transactions that were incomplete when the backup operation started.

Transaction log backups generally use fewer resources than database backups. As a result, you can create them more frequently than database backups. Frequent backups decrease your risk of losing data.

Note  Sometimes a transaction log backup is larger than a database backup. For example, a database has a high transaction rate causing the transaction log to grow quickly. In this situation, create transaction log backups more frequently.

0
 

Author Comment

by:gupshup
ID: 12113730
if i make a change on the front end to some data and then i query this on the backend and i see my changes does that mean this is a 'committed transaction'?
0
 
LVL 7

Expert Comment

by:natloz
ID: 12113739
I would say so.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 20 total points
ID: 12113816
Most often.  However, you might see uncommitted transactions if:

1) you query using the same connection that made the change

2) your query specifies "WITH (NOLOCK)" or "READ UNCOMMITTED".
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now