Exception handling strategies in all types of Databases

Depending upon the usage requirements, there are following types of databases available in the market −

• Centralised database.

The information(data) is stored at a centralized location and the users from different locations can access this data. This type of database contains application procedures that help the users to access the data even from a remote location.

Example: a desktop or server CPU, or a mainframe computer.

• Distributed database.

Just opposite of the centralized database concept, the distributed database has contributions from the common database as well as the information captured by local computers also. The data is not at one place and is distributed at various sites of an organization. These sites are connected to each other with the help of communication links which helps them to access the distributed data easily.

Example: Apache Ignite, Apache Cassandra, Apache HBase, Couchbase Server, Amazon SimpleDB, Clusterpoint, and FoundationDB.

• Personal database.

Data is collected and stored on personal computers which is small and easily manageable. The data is generally used by the same department of an organization and is accessed by a small group of people.

Example: Microsoft Access and Open Office Base

• End-user database.

The end user is usually not concerned about the transaction or operations done at various levels and is only aware of the product which may be a software or an application. Therefore, this is a shared database which is specifically designed for the end user, just like different levels’ managers. Summary of whole information is collected in this database.

Example: word processors, spreadsheet managers etc.

• Commercial database.

These are the paid versions of the huge databases designed uniquely for the users who want to access the information for help. These databases are subject specific, and one cannot afford to maintain such a huge amount of information. Access to such databases is provided through commercial links.

Example: MySQL ,MongoDB, Redis ,Oracle 

• NoSQL database.

These are used for large sets of distributed data. There are some big data performance issues which are effectively handled by relational databases, such kind of issues are easily managed by NoSQL databases. There are very efficient in analyzing large size unstructured data that may be stored at multiple virtual servers of the cloud.

Example: 

• Operational database(Transactional database).

Information related to operations of an enterprise is stored inside this database. Functional lines like marketing, employee relations, customer service etc. require such kind of databases.

The primary focus of an operational database is to support efficient and effective transactional processing of data, such as creating, updating, and deleting records. Operational databases typically store structured data, such as customer information, inventory, sales transactions, and other types of business data. They are designed to provide fast and reliable access to this data to support real-time business operations.

Example: Apache Cassandra and AWS Dynamo

• Relational database.

These databases are categorized by a set of tables where data gets fit into a pre-defined category. The table consists of rows and columns where the column has an entry for data for a specific category and rows contains instance for that data defined according to the category. The Structured Query Language (SQL) is the standard user and application program interface for a relational database.

Example:  Microsoft SQL Server, Oracle Database, MySQL and IBM DB2.

• Cloud database.

Nowadays, data has been specifically getting stored over clouds also known as a virtual environment, either in a hybrid cloud, public or private cloud. A cloud database is a database that has been optimized or built for such a virtualized environment. There are various benefits of a cloud database, some of which are the ability to pay for storage capacity and bandwidth on a per-user basis, and they provide scalability on demand, along with high availability.

Example: Amazon Web Services, SAP, Cloud SQL by Google, Azure by Microsoft

• Object-oriented database.

An object-oriented database is a collection of object-oriented programming and relational database. There are various items which are created using object-oriented programming languages like C++, Java which can be stored in relational databases, but object-oriented databases are well-suited for those items.An object-oriented database is organized around objects rather than actions, and data rather than logic. For example, a multimedia record in a relational database can be a definable data object, as opposed to an alphanumeric value.

Example:  Smalltalk is used in GemStone, LISP is used in Gbase, and COP is used in Vbase

• Graph database.

The graph is a collection of nodes and edges where each node is used to represent an entity and each edge describes the relationship between entities. A graph-oriented database, or graph database, is a type of NoSQL database that uses graph theory to store, map and query relationships.

Example: Amazon Neptune, Dgraph, OrientDB, DataStax, Kibana, Stardog, Apache Giraph, FlockDB.

Types of database exceptions

When the database server executes an SQL statement, it can return one of four types of database exceptions conditions to the application program:

• Success

The SQL statement executed successfully. When a statement that might return data into host variables executes, a success condition means that the statement has returned the data and that the program can access it through the host variables.

• Success, but warning generated

A warning is a condition that does not prevent successful execution of an SQL statement; however, the effect of the statement is limited and the statement might not produce the expected results. A warning can also provide additional information about the executed statement.

• Success, but no rows found

The SQL statement executed without errors, with the following exceptions:

• No rows matched the search criteria (the NOT FOUND condition).

• The statement did not operate on a row (the END OF DATA condition).

• Error

The SQL statement did not execute successfully and did not change the database. Runtime errors can occur at the following levels:

• Hardware errors include controller failure, bad sector on disk, and so on.

• Kernel errors include file-table overflow, insufficient semaphores, and so on.

• Access-method errors include duplicated index keys, SQL null inserted into non-null columns, and so on.

• Parser errors include invalid syntax, unknown objects, invalid statements, and so on.

• Application errors include user or lock-table overflow, and so on.

In database management systems (DBMS), an exception is an error or an unexpected condition that occurs during the execution of a database operation. Exceptions can occur due to various reasons, such as data constraints violation, invalid input data, hardware or software failure, or network issues. To handle such exceptions, DBMS provides different types of exception handling mechanisms. Here are some of the commonly used types of database exceptions:

1. Constraint Violation Exception: This type of exception occurs when a database operation violates the constraints set on the database. For example, if a database table has a unique constraint on a particular column, attempting to insert a record with a duplicate value in that column will trigger a constraint violation exception.

2. Data Type Mismatch Exception: This type of exception occurs when the data type of a value being inserted or updated in the database is not compatible with the data type specified in the database schema. For example, attempting to insert a string value into an integer column will trigger a data type mismatch exception.

