Concurrency Control and Various Methods of Concurrency Control Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Concurrency Control and Various Methods of Concurrency Control without wasting too much if your time.

The question is published on by Tutorial Guruji team.

DBMS – Methods for Concurrency Control

There are three fundamentals methods for concurrency control. They are as per the following:

  1. Locking Methods
  2. Time-stamp Methods
  3. Idealistic Methods

1. Locking Methods of Concurrency Control:

“A lock is a variable, related with the information thing, which controls the entrance of that information thing.”

Locking is the most broadly utilized type of the concurrency control. Secures are additionally isolated in three fields:

  • Lock Granularity
  • Lock Types
  • Deadlocks

1. Lock Granularity:

An information base is fundamentally spoken to as an assortment of named information things. The size of the information thing picked as the unit of security by a concurrency control program is

called GRANULARITY. Locking can occur at the accompanying level:

  • Information base level
  • Table level
  • Page level
  • Line (Tuple) level
  • Properties (fields) level

I. Information base level Locking:

At information base level bolting, the whole data set is bolted. Accordingly, it forestalls the utilization of any tables in the information base by transaction T2 while transaction T1 is being executed. Information base degree of locking is appropriate for clump measures. Being moderate, it is unsatisfactory for on-line multi-client DBMSs.

ii. Table level Locking:

At table level bolting, the whole table is bolted. In this manner, it forestalls the admittance to any line (tuple) by transaction T2 while transaction T1 is utilizing the table. on the off chance that a transaction expects admittance to a few tables, each table might be bolted. Notwithstanding, two transactions can get to a similar information base if they access various tables. Table level locking is less prohibitive than information base level. Table level locks are not appropriate for multi-client DBMS

iii. Page level Locking:

At page level bolting, the whole circle page (or plate block) is bolted. A page has a fixed size, for example, 4 K, 8 K, 16 K, 32 K, etc. A table can traverse a few pages, and a page can contain a few columns (tuples) of at least one tables. Page level of locking is generally reasonable for multi-client DBMSs.

iv. Column (Tuple) level Locking:

At column level locking, specific line (or tuple) is bolted. A lock exists for each line in each table of the information base. The DBMS permits simultaneous transactions to get to various columns of similar table, regardless of whether the lines are situated on the same wavelength. The line level lock is substantially less prohibitive than information base level, table level, or page level locks. The line level locking improves the accessibility of information. Be that as it may, the administration of column level locking requires high overhead expense.

v. Characteristics (fields) level Locking:

At characteristic level locking, specific quality (or field) is bolted. Property level locking permits simultaneous transactions to get to a similar column if they require the utilization of various credits inside the line. The quality level lock yields the most adaptable multi-client information access. It requires an elevated level of PC overhead.

2. Lock Types:

The DBMS mainly utilizes following kinds of locking strategies.

  • Double Locking
  • Shared/Exclusive Locking
  • Two – Phase Locking (2PL)

a. Double Locking:

A double lock can have two states or qualities: bolted and opened (or 1 and 0, for effortlessness). A particular lock is related with every information base thing X.

If the estimation of the lock on X is 1, thing X cannot be gotten to by an information base activity that demands the thing. On the off chance that the estimation of the lock on X is 0, the thing can be gotten to when mentioned. We allude to the current worth (or condition) of the lock related with thing X as LOCK(X).

Two activities, lock item and unlock_item, are utilized with paired bolting.


A transaction demands admittance to a thing X by first giving a lock_item(X) activity. On the off chance that LOCK(X) = 1, the transaction is compelled to stand by. On the off chance that LOCK(X) = 0, it is set to 1 (the transaction bolts the thing) and the transaction is permitted to get to thing X.

Unlock_item (X):

At the point when the transaction is through utilizing the thing, it gives an unlock_item(X) activity, which sets LOCK(X) to 0 (opens the thing) so X might be gotten to by different transactions. Consequently, a paired lock implements common prohibition on the information thing, i.e., at a time only one transaction can hold a lock.

b. Shared/Exclusive Locking:

Shared lock:

These locks are referred as read bolts and signified by ‘S’.

If a transaction T has gotten Shared-lock on information thing X, at that point T can read X, yet can’t write X. Different Shared lock can be set at the same time on an information thing.

Selective lock:

These Locks are alluded as Write bolts and indicated by ‘X’.

If a transaction T has acquired Exclusive lock on information thing X, at that point T can be read just as write X. Just a single Exclusive lock can be set on an information thing at a time. This implies multiple transactions does not alter a similar information at the same time.

c. Two-Phase Locking (2PL):

