SQL Server

Application of Triggers and Understanding the Transaction Procedures in Database Part – 7

Application of Triggers and Understanding the Transaction Procedures in Database Part – 7


How to Control SQL Server Locks?

The different types of locks are applied spontaneously by the SQL Server only. By default, the SQL Server locks every single row / tuple which an individual request. At times when an individual request a huge result set, the locks can raise from rows / tuples to information pages as well as to the table / relation levels too. When the query executed by an individual consumes time to execute, it will then stop extra workers from gaining access to the database items. As a consequence this reduces the chances of concurrency in the database. Moreover, an individual may want to alter the lock type from a usual shared lock (S) to an exclusive lock (X). To solve these difficulties, an individual should make practice of using the isolation levels. The individual can practice isolation levels to stipulate the privileges that an additional transaction will have on the information initiated for alteration by a particular transaction. The SQL Server provides subsequent sorts of isolation levels:-

· Snapshot

· Read Committed

· Read Uncommitted

· Repeatable Read

· Serializable

Snapshot

Snapshot isolation offers every single transaction with a photo of the present data. Every single transaction performs the task plus marks the modifications on its individual copy of the information. Whenever a transaction is prepared to update the modifications, it examines if the information has been altered meanwhile the time it has started functioning on the information as well as chooses whether to update the information or not.

Read Committed

Read Committed isolation level stipulates that not a single transaction can read the information which is getting altered by means of the present transaction. As a result this stops the difficulty of dirty read. This isolation level puts an exclusive lock (X) on every single update command in the present transaction. Whenever this isolation level is fixed, additional transactions can alter the information among the separate commands inside the present transaction. As consequence this result in a difficulty of phantom read.

Read Uncommitted

Read Uncommitted isolation level stipulates that any transaction can read the information which has been altered by means of the present transaction however the alterations are not yet committed. Transaction executing with this isolation level has no shared lock (S) present on the database item, which in turn permitting additional transactions to alter the information begin read by means of the present transaction. The database items are not saved by the exclusive lock (X) too, which in turn allowing additional transaction to read the data altered but not yet committed by means of the present transaction. Whenever this level is fixed the transaction can read the uncommitted information ensuing in the dirty read difficulty.

Repeatable Read

Repeatable Read isolation level stipulates that not a single transaction can read the information which is getting altered by means of the present transaction. Furthermore, not a single transaction can modify the information among the distinct commands even inside the present transaction. This isolation level puts an exclusive lock (X) on every single update command inside the present transaction. Moreover, it puts a shared lock (S) on every single select command. After this isolation level is set, additional transactions can add fresh rows / tuples that as a consequence create a phantom read.

Serializable

Serializable isolation level stipulates that not a single transaction can read, alter and add fresh information when the information is getting read or altered by means of the present transaction. This is the highest isolation level offered by the SQL Server as it puts a lock on every single command of the transaction. At this isolation level, the simultaneous actions are at the minimum.

Applying the Isolation Levels in Practical

An individual can apply the isolation levels in the transactions by means of the SET TRANSACTION ISOLATION LEVEL command in advance of starting the transaction. The code of the SET TRANSACTION ISOLATION LEVEL is as follows:

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } [ ; ]

BEGIN TRANSACTION My_Transaction_Name

—- T – SQL Statements —-

COMMIT TRANSACTION My_Transaction_Name

For an instance, while altering the loan details of a customer, an individual do not desire any additional transaction to read the uncommitted data. So, the individual can make use of the subsequent commands to fulfill the need:-

SET TRANSACTION ISOLATION LEVEL

READ COMMITTED

BEGIN TRANSACTION TranLoanDetails

BEGIN TRY

UPDATE LoanDetails SET ROI = ‘ 11.44% ’ WHERE CustomerID = 20099474 AND LoanID = ‘ L#063 ’

UPDATE CutomerDetails SET LoanAmt = ‘ 123456 ’

WHERE CustomerID = 20099474

COMMIT TRANSACTION TranLoanDetails

PRINT ‘ Transaction is executed successfully ’

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION TranLoanDetails

PRINT ‘ The commands Failed. So the Transaction – TranLoanDetails is rolled back, please try again.. ’

END CATCH

The above command fixes the isolation level of transaction TranLoanDetails as READ COMMITTED. As a result this stops additional transaction from accessing the uncommitted alteration from these tables / relations.

The upcoming article will define What is Deadlock? Next it will show How to Identify the Occurring Deadlocks? How to Set the Deadlock Significance? How to Alter the Lock Timeout? Along with some tips and suggestion of How to Dodge the Deadlocks by means of Update Lock?