3. Deadlock Exception: This type of exception occurs when two or more transactions are waiting for each other to release a lock on a database object. Deadlocks can occur when two or more transactions are accessing the same database object simultaneously.

4. Disk Full Exception: This type of exception occurs when the DBMS runs out of disk space to store the data. This can happen when the disk capacity is not sufficient to accommodate the growing data volume or when the disk is damaged or corrupted.

5. Network Exception: This type of exception occurs when there is a communication problem between the database server and the client application. This can happen due to network congestion, network failure, or firewall issues.

6. Transaction Rollback Exception: This type of exception occurs when a transaction fails to complete and needs to be rolled back. Rollback can happen due to various reasons, such as constraint violations, data type mismatches, or deadlock situations.

7. Security Exception: This type of exception occurs when there is an attempt to access a database object without proper authorization or authentication. For example, attempting to access a database table with a wrong username or password will trigger a security exception.

8. Query Timeout Exception: This type of exception occurs when a query takes too long to execute and exceeds the maximum time allowed. This can happen due to large data volumes, complex queries, or network issues.

Strategies

Exception handling in databases involves managing errors that may occur during database operations. Different databases may have different approaches to exception handling. Here are some common exception handling strategies in various types of databases:

1. Relational Databases: In relational databases, exception handling is typically done using SQL's error handling mechanism, which allows for handling exceptions through the use of TRY...CATCH blocks. The TRY block contains the code that might raise an exception, while the CATCH block contains the code that handles the exception if it occurs. The CATCH block can contain error messages, logging, and rollback or commit operations.

For example, consider a database table called "Employees" that has a constraint that ensures that each employee's salary is greater than or equal to the minimum salary for their job title. If an attempt is made to insert a new record with a salary that violates this constraint, the database system will raise an exception.

BEGIN

  INSERT INTO Employees (Name, Title, Salary)

  VALUES ('John Smith', 'Manager', 50000);

EXCEPTION

  WHEN OTHERS THEN

ROLLBACK;

    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;

In this example, the BEGIN and EXCEPTION keywords define a block of code that contains the database operation and the exception handling logic. If an exception occurs during the execution of the INSERT statement, the EXCEPTION block will be executed. In this case, the ROLLBACK statement will undo the transaction, and the DBMS_OUTPUT.PUT_LINE statement will display an error message that includes the error code and message returned by the database system.

2. NoSQL Databases: In NoSQL databases, exception handling is often done at the application level rather than at the database level. For example, in MongoDB, you can use the try-catch block in the application code to handle exceptions. Exceptions in NoSQL databases can occur due to network issues, authentication errors, or invalid queries. To handle these exceptions, it is important to have proper error handling and logging mechanisms in place.

Here is an example of how exception handling might be implemented in a NoSQL database using MongoDB:

try {

db.users.insertOne({

name: "John Smith",

email: "john@example.com"

  });

} catch (e) {

print("Error: " + e);

}

In this example, the try block contains the database operation, which is an insertOne operation to add a new user to the users collection. If an error occurs during the execution of the insertOne operation, such as a duplicate key error, the error will be caught by the catch block. In this case, the print statement will display an error message that includes the error information.

3. Graph Databases: In graph databases like Neo4j, exceptions are handled through transactional mechanisms. When a transaction fails, the transaction is rolled back to its previous state, and the error is reported. The exception handling is done through the use of the Transaction class and the TransactionFailureException class, which is raised when a transaction fails.

Here is an example of how exception handling might be implemented in a graph database using Neo4j:

try {

tx = graphDb.beginTx();

Node node = graphDb.createNode();

node.setProperty("name", "John Smith");

node.setProperty("email", "john@example.com");

tx.success();

} catch (Exception e) {

tx.failure();

throw e;

} finally {

tx.close();

}

This code snippet is an example of creating a new node in a Neo4j graph database using the Neo4j Java driver. In this example, the try block contains the database operation, which creates a new node in the graph database with two properties: name and email. If an error occurs during the execution of the operation, such as a constraint violation or a transaction failure, the catch block will be executed. In this case, the tx.failure() statement will mark the transaction as failed, and the throw statement will re-throw the exception for higher-level exception handling. Finally, the finally block will ensure that the transaction is closed, regardless of whether an exception occurred or not.

4. Time-series Databases: In time-series databases, exceptions may occur when data is being ingested or when queries are being executed. Exception handling is done by handling errors that are raised during ingestion or query execution. For example, in InfluxDB, errors can be handled using the try-catch block in the application code. The database also provides a detailed error message that can be used to troubleshoot and fix the issue.

Here is an example of how exception handling might be implemented in a time-series database using InfluxDB:

try {

 InfluxDB influxDB = InfluxDBFactory.connect("http://localhost:8086", "username", "password");

 BatchPoints batchPoints = BatchPoints.database("mydb")

   .tag("async", "true")

   .retentionPolicy("autogen")

   .consistency(ConsistencyLevel.ALL)

   .build();

 Point point = Point.measurement("temperature")

  .time(System.currentTimeMillis(), TimeUnit.MILLISECONDS)

   .addField("value", 25.0)

   .build();

  batchPoints.point(point);

  influxDB.write(batchPoints);

} catch (Exception e) {

  System.out.println("Error: " + e.getMessage());

}

In this example, the try block contains the database operation, which writes a new data point to the "temperature" measurement in the "mydb" database. If an error occurs during the execution of the operation, such as a network or server error, the catch block will be executed. In this case, the System.out.println statement will display an error message that includes the error information.

In general, exception handling in databases requires a combination of proper coding practices, error handling mechanisms, and logging. It is important to have a comprehensive error handling strategy in place to ensure that exceptions are properly handled, and data integrity is maintained.