Is Truncate A Transaction

This is very complex question, which is very difficult to describe. Though all RDBMS are similar but found major difference for transaction in PostgreSQL, MySQL & SQL Server.

PostgreSQL we can have truncate in transaction. As PostgreSQL can run DDL and DML in transaction. If we run truncate in transaction, it can rollback

SQL server can run DML, DDL in transaction but truncate in SQL Server can not be rollback so truncate is not a transaction.

MySQL also don't run truncate in transaction. We can not rollback once we issue truncate command even in transaction.   

Transaction in DBMS

Transaction are set of commands executed in a small program. Transaction in DBMS must follow ACID( Atomicity, Consistency, Isloation & Durability).

Atomicity ensures that transaction is either completed or roll backed. It wont be partially completed. Consistency ensures that the database must be in consistent state after every transaction. Durability ensures that if data are committed, it should be present in DB. Isolation level makes sure, we read committed data.

States of transaction

There are many states within a transaction. It can be either committed, failed or rollback. Lets see the states within a transaction.

Active State

When transaction is being initiated or started.

Partially Committed State

When transaction executed its final statement.

Failed State

A failed transaction can not proceed further. If any statement fails, it moves to failed state. 

Aborted State

When transactions fails and recovery manager rollbacks all contents to original state, that state is called as aborted 

Committed State

When transactions successfully runs all statements, that state is committed state.

Introduction

This is my first blog and I am starting it with the intention to share my knowledge with the whole world. Comments are welcomed  to improve each and every page. 

About Me:

I am open source consultant. Presently with CISCO delivering end to end projects. Have sound knowledge on PostgreSQL, MySQL and Data ware housing tool like Netezza and Amazon Redshift. Though I am Oracle Certified Professional too.

About the Blog:

I am creating this blog to write internal about open source DB and also I will share about Linux and Networking which is part of DB. I will also share my experience to handle various scenarios and also will keep an update on the implementation in simple terms.

Conclusion:

Lets start the journey with good note and have a very good knowledge sharing among all the experts. Thank you for reading my blog. Do comment and share the information.