Architecture of Oracle 11g : Part 2

The second part of the memory structure of the Oracle architecture which is known as PGA (Program Global Area)

PGA: This area is always controlled by main process for which it's spun. To elaborate further, it's the memory allocated to every server process or the background process running in the memory. The configuration of the PGA changes with the dedicated server and shared server architecture. In shared server architecture the sort area is in SGA while in dedicated server the area is in PGA. Furthermore, PGA helps to localize the memory location to the dedicated User; the SQL statements executed by the user use this memory space. PGA has one to one relations with processes. 

The PGA memory structure can be classified as below:

1) SQL Area:
The area is allocated to SQL variables bind information and runtime memory structure. Every user creates its own session on Oracle server which has its own SQL area.

 In-Execution Area: 
This area is used to keep user session information whenever the user does DML operations like SELECT, INSERT, DELETE or UPDATE.  Garbage collection of In- Execution area is done as soon the output of the above queries is received. Also, the SQL statements using JOINS or GROUP BY or ORDER BY clauses use this area for sorting purpose.
OPEN_CURSOR parameter initializes the number of cursors in the session to handle SQL areas in memory. By now we know that all sorting operations are performed in the PGA cache memory, and this mode of operations is called as optimal operation mode, as it prevents disk I/O too. Further, if the memory area is not appropriately configured the SQL end up using disk in limited fashion, such operations are single pass operations. Thus, for an efficient execution of the query the memory size should be appropriated to the size of SQL queries like in OLTP the SQL statements are not data intensive, while in OLAP the queries are executed on large data to generate reports including sorting on huge dataset. 

Managing PGA size:
The PGA size can be automatically managed by setting WORKAREA_SIZE_POLICY initialization parameter to auto. We are also required to manually configure SORT_AREA_SIZE, HASH_AREA_SIZE and BITMAP_AREA_SIZE parameters to get adequate sort performance. Further, if we want to automate the memory allocation to PGA process that is, let oracle decide what is best for the users then we need only to initialize PGA_AGGREGATE_TARGET parameter. 

Now let us discuss the second major part of oracle architecture i.e "LOGICAL STRUCTURE".

The Logical structure of the Oracle database is hierarchal in nature.

1) Data Block: This is the most basic part of the logical composition of the database. It contains specified number of bytes on the disk. DB_BLOCK_SIZE parameter initializes the size of the data block, which can range from 2KB to 32 KB. For a good performance we should size data block as multiple of OS level data block size. The main role of oracle data block comes in block recovery via RMAN, this happens in the scenario when there is block corruption due to some hardware, i/o, oracle or OS level reasons, then we create block dump to see the data present in the data block which done by the process of binary dumping. ALTER SYSTEM commands can be used to create data block dump files

2) Extents:  It is a combination of two or more data blocks. Please remember these data blocks are in consecutive manner. This is also a unit of space allocation. We can understand it better with example: If we create a table we can specify the initial storage space to be allocated in extents and also gives its next extent range to a max range.

CREATE TABLE emp 
(emp_no     NUMBER(2), 
 emp_name   VARCHAR2(14), 
 location   VARCHAR2(13) ) 
 STORAGE  ( INITIAL 100K  NEXT     50K 
            MAXEXTENTS 50 );

3) Segments: It is a collection of extents which is allocated for use in structures like index or table or so. Thus, the space allocated to above created EMP table will be called as the EMP table segments. Similarly, the other data objects like sort segments, undo segments etc.; will have the segments name same as the object name. 

4) Table space: This is formed by allocation of similar segments. It contains one or more data files and all objects are inside the table space. These data files are oracle formatted OS files. It is advised that related tables be kept in the same table space. Space management can be done by adding new data files if the space is full, else it is managed automatically by the oracle. Below are the default table space create while installing the Oracle DB

a) SYSAUX table space 

b) System table space 

c) Temporary table space 

d) Undo table space

e) Default table space

Advantage of using table space:

I. It helps in easy recovery and backups as it acts as a unit.