Two-stage locking (additionally called 2PL) is a method or a convention of controlling simultaneous preparing in which all locking tasks go before the first opening activity. Accordingly, a transaction is said to follow the two-stage locking convention if all locking tasks, (for example, read_Lock, write_Lock) go before the first open activity in the transaction. Two-stage locking is the standard convention used to keep up level 3 consistency 2PL characterizes how transactions secure and give up locks. The fundamental control is that after a transaction has delivered a lock it may not get any further bolts. 2PL has the accompanying two stages:

A developing stage, in which a transaction secures all the necessary locks without opening any information. When all locks have been gained, the transaction is in its bolted point.

A contracting stage, in which a transaction delivers all locks and cannot acquire any new lock.

3. Deadlocks:

A Deadlock is a condition where (at least two) transactions in a set are standing by at the same time for locks held by some other transaction in the set.

Neither one of the transactions can proceed on the grounds that every transaction in the set is on a holding up line, hanging tight for one of different transactions in the set to deliver the lock on a thing. In this manner, a Deadlock is a stalemate that may result when at least two transactions are each trusting that locks will be delivered that are held by the other. Transactions whose lock demands have been denied are lined until the lock can be allowed.

A Deadlock is likewise called a round holding up condition where two transactions are pausing (straightforwardly or in a roundabout way) for one another. Hence in a gridlock, two transactions are commonly rejected from getting to the following record needed to finish their transactions, additionally called a dangerous grasp.

Gridlock Detection and Prevention:

Gridlock identification:

This strategy permits gridlock to happen, however at that point, it recognizes it and settles it. Here, an information base is occasionally checked for Deadlocks. If a gridlock is recognized, one of the transactions, associated with Deadlock cycle, is cut off. other transaction proceeds with their execution. A cut short transaction is moved back and restarted.

Gridlock Prevention:

Gridlock counteraction strategy keeps away from the conditions that lead to halting. It necessitates that each transaction locks all information things it requires ahead of time. On the off chance that any of the things cannot be gotten, none of the things are bolted. As such, a transaction mentioning another lock is cut short if there is the likelihood that a Deadlock can happen. Accordingly, a break might be utilized to cut short transactions that have been inactive for a long time. This is a basic however aimless methodology. On the off chance that the transaction is cut short, all the progressions made by this transaction are moved back and all locks got by the transaction are delivered. The transaction is then rescheduled for execution. Deadlock avoidance strategy is utilized in two-stage locking.

2. Time-Stamp Methods for Concurrency control:

Timestamp is an interesting identifier made by the DBMS to distinguish the general beginning season of a transaction.

Commonly, timestamp values are relegated in the request wherein the transactions are submitted to the framework. In this way, a timestamp can be considered as the transaction start time. Along these lines, time stepping is a method of concurrency control in which every transaction is doled out a transaction timestamp. Timestamps should have two properties to be specific

Uniqueness: The uniqueness property guarantees that no equivalent timestamp qualities can exist.

monotonicity: monotonicity guarantees that timestamp values consistently increment.

Timestamp are separated into additional fields:

  • Granule Timestamps
  • Timestamp Ordering
  • Compromise in Timestamps

1. Granule Timestamps:

Granule timestamp is a record of the timestamp of the last transaction to get to it. Every granule got to by a functioning transaction should have a granule timestamp.

A different record of last Read and Write gets to might be kept. Granule timestamp may cause. Extra Write activities for Read gets to on the off chance that they are put away with the granules. The issue can be tried not to buy keep up granule timestamps as an in-memory table. The table might be of restricted size since clashes may just happen between current transactions. A section in a granule timestamp table comprises of the granule identifier and the transaction timestamp. The record containing the biggest (most recent) granule timestamp eliminated from the table is likewise kept up. A quest for a granule timestamp, utilizing the granule identifier, will either be effective or will utilize the biggest eliminated timestamp.

2. Timestamp Ordering:

Following are the three essential variations of timestamp-based methods of concurrency control:

Complete timestamp requesting

Fractional timestamp requesting

Multi version timestamp requesting

(a) Total timestamp requesting:

The complete timestamp requesting calculation relies upon keeping up admittance to granules in timestamp request by cutting short one of the transactions engaged with any clashing access. No differentiation is made among Read and Write access, so just a solitary worth is needed for every granule timestamp.

(b) Partial timestamp requesting:

In an incomplete timestamp requesting, just non-permutable activities are requested to develop the complete timestamp requesting. For this situation, both Read and Write granule timestamps are put away.

The calculation permits the granule to be read by any transaction more youthful than the last transaction that updated the granule. A transaction is cut short on the off chance that it attempts to update a granule that has recently been gotten to by a more youthful transaction. The halfway timestamp requesting calculation cuts short less transactions than the all-out timestamp requesting calculation, at the expense of additional capacity for granule timestamps

