{"id":23059,"date":"2018-08-12T08:02:52","date_gmt":"2018-08-12T08:02:52","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=23059"},"modified":"2021-03-11T14:43:59","modified_gmt":"2021-03-11T09:13:59","slug":"sql-server-transaction","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/","title":{"rendered":"SQL Server Transaction – Properties, Control, Example"},"content":{"rendered":"

We are aware of various aspects and uses of SQL from our previous tutorials. In this tutorial, we will discuss transactions. We will dive deep into what are transactions in SQL and the usage.<\/p>\n

We will also discuss various examples of transactions in SQL. When we talk about transactions, we can think of them as a series of work performed on a database.<\/p>\n

What are SQL Transactions?<\/h3>\n

A transaction is a work that is performed against a database.<\/p>\n

Transactions are units or arrangements of work achieved in a reasonable request, regardless of whether in a manual manner by a client or consequently by a database program.<\/p>\n

For instance, we can face a scenario where we are updating a record, and parallelly some other user is deleting the same record. So, to avoid such cases, we use transactions.<\/p>\n

It is essential to control these transactions to guarantee the information is correct and to check the database mistakes.<\/p>\n

Essentially, we will club numerous SQL inquiries into a gathering, and you will execute every one of them all together on an exchange.<\/p>\n

Transactions are used only with the DML queries i.e. Delete, Update, and Insert statements.<\/p>\n

\"What<\/a><\/p>\n

Properties of Transactions in SQL<\/h3>\n

All transactions follow four major properties referred by the acronym ACID. The properties are as follows:<\/p>\n\n\n\n\n\n\n\n
Sr.No<\/b><\/td>\nProperty<\/b><\/td>\n<\/tr>\n
1<\/td>\nAtomicity<\/td>\n<\/tr>\n
2<\/td>\nConsistency<\/td>\n<\/tr>\n
3<\/td>\nIsolation<\/td>\n<\/tr>\n
4<\/td>\nDurability<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Let us understand each of these in details:<\/p>\n

1. Atomicity:<\/strong> This property ensures that the operations we are performing on the database during a session are completed successfully.<\/p>\n

If a transaction fails before completion, the whole process is sent back to the previous state. This means the transaction is halted at the point of failure and all the previous operations are reset to the original state.<\/p>\n

2. Consistency:<\/strong> With this property, we ensure that the database is able to change the states upon successfully committed transactions.<\/p>\n

3. Isolation:<\/strong> With the isolation property, we make sure that transactions operate independently and are always transparent to each other.<\/p>\n

4. Durability:<\/strong> With the durability property, we make sure that the result or effect of a committed transaction is put down properly in case of any type of system failure.<\/p>\n

\"Properties<\/a><\/p>\n

SQL Control Statements<\/h3>\n

We have various defined control statements for transactions and are widely used as commands.<\/p>\n

The commands used to control transactions are as follows:<\/p>\n

1. START TRANSACTION:<\/strong> starts a SQL transaction for us.<\/p>\n

2. COMMIT:<\/strong> helps to save the changes made in the database.<\/p>\n

3. ROLLBACK:<\/strong> helps to restore the previous state of the database.<\/p>\n

4. SAVEPOINT:<\/strong> helps to create points in the transaction groups to which the system might try to rollback.<\/p>\n

5. RELEASE SAVEPOINT:<\/strong> helps us to remove an already existing SAVEPOINT.<\/p>\n

6. SET TRANSACTION:<\/strong> helps to name a transaction, so that it can be pointed out easily.<\/p>\n

Demo Database<\/h3>\n

Let us first view our demo database DataFlair_emp2.
\nQuery:<\/strong><\/p>\n

SELECT * FROM DataFlair_emp2;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Here we can see our demo database and the contents in it.<\/p>\n
Example 1: Let us start a transaction on our demo database.<\/strong>
\nQuery:<\/strong><\/p>\n
START TRANSACTION;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Example 2: Let us create a save point in our transaction.<\/strong>
\nQuery:<\/strong><\/p>\n
SAVEPOINT initialState;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Example 3: Let us delete the records with employee age more than 25 and save the transaction as DataFlair_under25.<\/strong><\/p>\n
DELETE from dataflair_emp2 WHERE age>25;\r\nSAVEPOINT DataFlair_under25;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Now we have saved this state of our transaction as DataFlair_under25.<\/p>\n
Let us view the database of DataFlair_under25 transaction.
\nQuery:<\/strong><\/p>\n
SELECT * FROM DataFlair_emp2;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
We can see the updated database with details of employees under 25 in DataFlair.<\/p>\n
Example 4: Let us now restore our original DataFlair database.<\/strong>
\nQuery:<\/strong><\/p>\n
ROLLBACK  to initialState;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Let us now view our database again.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT * FROM DataFlair_emp2;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Example 5: Let us now remove the initialState SAVEPOINT we created.<\/strong>
\nQuery:<\/strong><\/p>\n
RELEASE SAVEPOINT initialState;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
After the execution of the query, our already existing SAVEPOINT is released. Once we release a SAVEPOINT we can no longer ROLLBACK to the SAVEPOINT.<\/p>\n
Summary<\/h3>\n
In this tutorial, we have understood SQL Transactions with examples and implementations.<\/p>\n
Firstly start a transaction and then we can even name a transaction to access afterward. Then save the transaction by using commit.<\/p>\n
We also save various states of the transaction by using savepoint, and these are helpful when we need to rollback our transactions.<\/p>\n
We use rollbacks when a transaction makes an exit in between or shuts down. Rollback helps us to maintain our data properly. We have covered all the details of SQL transactions in our tutorial.<\/p>\n","protected":false},"excerpt":{"rendered":"
We are aware of various aspects and uses of SQL from our previous tutorials. In this tutorial, we will discuss transactions. We will dive deep into what are transactions in SQL and the usage....<\/p>\n","protected":false},"author":6,"featured_media":86903,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[6380,8990,13562,13576,14887,14975],"class_list":["post-23059","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-how-to-use-transaction-in-sql-server","tag-mysql-transaction","tag-sql-server-transaction-example","tag-sql-transaction-rollback","tag-transaction-in-sql","tag-tsql"],"yoast_head":"\nSQL Server Transaction - Properties, Control, Example - DataFlair<\/title>\n<meta name=\"description\" content=\"SQL server transaction, transaction in SQL, properities of SQL transaction, SQL Transaction Example, SQL transaction Control, tsql\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Transaction - Properties, Control, Example - DataFlair\" \/>\n<meta property=\"og:description\" content=\"SQL server transaction, transaction in SQL, properities of SQL transaction, SQL Transaction Example, SQL transaction Control, tsql\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-12T08:02:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-11T09:13:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Transactions-in-SQL.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Transaction - Properties, Control, Example - DataFlair","description":"SQL server transaction, transaction in SQL, properities of SQL transaction, SQL Transaction Example, SQL transaction Control, tsql","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Transaction - Properties, Control, Example - DataFlair","og_description":"SQL server transaction, transaction in SQL, properities of SQL transaction, SQL Transaction Example, SQL transaction Control, tsql","og_url":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-08-12T08:02:52+00:00","article_modified_time":"2021-03-11T09:13:59+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Transactions-in-SQL.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SQL Server Transaction – Properties, Control, Example","datePublished":"2018-08-12T08:02:52+00:00","dateModified":"2021-03-11T09:13:59+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/"},"wordCount":701,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Transactions-in-SQL.jpg","keywords":["How to use Transaction in SQL Server","mysql transaction","SQL Server Transaction Example","SQL Transaction Rollback","Transaction in SQL","tsql"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-server-transaction\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/","url":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/","name":"SQL Server Transaction - Properties, Control, Example - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Transactions-in-SQL.jpg","datePublished":"2018-08-12T08:02:52+00:00","dateModified":"2021-03-11T09:13:59+00:00","description":"SQL server transaction, transaction in SQL, properities of SQL transaction, SQL Transaction Example, SQL transaction Control, tsql","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-server-transaction\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Transactions-in-SQL.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Transactions-in-SQL.jpg","width":1200,"height":628,"caption":"Transactions in SQL"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-server-transaction\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Searching for Groups of Scala Regex","item":"https:\/\/data-flair.training\/blogs\/tag\/searching-for-groups-of-scala-regex\/"},{"@type":"ListItem","position":3,"name":"SQL Server Transaction – Properties, Control, Example"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/23059","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=23059"}],"version-history":[{"count":5,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/23059\/revisions"}],"predecessor-version":[{"id":86914,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/23059\/revisions\/86914"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/86903"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=23059"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=23059"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=23059"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}