|
Define transaction and transaction isolation levels.
By Nishant Kumar
Transaction
A transaction is a set of operations that works as a single
unit. The transactions can be categorized into explicit, autocommit, and
implicit transactions. Every transaction should follow four properties called
the ACID properties i.e. atomicity, consistency, isolation, and durability.
|
Atomicity
Transaction ensures either modification is committed or not committed.
Consistency
The data should be in consistent state when transaction process is completed.
This means that all related tables are updated.
Isolation
SQL server supports concurrency when mean that data can be access or shared by
many users. A transaction works in isolation and doesn’t allow other
transaction to work concurrently on the same piece of data.
Durability
Data is permanent once transaction is completed and it can be recovered even if
system fails.
There are four transaction isolation levels:
Read uncommitted
Read committed
Repeatable read
Serializable
Read uncommitted isolation levels
This is the lowest isolation level which can also be called as dirty read. Using
this, you can read uncommitted data which can be rolled back at any point. With
this level, SQL server uses share lock while reading data.
Read committed isolation levels
With this level, uncommitted data can’t be read. This is default isolation level
and uses shared lock while reading data.
Repeatable read isolation levels
It locks all the data that is used in the query.
Serializable isolation levels
It locks data set until the transaction will be completed.
Data Integrity validates the data before getting stored in the columns of the
table. SQL Server supports four type of data integrity.....
The Bulk Copy is a command utility that transfer SQL data to or from a data
file.
This utility mostly used to transfer huge data to SQL server from other
database....
Shared Lock allows simultaneous access of record by multiple Select statements.
Shared Lock blocks record from updating and will remain in queue waiting while
record is accessed for reading......
Master database is system database. It contains information about server’s
configuration. It is a very important database and important to backup
Master.....
Restricting query result means return of required rows instead of all rows of
the table. This helps in reducing network traffic......
It is
system variable that returns error code of the SQL
statement.....
|