locked
transactions vs. transaction logging RRS feed

  • Question

  • I think I've been confused thinking that transactions and transaction logging are related. Maybe they aren't. I'd be interested in comments from others about this.

    Transactions are "do all of these changes or none of them" with a begin transaction, then data changes, then a commit or rollback of the changes since the "begin transaction" statement.

    I thought transactions wouldn't work if the database was in "Simple" recovery mode. That the database needed to be in "Full" recovery mode and that its transactions needed to be backed up periodically (every 10 minutes typically).

    Now I think it turns out these two types of "transactions" are unrelated. Are they? Or have I missed something here? Especially, is there anything about being in full recovery mode that makes coding "do all of these changes or none of them" work differently?

    Tuesday, April 4, 2017 9:21 PM

Answers

  • You may be getting COMMIT TRANSACTION operation confused with transaction log. They are related, but different.

    The database recovery model each database is set to, determines how much transactions get recorded in transaction log. FULL recovery model records all transactions (insert/update/delete operations), whereas SIMPLE recovery model, transactions activity is 'minimally' logged. Transaction operations still happen, but log space is reused and transactions are overwritten in the log when SIMPLE recovery model is defined.

    How the transaction is committed, depends on the server transaction isolation level setting. 'Read committed' is the default isolation level. You can read more about isolation levels here:
    Isolation Levels in the Database Engine

    And COMMIT TRANSACTION here:
    COMMIT TRANSACTION (Transact-SQL)

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Tuesday, April 4, 2017 9:31 PM
  • Even if the database is set to SIMPLE recovery mode SQL Server keeps\manage transactions in the same way in the log file. The difference is in the SIMPLE recovery mode there  is CHECKPOINT  runs if the the log is 70% full.

    Transactions (all DML modifications (INSERT /UPDATE/DELETE)  are logged to the Transaction Log regardless whether it SIMPLE or FULL mode. 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 5, 2017 4:50 AM
  • Hi GordonPrince4575,

     

    Once I spent some time studying this problem, based on my point of view, transactions and transactional log has relationship.

     

    A transaction is a sequence of operations performed as a single logical unit of work, it is a concept of DBMS. For SQL Server, every transaction will be recorded first into transactional log, after a checkpoint, these change will be recorded in database. Please refer to the article given from Shashank.

     

    Transaction still works if the database was in "Simple" recovery mode, the recovery mode only affects the way of storing transactional log and the way using and reclaiming space of VLF.

     

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 5, 2017 5:59 AM

All replies

  • You may be getting COMMIT TRANSACTION operation confused with transaction log. They are related, but different.

    The database recovery model each database is set to, determines how much transactions get recorded in transaction log. FULL recovery model records all transactions (insert/update/delete operations), whereas SIMPLE recovery model, transactions activity is 'minimally' logged. Transaction operations still happen, but log space is reused and transactions are overwritten in the log when SIMPLE recovery model is defined.

    How the transaction is committed, depends on the server transaction isolation level setting. 'Read committed' is the default isolation level. You can read more about isolation levels here:
    Isolation Levels in the Database Engine

    And COMMIT TRANSACTION here:
    COMMIT TRANSACTION (Transact-SQL)

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Tuesday, April 4, 2017 9:31 PM
  • Even if the database is set to SIMPLE recovery mode SQL Server keeps\manage transactions in the same way in the log file. The difference is in the SIMPLE recovery mode there  is CHECKPOINT  runs if the the log is 70% full.

    Transactions (all DML modifications (INSERT /UPDATE/DELETE)  are logged to the Transaction Log regardless whether it SIMPLE or FULL mode. 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 5, 2017 4:50 AM
  • I think I've been confused thinking that transactions and transaction logging are related. Maybe they aren't. I'd be interested in comments from others about this.

    They are transaction when executed produce logs and they are logged into transaction log by method called as transaction logging. This is most simple terms I have used actually the whole concept is bit deep


    Transactions are "do all of these changes or none of them" with a begin transaction, then data changes, then a commit or rollback of the changes since the "begin transaction" statement.

    A transaction can be atomic like you said one within begin tran and commit/rollback or it can be just implicit transaction like simple select or delete statement. In former case you have to manually enter commit to commit the changes and later system will automatically do it subject to condition the transaction succeeds.


    I thought transactions wouldn't work if the database was in "Simple" recovery mode. That the database needed to be in "Full" recovery mode and that its transactions needed to be backed up periodically (every 10 minutes typically).

    The recovery model of database only controls how much is "logged" it has nothing to do with how transactions work. Transactions would work in same way in all recovery models. The amount of information logged in transaction log is affected by recovery model


    Now I think it turns out these two types of "transactions" are unrelated. Are they? Or have I missed something here? Especially, is there anything about being in full recovery mode that makes coding "do all of these changes or none of them" work differently?

    I suggest you first read about recovery model and logging into SQL Server database. This will help you lot in understanding the concept


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Teige Gao Wednesday, April 5, 2017 5:36 AM
    Wednesday, April 5, 2017 5:11 AM
    Answerer
  • Hi GordonPrince4575,

     

    Once I spent some time studying this problem, based on my point of view, transactions and transactional log has relationship.

     

    A transaction is a sequence of operations performed as a single logical unit of work, it is a concept of DBMS. For SQL Server, every transaction will be recorded first into transactional log, after a checkpoint, these change will be recorded in database. Please refer to the article given from Shashank.

     

    Transaction still works if the database was in "Simple" recovery mode, the recovery mode only affects the way of storing transactional log and the way using and reclaiming space of VLF.

     

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 5, 2017 5:59 AM
  • Just to add one thing. Regarding recovery model. With a few exceptions (*), the same tings are logged in FULL and SIMPLE recovery model. What differs is that in simple, SQL Server can by itself re-use the space in the ldf file. In full you have to do a BACKUP LOG for that.

    (*) In simple these operations are minimally logged: SELECT INTO, bulk loading of data, CREATE/ALTER/DROP INDEX and possibly INSERT with subselect.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, April 5, 2017 7:09 PM