If you consider multiprogramming environment where various transactions work together at the same time, it is vital to manage the concurrency of dealings.
We have concurrency management methods to make sure atomicity, solitude, and serializability of contingency dealings.
Concurrency management methods can be subdivided into two classes −
- Lock based protocols
- Time stamp based protocols
Learn all these as there are many database jobs in pune for freshers.
Database techniques prepared with lock-based methods use a procedure by which any deal cannot read or edit data until it gets an proper lock on it.
Locks are of two kinds −
Binary Lock − A data item has a lock and it can be in two states; it is either locked or unlocked.
Shared/exclusive − This type of lock distinguishes the locks based on their uses. If a lock is obtained on a data product to carry out write function, it is a unique lock.
Enabling more than one deal to make on the same data product would cause the data source into an unreliable state.
Read locks are shared because no data value is being modified.
Lock protocols are of 4 different types −
Simplistic Lock Protocol
Simplistic lock-based methods enable dealings to get yourself a lock on every item before a ‘write’ operation is conducted.
After the completion of ‘write’ function, the transactions may unlock the data item.
Pre-claiming Lock Protocol
Pre-claiming methods assess their functions and make a list of items on which they need locks.
Before starting a performance, the deal pre claims the locks it needs in advance. If all the locks are provided, the deal carries out and unveils all the locks when all its functions are over.
If all the locks are not provided, the deal comes back and stays until all the locks are provided.
Two-Phase Lock 2PL
This locking method separates the performance stage of a deal into three areas.
In the first aspect, when the deal begins to work, it looks for authorization for the locks it needs.
The second aspect is where the deal gets all the locks.
As soon as the deal releases its first lock, the third stage begins. In this stage, the deal cannot demand any new locks; it only releases the obtained lock.
Two-phase securing has two stages, one continues to grow, where all the tresses are being obtained by the transaction; and the second stage is reducing, where the hair organised by the deal are developing.
To declare a unique (write) lock, a deal must first obtain a shared (read) lock and then update it to a unique lock.
Strict Two-Phase Locking
The first stage of Strict-2PL is same as 2PL. After obtaining all the lock in the first stage, the deal carries on to operate normally.
But contrary to 2PL, Strict-2PL does not to unveils lock after using it. Strict-2PL keeps all the lock until the commit point and unveils all the locks at one time.
The most widely used concurrency method is the timestamp centered method.
This method uses either program time or logical counter as a timestamp.
Lock-based methods handle the purchase between the inconsistent sets among dealings at the period of performance, whereas timestamp-based methods begin being soon as a deal is designed.
Every deal has a timestamp associated with it, and the purchasing will depend on the age of the deal. A deal designed at 0002 time time would be over the age of all other dealings that come after it.
For example, any deal ‘y’ coming into the program at 0004 is 2 seconds young and the concern would be given to the mature one.
In inclusion, every data item is given the newest study and write-timestamp.
This allows the program know when the last ‘read and write’ function was conducted on the data item.
Timestamp Ordering Protocol
The timestamp-ordering method guarantees serializability among dealings in their inconsistent read and write functions.
This holds to the method program that the inconsistent couple of projects should be implemented according to the timestamp principles of the dealings.
The timestamp of deal Ti is denoted as TS(Ti).
Read time-stamp of data-item X is denoted by R-timestamp(X).
Write time-stamp of data-item X is denoted by W-timestamp(X).
Timestamp purchasing method works as follows −
If a deal Ti provides a read(X) function −
If TS(Ti) < W-timestamp(X)
If TS(Ti) >= W-timestamp(X)
All data-item timestamps modified.
If a deal Ti problems a write(X) function −
If TS(Ti) < R-timestamp(X)
If TS(Ti) < W-timestamp(X)
Operation refused and Ti rolled back.
Otherwise, function executed.
Thomas’ Write Rule
This concept declares if TS(Ti) < W-timestamp(X), then the procedure is refused and Ti is rolled back.
Time-stamp ordering rules can be customized for making the routine view serializable.
You can join the sql dba training in Pune to make your career in this field.