Database transaction management

Welcome to my “Database transaction management.”

A database transaction is a unit of interaction in a database management system or similar system. A similar system here means that the transaction is a clear, mutually independent, consistent and reliable system of successes and failures.https://www.blogger.com

Theoretically, the database system guarantees atomicity, consistency, isolation, and durability for each transaction. This property is called ACID according to the first letter. In practice, however, these characteristics are often mitigated to improve performance.

In some systems, transactions are called Logical Units of Work (LUW).

Purpose of transaction

Among database functions, the ability to manipulate transactions ensures that the user maintains database integrity.

A single transaction requires multiple queries to be read or written to the database. For example, it is important that if you withdraw from one account at the time of the remittance, the deposit is confirmed from another account. 

If one query fails, the database system rolls back the entire transaction or the failed query. This depends on how the DBMS is used and set up. A transaction can be rolled back manually at any time before committing.

Transactional Database

    A database that supports transactions is called a transactional database. Currently, most relational database management systems are transactional databases.

A database management system (DBMS) is a set of software tools that allow multiple users to access data in a database. The DBMS handles the needs of the user or other programs and allows the data to be used in a proper response.

 Definition: A function that specifies the format, structure, and constraints on data. At this time, the definition and description of the database are stored in the form of a catalog or a dictionary.

    Construction: It is a function to store data in a storage device managed by DBMS.

    Operation: Query for retrieving specific data, database update, report generation function, and the like.

    Shared: A feature that allows multiple users and programs to access the database simultaneously.

    Protection: Protects the system from malfunctioning hardware or software or unauthorized malicious access.

    Maintenance: A feature that allows you to reflect changing requirements over time.

Advantages

Advantages of DBMS are as follows: 

    DBMS improves data integration.

Data integration improves because the DBMS defines the relationship between the data.

    DBMS is easy to access data.

    Data control is enhanced.

    Application programs can be easily developed and managed.

    Security is enhanced.

Database management system

    Data Model Relationship Model Model Database Design Normalization References Integrity Relational Algebra Relation Logical Database Management System Relationship Database Management System Object-Relational Database Distributed Database Transaction Processing

concept

    Database ACID CRUD NULL Candidate key Foreign key Primary key Super key Delegate key

individual

    Relationship (table, column, row) view Transaction log Trigger Index Stored procedure Cursor segmentation

SQL

    SELECT INSERT UPDATE MERGE DELETE JOIN CREATE DROP COMMIT ROLLBACK TRUNCATE ALTER WHERE SAVEPOINT

Component

    Concurrency Control Data Dictionary JDBC ODBC Database Language Query Query Optimization Query Execution Plan

    Database products: Comparison of relational database management systems Database connection clients

The database (English: database, DB) is a collection of organized data.  In other words, it is a bundle of public data that can be stored and operated by integrated information of various application systems as a created list.

As technology advances in processors, computer memory, computer storage, and computer networks, the size, functionality, and performance of databases and DBMSs have increased in order of rank. The evolution of database technology is divided into three periods, depending on the data model or structure: an intrusion database,  a SQL / relational database, and a relational database.

The two main navigational data models are the tiered model, which is typical of IBM's IMS systems, and the CODASYL model (network model), implemented in many products such as IDMS.

The relational model originally proposed by Edgar F. Cudd in 1970 began with this tradition, arguing that applications should retrieve data based on their content, not the link they follow. The relational model uses a collection of money-ticket style tables, each of which is used for different types of entities. In the 1980s, computing hardware became so powerful that it enabled widespread deployment of relational systems (DBMS + applications). However, in the early 1990s, all large data processing applications were dominated by relational systems, and the situation is still the same by 2015. : IBM DB2, Oracle, MySQL, Microsoft SQL Server is the top-level DBMS [3] The dominant database language, the standardized SQL for the relational model, soon influenced the database languages of other data models.

Object-oriented databases were developed in the 1980s to overcome the inconvenience of object-oriented impedance mismatches, resulting in the term "post-relational" and the development of a hybrid object-relational database.

It is a set of information that is integrated and managed for the purpose of sharing and using by many people. It is a collection of one or more logically related data that is highly structured to facilitate the search and update. In other words, it is a collection of data that organizes several data files, eliminates duplication of data items, and structures and memorizes data.

