Welcome to Appsloveworld technologies , In this session, we learn about transactions in the SQL server.
In this post, We will discuss the following topics
- How to write Rollback and Commit Transaction
- Create SQL Server procedure in a transaction
what’s a transaction?
A transaction is a group of SQL commands that change the data stored in the database. A transaction is treated as a single logical unit of work. and the most important thing is that A transaction ensures that either all of the SQL commands succeed or none of them.
If one of the SQL commands in the transaction fails, all of the SQL commands fail and any data that was modified in the database is then rolled back.
Let’s look at an example. I have a Table TblProducts which has information about products
At the moment, the quantity of Mobile Phones available is 100 at $10000. Now, let’s say I want to change the price to 12000.
TblProducts set price=10000 where id=2
Now, when We execute an update statement, it will update the price of the Mobile Phone in TblProducts Table.
Sql transaction real time example
Now, right now ,sql update statement is not part of any transaction. So as soon as you execute the update statement the change is made permanent to the database.
On the other hand, you can make an updated statement belong to a transaction by including begin transaction. So now let’s try to change the price to 15000 with a transaction.
So As you can see in the above query ,Now our update statement is part of the transaction. When we execute these two statements(i.e update and select) together, within the same window, within the same connection.
When I say, get the data back from the table, look at the below image. It shows 15000 which means our query executed successfully.
But then let’s try to make another connection to the SQL server. Click on the “New Query” button it will open a new connection for us.
Now, let’s try to get the data from the same table, look at what’s going to happen. It says executing the query and you will never get the data.
if you look at the TblProducts table, it has got only two rows to select those rows from that table. It should take less than a second. But it will return the data.
That’s because you have initiated transaction. But you did not either commit or Rollback the transaction because of which other users will not be able to see that uncommitted data by default.
And that’s because SQL Server default isolation level is read committed, meaning read only committed data.
But here we have some uncommitted data in TblProducts. That’s why we are not able to get the data from that table.
if I want to see that data, what I can do is basically set the transaction isolation level to read uncommitted data.
So let’s execute the above query, we are able yoy get the uncommitted data
Now, let’s Rollback transaction
when I execute select * from TblProducts we get the previous data i.e price is not updated to 15000 because we Rollback the transaction.
so the change is undone. now whenever you are issuing updating, insert, delete statements, and if these statements are part of a transaction, the change is not made immediately permanent to the database until you commit the transaction.
So if you want to make the change permanent, you need to commit the transaction. If you want to undo the change, then you can Rollback the transaction.
Let’s try to make that change permanent
I basically have to say commit transactions
At this point, the change is made permanent to the database.
Now transaction processing follows very simple steps. You Begin a transaction using “Begin Transaction” and then you process the database commands.
You might have one or more SQL servers of commands or inserts, updates, and deletes and then you check for any errors. If there are errors, you rollback the transaction, otherwise, you commit the transaction.
Transaction in sql server stored procedure example
Let’s look at an example. I have two tables here TblCustomerPhysicalAddress and TblShipingAddress. let’s say we are shopping online.
Most of the e-commerce sites have a Physical address and Shiping address, and most of us will have the same physical address and Shiping address along the same lines here.
If you look at this, we have a physical and a Shiping address of the customer.
if look at UserId with 1000, he has the same physical and Shiping address. Now, if you look at the city name, the city spelling, misspelled in Shiping Address.
Now I want to correct this, to correct this. I have a stored procedure here.
And if you look at here procedure, it’s a pretty simple.
All we are doing here is we are updating the TblCustomerPhysicalAddress table and TblShipingAddress table and we are setting the city to the correct spelling.
if you look at two update statements in procedure, these are wrapped between begin and Commit transaction blocks and this block is further wrapped between Begin try and End try. And then following the Try block with catch block where we are Rollback the transaction back.
so obviously when we excude the stored procedure, what’s going to happen?
That’s going to execute the first update statement, and then it’s going to exclude the second update statement. And if there are no errors, the transaction will be committed.
If there are errors, whenever you are executing first or second update statement, what’s going to happen?
The control will immediately be transferred to the catch block and within the catch block, we are rolling the transaction back.
Now, since these are two simple updates, statements, both of these statements should succeed and correct spelling to New Delhi.
So let’s exclude the stored procedure, it will update the city name correctly
if you look at the city column length in the TblShipingAddress table. So if you look at the city column length, it’s 10 characters.
Now let’s ceate one more stored procedure, But now I am trying to update the City = ‘New Delhi Heaven City 1100091’ and it is definitely more than 10 characters. So obviously when this update statement is being executed, we will get an error.
Now, when we execute this procedure, what’s going to happen if it comes to updates the TblCustomerPhysicalAddress city, which is fine.
And then when it comes to updating the TblShipingAddress, what’s going to happen?the city column length is 10, but the value is more than 10. So this is going to throw an exception.
So when an exception occurs, what’s going to happen?
The control immediately goes to the catch block and the catch block is rolling back the transaction.
So when we roll the transaction back, what’s going to happen?
Whatever change that was made by this first update statement is also going to be undone.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Is it possible to run a SQL Server view from a stored procedure?
- Can a Primary Key be a Non Clusterd Index on a Clustered Table?
- Change collation on clustered index column in SQL 2005
- Query not returning values for NULL
- Insert into statement returning an error
- Finding a preset value nearest to the column value
- Calling Stored Procedure in Access VBA only with the correct order of parameters, why?
- return longest sequence of digits from a string in SQL Server 2012
- Query all table data and index compression
- An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Invalid object name
- Grouping a tables column
- Check only specific field present in the string
- No error while executing a query with wrong column name
- Common substring of two string in SQL
- Declaring and setting variable within select statement sql
- How does one use TimeZoneInfo in a SQLCLR assembly in SQL Server 2012
- SQL Server--Setting column value during insert using trigger
- Cannot open existing package and msdb from integration system?
- Selecting top 5 grouped by, and ordered by average of multiple columns
- Automatically update column values in SQL Server
- Get latest row and sum of a column in it togather
- Export ER diagrams from SSMS
- SQL Query - Outstanding Payments
- SQL Server query: Union vs Distinct union all performance
- ASP, The SqlParameter is already contained by another SqlParameterCollection
- Socket io, mssql, node, expressjs in localhost cannot append data to client
- Trigger not working in BulkCopy in Sql Server
- Using where clause for R Variable in R script to use it in SQL statement
- Linq to SQL: Where clause comparing a Nullable<DateTime> with a SQL datetime null column
- Search count of words within a string using SQL
- SQL Server table partitioning based on a modulus function?
- What does the wait type PREEMPTIVE_XE_DISPATCHER mean?
- Invalid Object Error in spite of the Schema being the default Schema for the User
- SQL Server - JOIN isn't using the optimal order in Entity Framework's generated query
- How i can use cursor to delete record from table
- PHP-SQLSRV Stored Procedure with input variable of type `bit`
- Replacing Double Quote by Single Quote
- Performance of select over order by case join
- There is no email received
- Can Red Gate SQL Source Control identify a case change?
- Set up SQL Server to be accessible by all computers on a network via program manipulation
- Checking conditions, Doing calculation and filtering records, Doing Database Vs Web Server
- Return only the row where the date in one column is closest to the date in another column?
- How to connect to MS SQL Server with F# with SQLProvider?
- Remove quotation chars from file while bulk inserting data to table
- How do you order a list by numbers then by strings descending?
- PERSISTED Computed Column vs. Regular Column
- SQL INSERT INTO from multiple tables
- How to select Month Between in SQL
- XML parse using SQL
- Does ORDER BY clause slows down the query?
- Need help to write SQL query as a linq expression
- Sql Server 2005 error handling - inner exception
- Azure WCF service can't connect to SQL Azure
- SQL Server 'Invalid column' on a SELECT query to a temp table - CASE SENSITIVITY issue
- Save multiple rows in one table in one connection IN sqlserver
- SQL Server: parsing copyright symbol from xml
- Need SQL query to group together but sort overall
- Populate DropDownList from DataSource
- Empty IN sql statement
- Full table scan over indexed columns?
- How to execute folder with SQL Server 2008 scripts
- How to Deploy SQL Server Analysis Services Project to a different domain?
- sql Append for value with single quotes - String Concatenation
- SQL How to insert a new row in the middle of the table
- MySQL to MSSQL synchronization
- PHP PDO multiple delete queries not working
- How do I use Bulk insert without inserting into the autonumber column?
- Transform table and its child tables to XML and back?
- How can i modify this query to leave out blanks or empty value?
- How to convert products to product-with-pack-sizes using the largest pack size
- Views and message error Msg 245, Level 16, State 1, Line 1 Conversion failed
- SQL Server 2005 + JavaEE application on one computer in production
- SQL Primary Key Exception
- Working on String concatenation with parsename function in sql server
- How to find first positive value and third consecutive positive values in SQL?
- Is it possible to connect to mssql with windows auth mode from a nodejs app running on linux?
- Database login failed for user
- ASIHttpRequest: Can someone see the URL being sent?
- SQL Server convert hexadecimal varchar value to INT
- C#, SQL Server - Connecting to local server without specifying data source
- Recommendations for database structure with huge dataset
- Using SQL Server LEFT JOIN without multiple rows - how do I do that anyway?
- SQL Server Update with check of duplicates
- Conversion from String to SqlDbType.Ntext
- Sql Server - how to get the least qualifying record?
- Search all tables, all columns for a specific value SQL Server
- insert from sql stored procedure via dapper returns -1 (not "working")
- Obtain column names from query using wildcard
- Determining the current security checks being made (SQL Server)
- Auditing columns in Entity Framework
- Reading SQL Analysis Services Metadata
- How to use distinct and count for multiple columns in SQL query
- SQL Query variable vs hardcoded value
- Error converting data type nvarchar to datetime SQL Server
- How to update sql server timestamp column without changing record data
- UDF to insert a text string in to each row of a table
- SQL DELETE query with NOT IN clause not working
- OPENROWSET converting phone number in excel sheet to decimal format instead of varchar
- Sysadmin can't see tables from .net application
- An Error when trying to connect to SQL Server
- using subquery in between operator
- Is there a SQL Server collation option that will allow matching different apostrophes?
- SQL Server : duplicates joining 4 tables
- Attempt number over transitory statuses in T-SQL
- Find non-duplicate value in one column based on known value of another column
- How can you retrieve relationsships from a MSSQL database
- PHP and SQL Server, how to make it secure?
- How to get rows repeating as per selected data set in sql
- How do I connect to a local Windows SQL Server Instance from WSL?
- Search parent-child relationship until parent meets a condition
- SQL Server : add characters to a column
- MS SQL Server - Bulk Insert Across a Network
- number of days gap in between 2 dates to include the resultant data as another column
- Can Database Mirroring Be Setup On MS SQLServer Between Two Clusters
- JDBC MS SQL Server Kerberos Auth
- SQL return 1 row in results for each column in table
- How to efficiently store and manage images in SQL Server 2005
- How to use sqlcmd.exe in windows-auth no-trust cross-domain scenario?
- Customizing placement of report parameters
- SQL Server slow in simple insert statement
- MSDTC Distributed Transaction Coordinator Enabling
- Execute SQL Server stored procedure in VBA with multiple outputs
- How to allow null values on foreign keys in MS SQL Server 2012?
- Does SQL Server support measuring distance via routes?
- Global exception handler in CLR Stored Procedure
- SELECT FOR UPDATE with SQL Server
- How can I track the source of slowness for my scenario?
- Fun with BULK INSERT SQL SERVER - type mismatch or invalid character for the specified codepage
- Finding Customer Order Frequency
- Getting rid of an expensive self join
- Multiple join with the same table count
- Loop through ado.net sql select to add more rows to datatable
- Updating Value Where Date
- Call to SQL Server stored procedure failing in certain cases
- INSERT with Multiple select SQL
- Which is better for performance DateA <= ISNULL(DateB, '29991231) or (DateA < DateB OR DateA IS NULL)
- How do I negate a value in a SET clause for SQL Server Compact?
- Split call data to 15 minute intervals
- SQL Substring Issue
- Possibility to debug from .net code into SQL Server stored procedure code?
- Inserting GUIDs with SqlBulkCopy
- Selecting most recent answers efficiently
- Detect whether a row exists with a SQL IF statement
- How to dump data from MSSQL to XML with SQL Agent?
- Using group by in Common Table Expression in SQL Server 2008
- deploy SSIS Package to different environments (Dev, Test, Prod)
- Copy complete SQL Server 2008 db
- How to test if a SQL Server 2008 database can serve 200 concurent users
- Difference between two dates and if one date is null use current day in SQL