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

Why Locking Technique is required?

When the locking technique is not present, the difficulties might happen whenever more than one transaction is accessing the identical information from a database at the same point of time. Following are the types of difficulties which may occur:-

· Inconsistent Analysis

· Lost Update

· Phantom Read

· Dirty Read (Uncommitted Dependence)

Now, let’s take a detailed look for the above mentioned difficulties.

Inconsistent Analysis

The inconsistent analysis difficulty is identified as a Non – Repeatable difficulty too. The difficulty rises as soon as the information is altered among the concurring read by a single worker.

For an instance, in the banking system, an employee produces a report to show the balance amount of every account. The employee takes this result to alter the information. Subsequently, the employee once more recovers the identical result to reveal the alterations. But among the implementations of these two (2) commands, some other employee alters the original (parent) table / relation. Now, the minute the employee requests the information from the same table / relation for the next time, the information is altered. As a consequence this creates a misperception.

Lost Update

The lost update happens as soon as two (2) or more (N) transactions attempt to alter the identical row / tuple. For this type of situation, every single transaction is uninformed of the additional transaction. The last update in the particular transaction line will overwrite the alteration completed by the earlier transaction. As a consequence this makes the alteration done by means of the preceding transaction misplaced.

For an instance, in the banking system, two (2) transactions at the same point of time attempting to alter the account balance specifics for a specific account. Now, both the transactions select the information form the table / relation concurrently as well as get the identical data for the present balance too. As soon as one (1) transaction is completed, the account balance is altered, but then the next transaction does not fetch the altered data. As a result, once the succeeding transaction gets completed, the alterations completed by means of the earlier transaction will be lost. As a consequence the update is misplaced.

Phantom Read

The phantom read is identified as the phantom difficulty too. The difficulty happens as soon as a fresh data is added by an employee are recognized by the transactions which was initiated earlier to the insert command.

For an instance, in the banking system, an employee initiates a transaction to inquiries the available account balance for a particular account. The request yields a value ‘Rs. 1234’. Now, the same transaction attempts to deduct an amount of ‘Rs. 123’ for the EMI. At that same point of time, the customer (owner of that particular account) has already withdrawn an amount of ‘Rs. 1200’. Now, the minute the transaction of the employee attempts to deduct the amount of ‘Rs. 123’, the necessary balance is not present and the transaction gets cancelled. As a consequence this leads to the phantom read.

Dirty Read (Uncommitted Dependence)

The dirty read is identified as uncommitted dependency too. The difficulty happens whenever a transaction requests information from a table / table while some other additional transaction is on the way of altering the same piece of information.

For an instance, the particulars of every types of loan are kept in the LoanDescription table / relation in a database. An employee is performing an inquiry to alter the rate of interest for every loan. When the modifications are initiated, some different employee creates a report from the identical table / relation. The report contains every alteration that has been done still now (before this particular update). Now the initiated update command at this point of time gets committed as well as the table /relation is newly updated at present. But in this situation, the report which was created before the commencement of this particular alteration now has information which is not in effect as well as must be handled as redundant. This type of difficulty is termed as the dirty read. In the direction of avoiding this type of difficulty, an individual must not permit one worker to select any information form the table /relation unless the database worker confirms the modifications.

Different Types of Locking Technique in SQL Server

The SQL Server applies a multi – coarse locking technique that permits transactions to lock the diverse categories of properties at dissimilar stages. To diminish the struggle on locking, the SQL Server inevitably locks properties at a level suitable to the transaction.

For an instance, row / tuple level otherwise information page level. For transaction to get the properties, the SQL Server solves a clash among the simultaneous transactions by means of locking technique. The SQL Server practices the subsequent locking techniques:-

· Update Lock (U)

· Bulk Update Lock (BU)

· Shared Lock (S)

· Schema Lock (Sch – M)

· Intent Lock (I)

· Exclusive Lock (X)

Update Lock

The Update Lock (U) lies among the shared lock (S) as well as exclusive lock (X). For an instance, to alter every loan details which are having rate of interest less than 10.00%, an individual can execute an update command on the table / relation. To control the data which requires to be updated, the command will attain a shared lock (S) on the particular table / relation.

As soon as physical alteration happens, the command attains an exclusive lock (X). In between the time gap of switching the locking modes from shared lock (S) to exclusive lock (X), some other transaction may alter the information which an individual was going to alter. For that reason, the update lock is attained. An update lock is applied on the table / relation accompanied by a shared lock (S), that stops any additional transactions from altering the table / relation unless the particular update is done.

Bulk Update Lock

The Bulk Update Lock (BU) safeguards the table / relation from every additional standard T- SQL commands, nevertheless many BULK INSERT commands or else a bulk copy execution can be done at the same point of time.

Shared Lock

The Shared Lock (S), as the name suggests and as per the purpose it permits simultaneous transactions to read a property. When there is a single shared lock (S) present on any table / relation, some additional transactions cannot alter the information on that particular table / relation. After the information has been read through the transaction the shared lock (S) reliefs the property. But on other hand this is not imaginable once the transaction isolation level is fixed to repeatable read or else higher, or when a locking hint is castoff to keep the shared lock (S) for the period of the transaction.

Schema Lock

The SQL Server even provides Schema Lock (Sch – M) for alteration whenever some Data Definition Language (DDL) activities are done on a table / relation. The SQL Server takes the Schema Stability Lock (Sch – S) when accumulating the requests. The Schema Stability Lock (Sch – S) does not hold any additional locks counting the exclusive lock (X) too. Hence, additional transactions counting those with an exclusive lock (X) on a table / relation can get executed when the request gets accumulated.

Intent Lock

The Intent lock (I), by means of its purpose, specifies that the SQL Server needs to obtain a shared lock (S) otherwise exclusive lock (X) on few of the properties which are inferior in the order.

For an instance, whenever a shared intent lock is applied at the table / relation level, the transaction puts a shared lock on the pages or else on the rows / tuples in that particular table / relation.

Applying an intent lock at the table / relation level confirms that not a single transaction can afterwards obtain an exclusive lock (X) on the particular table / relation which is holding that page. Intent lock (I) expand the power of the SQL Server since the SQL Server inspects the intent lock (I) solely at the table / relation level to control if a transaction can securely obtain a lock on that particular table / relation. Consequently, an individual should inspect each and every single row / tuple or page lock on the table / relation to define if a transaction can lock the whole table / relation.

Intent lock (I) with its functionality contains Intent Exclusive (IX), Intent Shared (IS), as well as Shared with Intent Exclusive (SIX) locks too.

Exclusive Lock

The Exclusive Lock (X), by means of its purpose, completely limits simultaneous transactions from gaining access to a property. Not a single additional transaction can read or else alter the information protected with this exclusive lock (X).

The upcoming article will exhibit How to Control SQL Server Locks? along with the different isolation levels that are currently present in the SQL Server like Snapshot, Read Committed, Read Uncommitted, Repeatable Read and Serializable. In the last section the readers will get practical application of isolation levels under the Applying the Isolation Levels in Practical.