(c) Multi version Timestamp requesting:

The multi version timestamp requesting calculation stores a few renditions of an updated granule, permitting transactions to see a reliable arrangement of forms for all granules it gets to. Thus, it diminishes the contentions that bring about transaction restarts to those where there is a Write-Write strife. Each update of a granule makes another rendition, with a related granule timestamp.

A transaction that requires read admittance to the granule sees the most youthful variant that is more seasoned than the transaction. That is, the variant having a timestamp equivalent to or promptly beneath the transaction’s timestamp.

3. Compromise in Timestamps:

To manage clashes in timestamp calculations, a few transactions engaged with clashes are made to pause and to cut short others.

Following are the primary methodologies of compromise in timestamps:

Stand by DIE:

The more established transaction hangs tight for the more youthful if the more youthful has gotten to the granule first.

The more youthful transaction is cut short (passes on) and restarted on the off chance that it attempts to get to a granule after a more seasoned simultaneous transaction.


The more established transaction pre-empts the more youthful by suspending (injuring) it if the more youthful transaction attempts to get to a granule after a more seasoned simultaneous transaction.

A more established transaction will trust that a more youthful one will submit if the more youthful has gotten to a granule that both need.

The treatment of cut short transactions is a significant part of compromise calculation. For the situation that the cut short transaction is the one mentioning access, the transaction should be restarted with another (more youthful) timestamp. It is conceivable that the transaction can be consistently cut short if there are clashes with different transactions.

A cut short transaction that had earlier admittance to granule where struggle happened can be restarted with the equivalent timestamp. This will take need by wiping out the chance of transaction being persistently bolted out.

Downsides of Timestamp

Each worth put away in the information base requires two extra timestamp fields, one for the last time the field (characteristic) was read and one for the last update.

It builds the memory necessities and the preparing overhead of information base.

3. Idealistic Methods of Concurrency Control:

The idealistic method of concurrency control depends on the supposition that contentions of information base tasks are uncommon and that it is smarter to allow transactions to hurry to culmination and just check for clashes before they submit.

An idealistic concurrency control method is otherwise called approval or affirmation methods. No checking is done while the transaction is executing. The idealistic method does not need bolting or timestamping strategies. All things being equal, a transaction is executed without limitations until it is submitted. In hopeful methods, every transaction travel through the accompanying stages:

  • Read stage
  • Approval or accreditation stage
  • Write stage

a. Read stage:

In a Read stage, the updates are readied utilizing private (or neighborhood) duplicates (or forms) of the granule. In this stage, the transaction reads estimations of submitted information from the information base, executes the required calculations, and makes the updates to a private duplicate of the data set qualities. All update tasks of the transaction are recorded in an impermanent update document, which is not gotten to by the excess transactions.

It is regular to assign a timestamp to every transaction toward the finish of its Read to decide the arrangement of transactions that should be inspected by the approval system. These arrangements of transactions are the individuals who have completed their Read stages since the beginning of the transaction being confirmed

b. Approval or affirmation stage:

In an approval (or affirmation) stage, the transaction is approved to guarantee that the progressions made will not influence the honesty and consistency of the information base.

On the off chance that the approval test is positive, the transaction goes to the write stage. If the approval test is negative, the transaction is restarted, and the progressions are disposed of. Subsequently, in this stage the rundown of granules is checked for clashes. If contentions are recognized in this stage, the transaction is cut short and restarted. The approval calculation should watch that the transaction has:

Seen all alterations of transactions submitted after it begins.

Not read granules updated by a transaction submitted after its beginning.

c. Write stage:

In a Write stage, the progressions are forever applied to the information base and the updated granules are disclosed. Something else, the updates are disposed of and the transaction is restarted. This stage is just for the Read-Write transactions and not for Read-just transactions.

Advantages of Optimistic Methods for Concurrency Control:

This method is exceptionally proficient when clashes are uncommon. A periodic clash brings about the transaction move back.

The rollback includes just the neighborhood duplicate of information, the data set is not included and hence there will not be any falling rollbacks.

Issues of Optimistic Methods for Concurrency Control:

Clashes are costly to manage since the clashing transaction should be moved back.

Longer transactions are bound to have clashes and might be consistently moved back on account of contentions with short transactions.

Uses of Optimistic Methods for Concurrency Control:

Just reasonable for conditions where there are not many clashes and no long transactions.

Adequate for generally Read or Query information base frameworks that require not many update transactions

We are here to answer your question about Concurrency Control and Various Methods of Concurrency Control - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji