SQL Interview Questions and Answers - Part 17
Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by below link:
Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.
Introduction
This is the 17th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.
I will highly recommend to please read the previous parts over here before continuing the current part:
SQL Interview Questions and Answers - Part 17
Q152. What is a Transaction in SQL?
A transaction in SQL is a single unit of work. It can contain a set of SQL statements within a single transaction.
If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.
If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
Every query that runs in a SQL Server is in a transaction.
If you run a query without mentioning the BEGIN TRAN keyword then it would be considered an implicit transaction.
If you run a query that starts with BEGIN TRAN and ends with COMMIT or ROLLBACK, then it would be considered an explicit transaction.
SQL Transaction Modes: SQL Server operates in the following transaction modes:
Autocommit Transactions: Each individual SQL statement is a transaction. When you execute any SQL command they are committed automatically.
Explicit Transactions: In this mode, each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
Implicit Transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
Batch-scoped Transactions: It is applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction.
A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.
Q153. What is TCL in SQL? What are the different TCL commands?
In SQL, TCL stands for Transaction Control Language. TCL commands are used to manage and control transactions in a database.
TCL allows the statements to be grouped together into logical transactions. They are used to manage the changes made by DML statements.
SQL TCL Commands:
TCL commands are used to track the effects of other commands on the database. They are also used to control transactional processing in a database. The changes made using TCL commands are permanent and cannot be reversed.
BEGIN TRAN: It is used to start a transaction in SQL. It marks the starting point of an explicit, local transaction. Explicit transactions start with the BEGIN TRANSACTION statement and end with the COMMIT or ROLLBACK statement.
You can also use the transaction name. The transaction_name must conform to the rules for identifiers, but identifiers longer than 32 characters are not allowed. A transaction_name is always case sensitive, even when the instance of SQL Server is not case sensitive.
BEGIN TRAN tran_name
You can also use a user-defined variable containing a valid transaction name. The variable must be declared with a char, varchar, nchar, or nvarchar data type. If more than 32 characters are passed to the variable, only the first 32 characters will be used; the remaining characters will be truncated.
BEGIN TRAN @tran_variable_name
A BEGIN TRANSACTION always increments @@TRANCOUNT by 1.
Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement.
BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency.
COMMIT TRAN: The Commit command is used to permanently save any transaction into the database. It ends the current transaction and makes permanent changes during the transaction.
SAVEPOINT TRAN: The SAVEPOINT command is used to temporarily save a transaction so that you can roll back to that point whenever necessary.
We can divide the database operations into parts with different savepoint names. Using the SAVEPOINT command in SQL, we can save the different parts of the same transaction using different names. SAVEPOINT command is used for saving all the current points in the processing of a transaction.
ROLLBACK TRAN: The ROLLBACK command restores the database to the original since the last COMMIT. It is used to restore the database to the last committed state. Users can roll back the transaction to different save points as well.
WITH MARK: It is used to specify that the transaction is marked in the log. Here, the description is a string that describes the mark.
The WITH MARK option causes the transaction name to be placed in the transaction log.
When restoring a database to an earlier state, the marked transaction can be used in place of a date and time.
The mark is placed in the transaction log only if the database is updated by the marked transaction. Transactions that do not modify data are not marked.
WITH MARK 'description'
A description longer than 128 characters is truncated to 128 characters before being stored in the msdb.dbo.logmarkhistory table.
If WITH MARK is used, a transaction name must be specified. WITH MARK allows for restoring a transaction log to a named mark.
Additionally, transaction log marks are necessary if you need to recover a set of related databases to a logically consistent state. Marks can be placed in the transaction logs of the related databases by a distributed transaction. Recovering the set of related databases to these marks results in a set of databases that are transactionally consistent.
Q154. What are ACID properties in SQL?
The acronym ACID stands for Atomic, Consistent, Isolated, and Durable. A SQL Transaction must adhere to a set of requirements, known as the ACID properties. All the ACID properties are independent.
The acronym ACID was created by Andreas Reuter and Theo Härder in the year 1983, however, Jim Gray had already defined these properties in the late 1970s.
Atomic or Atomicity:
The Atomicity Property of a SQL Transaction ensures that either all the DML Statements inside a transaction are completed successfully or all of them are rolled back.
A transaction must execute exactly once and must be atomic in nature.
"All changes made by the transaction are completed as a single unit, or none of the changes are made."
If a partial transaction were committed, the atomic property is violated, and the database is left in an inconsistent state. The ability to commit or roll back transactions is required for Atomicity.
Consistent or Consistency:
The Consistency Property of a SQL Transaction ensures that the database data is in a consistent state before the transaction started and also left the data in a consistent state after the transaction is completed. If the transaction violates the rules then it should be rolled back.
A SQL Transaction must preserve database Consistency, which means that the database must begin the transaction in a state of consistency and return to a state of consistency when the transaction is complete.
BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency.
Isolated or Isolation:
The Isolation Property of a SQL Transaction ensures that the intermediate state of a transaction is invisible to other transactions.
The data modifications made by one transaction must be isolated from the data modifications made by all other transactions. Most databases use locking to maintain transaction isolation.
A transaction must be a unit of isolation, which means that concurrent transactions should behave as if each were the only transaction running in the system.
Because a high degree of isolation can limit the number of concurrent transactions, some applications reduce the isolation level in exchange for better throughput.
Durable or Durability:
The Durability Property of a SQL Transaction ensures that once the transaction is successfully completed, then the changes it made to the database will be permanent. Even if there is a system failure or power failure or any abnormal changes, it should safeguard the committed data.
A transaction must be recoverable and therefore must have durability. If a transaction commits, the system guarantees that its updates can persist even if the computer crashes immediately after the commit.
Some specialized logging allows the system's restart procedure to complete unfinished operations required by the transaction, making the transaction durable.
Q155. What is the use of SAVEPOINT in Transaction?
The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled.
If a transaction is rolled back to a savepoint, it must proceed to completion with more Transact-SQL statements if needed or it must be canceled altogether by rolling the transaction back to its beginning. To cancel the entire transaction, we need to use ROLLBACK TRANSACTION transaction_name.
Duplicate savepoint names are allowed in a transaction, but a ROLLBACK TRANSACTION statement that specifies the savepoint name will only roll the transaction back to the most recent SAVE TRANSACTION using that name.
SAVE TRANSACTION is not supported in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.
A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks that are acquired beyond the savepoint, with the exception of escalations and conversions. These locks are not released, and they are not converted back to their previous lock mode.
Q156. What is the difference between COMMIT and SAVE transactions in SQL?
Save Transaction is used to temporarily save a transaction into the database so that you can roll back to that point whenever necessary. It is temporary storage from where we can roll back if something seems not fine.
For the Save command also a record id is generated to the database. But the changes are limited only to the transaction but you can anytime rollback.
On the other hand, Commit is used to permanently save any transaction into the database, and once stored, everyone knows, that can not be rolled back. Commit is the final stage for a transaction, where it saves data into the database which were there in temporary transactions.
Q157. What is the use of @@TRANCOUNT in SQL?
@@TRANCOUNT function returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. The data type is an integer for this count.
- Every BEGIN TRANSACTION statement increments @@TRANCOUNT by 1.
- Every ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT.
- Every COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.
Q158. What is the difference between TRANSACTION and DISTRIBUTED TRANSACTION in SQL?
The BEGIN DISTRIBUTED TRANSACTION specifies the start of a Transact-SQL distributed transaction. While BEGIN TRANSACTION is used to start the normal transaction.
For Distributed Transactions, when using SQL Server the distributed transaction is managed by the Microsoft Distributed Transaction Coordinator (MS DTC). In the case of Azure SQL Managed Instance, the transaction is managed by the service itself and not MS DTC.
The instance of the SQL Server Database Engine executing the BEGIN DISTRIBUTED TRANSACTION statement is the transaction originator and controls the completion of the transaction. The connection that creates the MS DTC transaction becomes the originator for the transaction. COMMIT TRANSACTION initiates an MS DTC coordinated commit.
When a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is issued for the session, the controlling instance requests that MS DTC manage the completion of the distributed transaction across all of the instances involved.
When a distributed query is executed in a local transaction, the transaction is automatically promoted to a distributed transaction if the target OLE DB data source supports ITransactionLocal.
If the target OLE DB data source does not support ITransactionLocal, only read-only operations are allowed in the distributed query. Transaction-Level Snapshot Isolation does not support distributed transactions.
The sp_configure remote proc trans option controls whether calls to remote stored procedures in a local transaction automatically cause the local transaction to be promoted to a distributed transaction managed by MS DTC.
The connection-level SET option REMOTE_PROC_TRANSACTIONS can be used to override the instance default established by sp_configure remote proc trans.
Q159. What is a Two-Phase Commit (2PC) in SQL? Why it is required?
The distributed transaction uses the 2 Phase Commit. Distributed transactions span two or more servers known as resource managers. A transaction within a single instance of the Database Engine that spans two or more databases is also a distributed transaction.
Each instance of the SQL Server Database Engine can operate as a resource manager in distributed transactions coordinated by transaction managers, such as Microsoft Distributed Transaction Coordinator (MS DTC).
At the end of the transaction, the application requests the transaction to be either committed or rolled back. A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure may result in some resource managers successfully committing while others roll back the transaction.
This failure risk is achieved by managing the commit process in two phases (the prepare phase and the commit phase), which is known as a two-phase commit (2PC).
Prepare Phase:
When the transaction manager receives a commit request, it sends a prepare command to all of the resource managers involved in the transaction.
Each resource manager then does everything required to make the transaction durable, and all buffers holding log images for the transaction are flushed to disk.
As each resource manager completes the prepare phase, it returns the success or failure of the preparation to the transaction manager.
Commit Phase:
If the transaction manager receives successful prepares from all of the resource managers, it sends commit commands to each resource manager. The resource managers can then complete the commit.
If all of the resource managers report a successful commit, the transaction manager then sends a success notification to the application.
If any resource manager reported a failure to prepare, the transaction manager sends a rollback command to each resource manager and indicates the failure of the commit to the application.
Q160. What is the SET XACT_ABORT option in SQL? How does it affect the transaction?
If a run-time statement error such as a constraint violation occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error.
You can change this behavior using the SET XACT_ABORT option statement. Please watch the complete video here for more details.
After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
When errors occur, corrective action (COMMIT or ROLLBACK) should be included in the application code explicitly. One effective tool for handling errors, including those in transactions, is the Transact-SQL TRY…CATCH construct.
Some Auto Rollback Scenario in SQL:
If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction.
If the client logs off the application, any outstanding transactions are rolled back.
If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break.
If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break.
To Be Continued Part-18...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
No comments:
Post a Comment
Please do not enter any HTML. JavaScript or spam link in the comment box.