II. It`s easy to allocate space.

III. We can offline a particular table space without bringing down the entire table space.

IV. Table space helps increase performance as we can use data files which resets on different disks, further controlling the I/O on the disks.

V. Also we can use import/export utility at table space level.

And finally the third part which is "PHYSICAL DATABASE STRUCTURE": 

In this I am going to share about how the oracle database files are kept in the OS level. The physical data structure mainly consists of three types of files:

1) Data files: 
The data files as discussed above are used to store logical entity like Table space. The data files can be part of one or more data base or entirely belong to one database. These data files store all the tables, indexes on the physical files. If there is a read or write then there is I/O operation for the disks where the data files are saved. Oracle managed Files (OMF) which was introduced in Oracle8i has automated the management of the Operating System file. We need not mention data file names while performing operations on data objects. In oracle we can map a table space to a data files but we cannot map table or data inside a table space to a data files directly.

2) Control files: 
This is the most vital file of Oracle Database, without this file database cannot function or even be brought up. It contains the recent state information of the database, and because of this it is maintained in multiplexed form, generally three copies. The control file consists of the information about the names and locations of the data files, backup set details, redo log files,  current log sequence numbers and the all- significant system change number (SCN), which indicates the most recent version of committed changes in the database information that is restricted form the users even for reading purposes. The changes in the control file are made by oracle processes only, which again is replicated in all the copies of the Control file. When we bring up the Oracle database in mount state it reads the control file to make itself aware of the database status and where about of its configuration and data files. V$CONTROLFILE is the dynamic view which provides us information about the control files. The control files end with .ctl extension.

3) Redo log Files:      
The redo log files actually records everything (DML operations) happening on the database. These are used for recovery and backups also. Whenever there are changes made to the data and committed its first recorded in the redo log files even before its being updated on the data files, thus we can recover the latest changes made from the redo log files as part of recovery process. The set of redo log files that are currently being used are called online redo log file and these files are flushed to be archived and kept for recovery, these are offline files called archived redo log files. Redo log buffer area keeps the online redo log files. Further it is recommended to multiplex the redo log files because of its importance in database recovery.

4) Other important files which includes below files:

I. SPFILE: It is called server parameter files. It is the first file that is read while bringing up the database. This file contains information about the initialization parameters like memory limit, setting to determine the database configuration, archived log location and most important the CONTROL FILE location. This file is stored on the Oracle server, though the directories may differ in different Operating System. We can use V$SPPARAMETER dynamic view to get the information of the SP file.

II. Password File: This file contains the information about the SYSDBA and SYSOPER administrative privilege user, as it helps them to perform startup, shutdown, backup and recovery of database.

III.  Alert log file: This is a mandatory file of oracle database its nomenclature is alterdb_name.log where db_name is the name of the database. It captures all the major database incidents like errors related to oracle, warnings or other messages, sequence of startup, parameters of initialization, track table space regarding the addition or removal of data files. The alert log location cab be viewed by BACKGROUND_DUMP_DEST parameter. Alert log is the first step towards diagnosing an error.

IV. Trace Files: If any background process like DB Writer, Log Writer, others fail or any user process fails, these trace files help to diagnose the error. DIAGNOSE_DEST is the initialization parameter to know the destination of the trace files. These files have extension of .trc.

Backup Files: Backup files help us to recover and restore database to its present form. These backup files could be self-initiated or created via using RMAN back-up tool. 

Architecture of Oracle 11g: Part 1

It is basically divided into the following three major parts: 

1. Memory Structure

2. Logical Database Structure

3. Physical Structure

The memory structure of the 11g architecture means segregation of Logical Memory for various Processes of Oracle. Whenever the Oracle instance is started it acquires the memory set for it via the MEMORY_TARGET and MEMORY_MAX_TARGET parameters (Automatic Memory Management must be enabled for this during installation) through the Oracle Universal Installer (OUI), a Java API for Oracle DB installs.  With no further ado, let's focus on the memory structure.

The Oracle Memory Structure is primarily allocated into the two parts:

1. System Global Area (SGA)  2. Program Global Area (PGA)

Let's explain the SGA first.

The SGA or System Global Area is the most vital of them.

It is manually managed by "SGA_TARGET" parameters.

The SGA itself is further sectioned into the following:

1. Shared Pool: SHARED_POOL_SIZE

2. Data Buffer Cache: DB_CACHE_SIZE

3. Redo Log Buffer:  LOG_BUFFER

4. Large Pool: LARGE_POOL_SIZE

5. Java Pool: JAVA_POOL_SIZE

6. Streams Pool: STREAMS_POOL_SIZE

7. Sort Extent Pool

8. Flash Back Buffer

The following explains the preceding topics in detail.

1. SHARED POOL

A Shared Pool is a very vital part of SGA and its configuration is what makes the response on the Oracle DB quick, prevents unnecessary I/O Hits and CPU utilization. The Shared Pool itself consists of the following:

 

Ø Library Cache

It's the heart and soul of SQL and PL/SQL statement execution. It does what is known as "Hard Parsing" and "Soft Parsing".
Hard Parsing occurs only if the execution plan of the SQL query does not already exist in the library cache. It takes time for such queries to execute and if the size of the Shared Pool is not configured appropriately then it affects the DB performance. When the same query is repeatedly fired by the same or various users, the Least Recently Used algorithm is used to create space in the library cache for continuously hitting new and existing queries.
Soft Parsing is the best option for the fastest execution of the query since it takes the already present execution plan from the library cache and the processing time is drastically improved. Thus an appropriate size of the library cache will help not an early phase out of the SQL, PL/SQL statement.

Ø Data Dictionary Cache

Data Dictionary is the collection of data about the data, in other words it is the meta data for the DB objects like tables, views, indexes, table spaces and so on.
Other than that it is used to cache the user names, their roles privileges and so. It also acts as an authenticator for the privileges if the specific user querying the table actually has the rights to do so. If any information is not present in the Data Dictionary cache then it is read from the Data Dictionary and that is a costlier miss than the library caches miss.
 

Ø Result cache

This area is newly introduces into the 11g version. This cache is used to store the result set returned from the SQL query. Thus the next time the same query is executed the result is directly displayed with a hit on the DB for data. To use this feature the RESULT_CACHE_MODE parameter must be initialized via the PL/SQL package DBMS_RESULT_CACHE or through the Enterprise Manager.

 

2. DATA BUFFER CACHE

The Data Buffer Cache, as the name suggests, holds the data read by the server process from the data files. If the data is not present in the Data Buffer Cache and the response time is increased for it then it will first read the data into buffer cache then display it. Thus, the larger the buffer cache, the fewer the disk reads and writes are needed and the better the performance of the database. There can be a free buffer area and dirty buffer area that is the data under manipulation and the pinned data that is currently in use by the active buffer. Further this buffer cache uses a complex combination of algorithms like LRU and Touch count. Also we can create multiple Data Buffer Caches and use the ALTER TABLE or ALTER INDEX command to modify the type of buffer pool that a table or an index should use. DB_CACHE_SIZE is the parameter to set the Data Buffer size. The keep and the recycle buffer pools are purely optional, while the default buffer pool is mandatory. DB_KEEP_CACHE_SIZE helps to maintain the data blocks in memory. We may have small tables that are frequently accessed, so we can assign the tables to the keep buffer cache when they are created.

DB_RECYCLE_CACHE_SIZE:  It helps to remove the data from the cache immediately after use. It will cycle out the object from the cache as soon as the transaction is over. This is ideal for large tables where transactions are not constant. 

 

3. REDO LOG BUFFER

It is set by the LOG_BUFFER initialization parameter and it's a circular buffer in SGA. The size of the Redo log buffer is just a couple of MBs and if it's once fixed then it cannot be dynamically resized like other parameters. The Log writer flushes the redo log to the disk for the following circumstances:

· The user commits a transaction.

· The redo log buffer is 1/3rd full.

· The database buffer cache is running low on space and thus the DB writer instruct the log writer process to flush the log buffer contents to disk to make space for the new data.

 

4.  LARGE POOL

It is an optional memory pool, it's useful only:

· When parallel quires are fired on the database.

· As session memory on shared servers environment.

· For backup and restore using the RMAN facility of Oracle.

The size of the Large pool is set by the LARGE_POOL_SIZE parameter.

5.  Java POOL

This memory space is configured for the database using Java code. This segregation helps to provide a definitive space for a Java app to run without affecting other functionality. It has reserved memory for the Java Virtual Machine (JVM).

 

6. THE STREAMS POOL
The STREAMS_POOL_SIZE initialization parameter helps set the stream pool component. It is useful for data sharing among various databases and various applications. If the automatic shared memory management option is enabled then the space allocated is almost 10 percent of the shared pool size.

 

7.  SORT EXTEND POOL

In a shared server scenario the sort extent pool is used to direct the extent of Sort segments within the temporary tables pace as may be requested by the user process. In a dedicated server scenario the sort extent pool resides in the PGA. V$SORT_SEGMENT contains the information about the sort extent pool that may help us decide to size our extent properly.

 

8.  FLASH BACK POOL

A Flash Back Buffer is used for the following purposes:

· Flash Back Query: It includes flashback query to retrieve data from previously fired queries.

· Flash Back version queries: It displays the various version of data rows along with the first and last execution time of a specific transaction leading to the creation of that row.

· Flash Back Transaction Query: It helps to retrieve historical data for any transaction so that we can undo them.

· Flash Back Table: This feature can be used to reinstate any table to its earlier version.

· Flash Back Database: As the name states. It is used for the recovery of the entire database to a specific time in the past.

· Flash Back Drop: This feature is quite handy if we accidentally drop a table and thus simply want to recover it. The DBMS_FLASHBACK parameter initializes it.

What is Oracle Database?

ummary: in this tutorial, you will have an overview of the Oracle Database, its features, andWhat is a database

A database is an organized collection of structured data stored electronically in a computer system.

When the computer was first invented, it was mainly used for scientific researches to perform calculation quickly.

Since the computer was adopted more and more, the requirements were also increased to require the computer to store a larger volume of data for fast retrieval.

Before the database system was invented, the flat file structure was commonly used to store data. For example, here is the comma-separated value (CSV) file that stores employee information:

first name, last name, phone
John, Doe, (408)-245-2345
Jane, Doe, (503)-234-2355
...

The CSV file has three columns which are known as fields and rows which are known as records. When the number of rows in the flat file is increased e.g., million rows, it becomes unmanageable.

In the 1970s, Dr. Ted Codd, a computer scientist, invented the relational model for database management. The relational model deals with many issues caused by the flat file model. According to his model, data is organized in entities and attributes, instead of combining everything in a single structure.

An entity is a person, place, or thing and attributes describe the person, place, and thing. For example, you can use the relational model to organize the employee information into an employee entity with the attributes: first name, last name, and phone:

Each employee may have one or more contacts, you can create a contact entity and relate the employee entity to the contacts entity through a relationship called one-to-many.

By the way, we often refer the entities as tables, records as rows and fields as columns.

The relational model is better than the flat file model because it removes the duplicate data e.g. if you put employee and contact information on the same file. The employee, who has more than one contact, will appear in multiple rows.

The Relational Database Management System, or RDBMS in short, manages relational data. Oracle Database is an RDBMS with the largest market share.

Besides the Oracle Database, there are other RDBMS products available. Here are some notable ones:

• Db2 from IBM.
• SQL Server from Microsoft.
• MySQL – the most popular open-source database, also from Oracle.
• PostgreSQL – the most advanced open source database.

Oracle Database features

Oracle Database allows you to quickly and safely store and retrieve data. Here are the integration benefits of the Oracle Database:

• Oracle Database is cross-platform. It can run on various hardware across operating systems including Windows Server, Unix, and various distributions of GNU/Linux.
• Oracle Database has its networking stack that allows application from a different platform to communicate with the Oracle Database smoothly. For example, applications running on Windows can connect to the Oracle Database running on Unix.
• ACID-compliant – Oracle is ACID-compliant Database that helps maintain data integrity and reliability.
• Commitment to open technologies – Oracle is one of the first Database that supported GNU/Linux in the late 1990s before GNU/Linux become a commerce product. It has been supporting this open platform since then.

Oracle Database has several structural features that make it popular:

• Logical data structure – Oracle uses the logical data structure to store data so that you can interact with the database without knowing where the data is stored physically.
• Partitioning – is a high-performance feature that allows you to divide a large table into different pieces and store each piece across storage devices.
• Memory caching – the memory caching architecture allows you to scale up a very large database that still can perform at a high speed.
• Data Dictionary is a set of internal tables and views that support administer Oracle Database more effectively.
• Backup and recovery – ensure the integrity of the data in case of system failure. Oracle includes a powerful tool called Recovery Manager (RMAN) – allows DBA to perform cold, hot, and incremental database backups and point-in-time recoveries.
• Clustering – Oracle Real Application Clusters (RAC) – Oracle enables high availability that enables the system is up and running without interruption of services in case one or more server in a cluster fails.

Oracle Database Editions

Oracle provides three main editions of Oracle Databases as follows:

1) Enterprise Edition (EE) is the common and expensive edition of the Oracle Database. It has the following characteristics:

• No maximum number of CPUs
• No limits on memory or database size
• Include premium features that are not available in other editions.

2) Standard Edition (SE) is a limited edition of the Enterprise Edition that has the following characteristics:

• Limited to four or fewer CPUs
• No limit on memory or database size
• Include many features, but no as many as EE

3) Expression Edition (XE) is a free-to-use version of the Oracle Database that available on both Windows and GNU/Linux platforms. These are the features of Oracle Database XE 18c:

• Limited to 2 CPUs
• Can use the maximum of 2GB of RAM, and has 12GB of user data.
• Very limited features

AAVU Cow Gomatha గోవు

గోవు గురించి తెలియనివారు ఉండరు. ఎందుకంటే దీనివలన కలిగే ప్రయోజనాలు చాలా ఎక్కవ. గోవును హిందువులు ఆరాధ్య దైవంగా భావిస్తారు. గోవు నుంచి వచ్చే మూత్రం సేవించడం వలన కాలేయ పనితీరు మెరుగుపడుతుంది. ఇది రోగనిరోధక శక్తిని పెంచుతుంది. ఆవు పాలకు విషాన్ని హరించే గుణం ఉంది. గోవు వలన మనకు కలిగే ఆరోగ్య ప్రయోజనాలేంటో ఇప్పుడు తెలుసుకుందాం.

1. ఆవు పేడలో కలరా వ్యాధిని వ్యాపింపచేసే క్రిములను నాశనం చేసే శక్తి ఉంది. ఆవు నెయ్యి మేధస్సును వృద్ధి చేస్తుంది.

2. ఆవు నెయ్యితో హోమం చేయడం వలన వాతావరణంలో ఉన్న క్రిములు చనిపోతాయి. పర్యావరణ పరిరక్షణలో గోవుపాత్ర ఎంతో ఉంది.

3. గోవుని ప్రతి నిత్యం పూజించటం వలన అష్టైశ్వర్యాలు సిద్ధిస్తాయి. గోవు వృష్ట భాగంలో బ్రహ్మ, మెడలో విష్ణువు ముఖాన శివుడు, రోమరోమాన మహర్షులు, దేవతలు నివశిస్తారు.

4. అంతేకాక ఆవుపేడలో అష్టలక్ష్ములు కొలువుంటాయి. ఆవు పాలు తల్లి పాల కన్నా శ్రేష్టమైనవి. ఇవి పలచగా ఉండి కొవ్వు తక్కువుగా ఉండటం వలన శరీర బరువుని నియంత్రిస్తాయి.

5. ఉదర సంబంధమైన జబ్బులను తగ్గించడంలో ఈ పాలు ప్రముఖ పాత్ర వహిస్తాయి. ఆవు పాలలో విటమిన్ ఎ పుష్కలంగా ఉంటుంది. అంతేకాకుండా దీనిలో కెఫిన్ అనే ఎంజైము ఉండటం వలన పాలు పసుపు రంగులో ఉంటాయి. ఈ ఆవు పాలను ప్రతి రోజు తాగడం వలన వృద్ధాప్య ఛాయలు దరిచేరవు.

Cow ( Gomatha / AAVU )

Cow (Cattle) was one of the first few species domesticated by humans (or evoluting modern humans) after dogs and horses. Few civilizations like Aryans and Indian were so much dependent on cattle over a period of time that they started worshipping them like mother and Goddess.

The designation of mother was given because of her ability to give milk which was suitable for human consumption. Milk and its products then became one of the base material for almost all Indian food and sweets, which we still have.

Apart from milk different benefits of keeping cows with you are:

• Drought animal power from bulls and bullocks. For centuaries they were main source of animal power in Indian agriculture (maybe in other parts of world too). They were also used as a means of transportation in form of bullock carts (slower than horses).
• Cattle dung as manure for agriculture systems. Before industrialization the main source of manure for agriculture was cattle dung, whcih was decomposed in specific ways by Indians and used in their fields. Still the modern concept of Organic farming promotes this way which was earlier used in all Indian subcontinent.
• Medicinal and Pesticidal properties of cow urine. In Ayurveda (An Indian system of healing) there are many uses of cow urine. Cow urine can be fermented with some herbs like Neem and Mahua to make effective pesticide for agriculture.
• Worshipped on certain occasions. There are certain celebrations in India when people worship the cow.
• Religion and Recreation. Some priests recommend people to worship and feed cattle in order to get rid of some sins of your past or present birth.

Amway How to Generate Income

Business Blog

Health Blog

Health Related

https://goodhealthstrongwealth.blogspot.com

Dead Lock - 2

What is a database deadlock? Provide an example and explanation of a deadlock in a database.

In a database, a deadlock is a situation that occurs when two or more different database sessions have some data locked, and each database session requests a lock on the data that another, different, session has already locked. Because the sessions are waiting for each other, nothing can get done, and the sessions just waste time instead. This scenario where nothing happens because of sessions waiting indefinitely for each other is known as deadlock.

If you are confused, some examples of deadlock should definitely help clarify what goes on during deadlock. And, you should probably read our explanation of database locks before proceeding since that will help your understanding as well.

Database deadlock example

Suppose we have two database sessions called A and B. Let’s say that session A requests and has a lock on some data – and let’s call the data Y. And then session B has a lock on some data that we will call Z. But now, lets say that session A needs a lock on data Z in order to run another SQL statement, but that lock is currently held by session B. And, let’s say that session B needs a lock on data Y, but that lock is currently held by session A. This means that session B is waiting on session A’s lock and session B is waiting for session A’s lock. And this is what deadlock is all about!

Let’s go through a more detailed (and less abstract) example of deadlock so that you can get a more specific idea of how deadlock can arise.

Database deadlock example in banking

Let’s use an example of two database users working at a bank – let’s call those database users X and Y. Let’s say that user X works in the customer service department and has to update the database for two of the banks customers, because one customer (call him customer A) incorrectly received $5,000 in his account when it should have gone to another customer (call him customer B) – so user X has to debit customer X’s account by $5,000 and also credit customer Y’s account $5,000.

Note that the crediting of customer B and debiting of customer A will be run as a single transaction – this is important for the discussion that follows.

Now, let’s also say that the other database user – Y – works in the IT department and has to go through the customers table and update the zip code of all customers who currently have a zip code of 94520, because that zip code has now been changed to 94521. So, the SQL for this would simply have a WHERE clause that would limit the update to customers with a zip code of 94520.

Also, both customers A and B currently have zip codes of 94520, which means that their information will be updated by database user Y.

Here is a breakdown of the events in our fictitious example that lead to deadlock:

• 1. Database user X in the customer service department selects customer A’s data and updates A’s bank balance to debit/decrease it by $5,000. However, what’s important here is that there is no COMMIT issued yet because database user X still has to update customer B’s balance to increase/credit by $5,000 – and those 2 separate SQL statements will run as a single SQL transaction. Most importantly, this means that database user X still holds a lock on the row for customer A because his transaction is not fully committed yet (he still has to update customer A). The lock on the row for customer A will stay until the transaction is committed.
• 2. Database user Y then has to run his SQL to update the zip codes for customers with zip codes of 94520. The SQL then updates customer B’s zip code. But, because the SQL statement from user Y must be run as a single transaction, the transaction has not committed yet because all of the customers haven’t had their zip codes changed yet. So, this means that database user Y holds a lock on the row for customer B. .
• 3. Now, Database user X still has to run the SQL statement that will update customer B’s balance to increase it by $5,000. But, now the problem is that database user Y has a lock on the row for customer B. This means that the request to update customer B’s balance must wait for user Y to release the lock on customer B. So, database user X is waiting for user Y to release a lock on customer B.
• 4. Now, the SQL statement being run by user Y tries to update the zip code for customer A. But, this update can not happen because user X holds a lock on customer A’s row. So, user Y is waiting for a lock to be released by user X.
• 5. Now you can see that we have user X waiting for user Y to release a lock and user Y waiting for user X to release a lock. This is the situation of deadlock, since neither user can make any progress, and nothing happens because they are both waiting for each other. So, in theory, these two database sessions will be stalled forever. But, read on to see how some DBMS’s deal with this unique situation.

Database deadlock prevention

So now you have seen an example of deadlock. The question is how do DBMS’s deal with it? Well, very few modern DBMS’s can actually prevent or avoid deadlocks, because there’s a lot of overhead required in order to do so. This is because the DBMS’s that do try to prevent deadlocks have to try to predict what a database user will do next, and the theory behind deadlock prevention is that each lock request is inspected to see if it has the potential to cause contention. If that is the case, then the lock is not allowed to be placed.

Database deadlock detection

Instead of deadlock prevention, the more popular approach to dealing with database deadlocks is deadlock detection. What is deadlock detection? Well, deadlock detection is based on the principle that one of the requests that caused the deadlock should be aborted.

How does deadlock detection work?

There are two common approaches to deadlock detection: 1. Whenever a session is waiting for a lock to be released it is in what’s known as a “lock wait” state. One way deadlock detection is implemented is to simply set the lock wait time period to a certain preset limit (like 5 seconds). So, if a session waits more than 5 seconds for a lock to free up, then that session will will be terminated. 2. The RDBMS can regularly inspect all the locks currently in place to see if there are any two sessions that have locked each other out and are in a state of deadlock.

In either of the deadlock detection methods, one of the requests will have to be terminated to stop the deadlock. This also means that any transaction changes which came before the request will have to be rolled back so that the other request can make progress and finish.