Suggested Videos
Part 67 - Alter database table columns without dropping table
Part 68 - Optional parameters in sql server stored procedures
Part 69 - Merge in SQL Server
In this video we will discuss
1. What a transaction is
2. The problems that might arise when tarnsactions are run concurrently
3. The different transaction isolation levels provided by SQL Server to address concurrency side effects
What is a transaction
A transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit of work. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.
Example : The following transaction ensures that both the UPDATE statements succeed or both of them fail if there is a problem with one UPDATE statement.
Databases are powerful systems and are potentially used by many users or applications at the same time. Allowing concurrent transactions is essential for performance but may introduce concurrency issues when two or more transactions are working with the same data at the same time.
Some of the common concurrency problems
One way to solve all these concurrency problems is by allowing only one user to execute, only one transaction at any point in time. Imagine what could happen if you have a large database with several users who want to execute several transactions. All the transactions get queued and they may have to wait a long time before they could get a chance to execute their transactions. So you are getting poor performance and the whole purpose of having a powerful database system is defeated if you serialize access this way.
At this point you might be thinking, for best performance let us allow all transactions to execute concurrently. The problem with this approach is that it may cause all sorts of concurrency problems (i.e Dirty Reads, Lost Updates, Nonrepeatable Reads, Phantom Reads) if two or more transactions work with the same data at the same time.
SQL Server provides different transaction isolation levels, to balance concurrency problems and performance depending on our application needs.
If you choose the lowest isolation level (i.e Read Uncommitted), it increases the number of concurrent transactions that can be executed at the same time, but the down side is you have all sorts of concurrency issues. On the other hand if you choose the highest isolation level (i.e Serializable), you will have no concurrency side effects, but the downside is that, this will reduce the number of concurrent transactions that can be executed at the same time if those transactions work with same data.
In our upcoming videos we will discuss the concurrency problems in detail with examples
Part 67 - Alter database table columns without dropping table
Part 68 - Optional parameters in sql server stored procedures
Part 69 - Merge in SQL Server
In this video we will discuss
1. What a transaction is
2. The problems that might arise when tarnsactions are run concurrently
3. The different transaction isolation levels provided by SQL Server to address concurrency side effects
What is a transaction
A transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit of work. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.
Example : The following transaction ensures that both the UPDATE statements succeed or both of them fail if there is a problem with one UPDATE statement.
-- Transfer $100
from Mark to Mary Account
BEGIN TRY
BEGIN TRANSACTION
UPDATE Accounts SET Balance = Balance
- 100 WHERE Id = 1
UPDATE Accounts SET Balance = Balance
+ 100 WHERE Id = 2
COMMIT TRANSACTION
PRINT 'Transaction
Committed'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Transaction
Rolled back'
END CATCH
Databases are powerful systems and are potentially used by many users or applications at the same time. Allowing concurrent transactions is essential for performance but may introduce concurrency issues when two or more transactions are working with the same data at the same time.
Some of the common concurrency problems
- Dirty Reads
- Lost Updates
- Nonrepeatable Reads
- Phantom Reads
One way to solve all these concurrency problems is by allowing only one user to execute, only one transaction at any point in time. Imagine what could happen if you have a large database with several users who want to execute several transactions. All the transactions get queued and they may have to wait a long time before they could get a chance to execute their transactions. So you are getting poor performance and the whole purpose of having a powerful database system is defeated if you serialize access this way.
At this point you might be thinking, for best performance let us allow all transactions to execute concurrently. The problem with this approach is that it may cause all sorts of concurrency problems (i.e Dirty Reads, Lost Updates, Nonrepeatable Reads, Phantom Reads) if two or more transactions work with the same data at the same time.
SQL Server provides different transaction isolation levels, to balance concurrency problems and performance depending on our application needs.
- Read Uncommitted
- Read Committed
- Repeatable Read
- Snapshot
- Serializable
| Isolation Level | Dirty Reads | Lost Update | Nonrepeatable Reads | Phantom Reads |
| Read Uncommitted |
Yes
|
Yes
|
Yes
|
Yes
|
| Read Committed |
No
|
Yes
|
Yes
|
Yes
|
| Repeatable Read |
No
|
No
|
No
|
Yes
|
| Snapshot |
No
|
No
|
No
|
No
|
| Serializable |
No
|
No
|
No
|
No
|
If you choose the lowest isolation level (i.e Read Uncommitted), it increases the number of concurrent transactions that can be executed at the same time, but the down side is you have all sorts of concurrency issues. On the other hand if you choose the highest isolation level (i.e Serializable), you will have no concurrency side effects, but the downside is that, this will reduce the number of concurrent transactions that can be executed at the same time if those transactions work with same data.
In our upcoming videos we will discuss the concurrency problems in detail with examples
BEGIN TRY
ReplyDeleteBEGIN TRANSACTION
UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 3
COMMIT TRANSACTION
PRINT 'Transaction Committed'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Transaction Rolled back'
END CATCH
WHEN WE PASS ID VALUE AS =3
WHICH IS NOT IN TABLE THEN IT'S NOT ROLLED BACK Transaction
PLEASE SUGGEST
if this is the case then you have to check if id exits in table
DeleteTransaction will be committed as update statement will not throw any error rather it would update 0 rows.
ReplyDeleteif not exists( select * from TableName WHERE Id = 3)
ReplyDeletebegin
print 'Not Exist'
return
end
if not exists (select * from tablename where id=3)
ReplyDeletebegin
raiserror ('Incorrect ID',16,1)
End