Solved

committed transactions

Posted on 2004-09-20
16
758 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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:Scott Pletcher
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
 

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:Scott Pletcher
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:Scott Pletcher
Scott Pletcher 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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