As common data, each user can use the same data differently depending on their application purpose.  

    Database Features

    Real-time accessibility

    Continuous change

    Simultaneous sharing

    Reference to content

    Data logical independence

Advantages and disadvantages of databases

    Database Benefits

    Minimize Data Duplication

    Data Sharing

    Consistency, Integrity, and Security

    Keep your data up-to-date

    Data can be standardized

    Logical and physical independence of data

    Easy data access

    Save data storage space

    Database Disadvantages

    Database Expert Needed

    A lot of costs

    Difficult to back up and recover data

    System complexity

    Overload occurs when access is concentrated on a large disk

Database model

The body of this section is the database model.

Some current conceptual logical data models for real database implementations are as follows:

Relational Data Model

The body of this section is the relational model.

The relational data model is the simplest model of the data model. The code I worked for at IBM Labs (E.F.Codd) was proposed in 1970. This model is based on relative mathematical theory. Because the code was a mathematician, I used the concepts of mathematics, especially set theory and logic. In order to develop a data model, a theoretical modeling process, which is described as a table relation, occurs. This is called an entity-relational model. The design process for this relational database is theoretically a real implementation based on relational mathematics. The real world is represented by object relation diagrams, and objects and their relations are drawn with rectangles and lines.

SQL

Structured Query Language (SEQUEL) (Structured English Query Language) was created by IBM Research in 1974 to support an object-relational database, which is based on mathematical relational algebra and relational calculus. I have left. The data model must have a set of operations to manipulate the data. Because it defines the database structure and constraints. In other words, a set of operations of a relational data model is represented by relational algebra, which allows the user to perform multiple queries.

Choosing a database management system

After database design, you should use database management system. There is various database management system options (DBMS).

There is now an 'IMS' database management system that is not widely used but runs under an IBM mainframe environment. IMS is an abbreviation of Information Management System and performs DBMS and DC functions. The DB types managed by IMS are 'DEDB' and 'MSDB'. DEDB is a traditional hierarchical DB with Data Entry DB. MSDB is the main memory DB. DB is resident in main memory when the system is running. It is used for tasks requiring quick access. However, since the access speed is very fast and resides in memory, there are many limitations in capacity and structure.

Select DBMS language

The database language is as follows.

    Data definition language (DDL) - Commands such as Create, Alter, and Drop

    Data manipulation language (DML) - Select, Insert, Delete, Update ...

    Data control language (DCL) - Grant, Revoke, Commit, Rollback ...

transaction

The body of this part is the transaction.

A transaction is a collection of database operations that make up a single logical unit. In order to execute several transactions at the same time, consistency of the database must be ensured. There are modules for concurrency control and recovery control, which are collectively referred to as a transaction management module.

    Concurrency control module: Controls the interaction between concurrent transactions to keep the database consistent.

    Recovery control module: In order to keep the database consistent, the existing state of the database is maintained even during system failure.

Transaction scheduling has the following three concepts.

    Serial scheduling: how to execute transaction operations sequentially for each transaction

    Nonserial scheduling: parallel execution of transaction operations with interleaving

    Serializable scheduling: When the non-serial scheduling S always has the same result for the serial scheduling SS, say "Sis serializable".

There are protocols to ensure serializable transactions, such as locking methods and timestamps.

Database data structure

Indexing

A database often must meet the following ACID rules:

    Atomicity: All operations in a transaction must be performed, or none at all. If the transaction did not execute properly, roll back.

    Consistency: All transactions must satisfy the integrity conditions defined in the database.

    Isolation: Two transactions cannot affect each other. The value while the transaction is running must not be accessible by other transactions.

    Durability: Once a transaction has been successfully completed, the result should remain in the database (even if a system failure occurs).

Concurrency control is a technology that processes transactions securely and satisfies ACID rules.

Demand for the database

In 2012, Korea's domestic DB industry is showing a year-on-year growth in all areas including DB construction market, DB consulting and solution market, and DB service market. In particular, the growth trend will continue in the future as demand for ICT convergence, smart environment expansion, and big data increases in all industries. In the report, the major factors for the growth of the DB industry are the increase of new demand for analysis and utilization of big data, the increase of DB investment due to the recognition of DB asset value, and the spread of smart-based mobile service.