bladedragon

Introduction:#

This time, we will introduce some knowledge about database transactions. Why choose databases as the focus of this introduction?

Databases are an important component of modern networks. In the era of explosive information and big data, all industries are thriving due to the massive amount of data. The vast, diverse, and rapidly generated data is creating unprecedented value. It can be said that data is changing our lives and shaping our future. Therefore, we increasingly need more flexible and powerful databases to help us store and process data. With the development of the times, mastering database operation-related technologies has become a necessary choice for professionals in the ICT industry.

Basic Concepts#

Before introducing transactions, let's first familiarize ourselves with some basic concepts that will be used in our future learning.

1. What is a database?

A "database" is a collection of data stored together in a certain way, which can be shared by multiple users, has minimal redundancy, and is independent of applications.

2. What is a database management system?

A database management system (DBMS) is a layer of data management software located between user applications and the operating system. It is a core component of a database system. It provides methods for users or applications to access databases, including database creation, querying, updating, and various data controls.

A database management system not only allows individual users to query and modify data, but also supports multiple users. Multiple users can cause concurrent operations and congestion.

3. What is a lock?

A lock is a mechanism used by a database server to control the parallel use of data resources. The locked object only allows the user who holds the lock to operate on it, and other users can only have the opportunity to hold the lock after the lock is released.

Most databases use the following two lock strategies:

• Write operations require write locks, while read operations require read locks. Only one write lock can be assigned to a table at a time, and read requests are rejected until the write lock is released.
• Write operations require write locks, while read operations do not require locks.

Common databases like MySQL can choose different lock strategies based on different storage engines.

4. Lock Granularity

Lock granularity refers to the scope of a lock. In a database, the server can apply locks at three different levels:

• Table-level lock

  Prevents multiple users from modifying data in the same table at the same time.

• Page-level lock

  Prevents multiple users from modifying a page in a table at the same time.

• Row-level lock

  Prevents multiple users from modifying a row in a table at the same time.

5. What is a storage engine?

A database storage engine is the underlying software organization of a database. The database management system (DBMS) uses the storage engine to create, query, update, and delete data. Different storage engines provide different storage mechanisms, locking levels, and other functions.

The behavior of locks is determined by the storage engine. Improper use of storage engines can lead to deadlock. For example, MySQL has three storage engines: InnoDB, MyISAM, and MEMORY.

What is a Transaction#

Let's start with a scenario. Suppose a person is conducting a money transfer transaction at a bank. Now, he wants to transfer 10,000 yuan to another person. Under normal circumstances, the transfer process is completed almost instantly. However, if we break down the process, the transfer steps can be divided into three steps: 1. Check if the account balance is greater than 10,000 yuan, 2. Deduct 10,000 yuan from the original account, 3. Add 10,000 yuan to the recipient's account. We can see that if there is a deviation in any of these steps, it could lead to irreparable losses. Therefore, to ensure that the amount in the bank is not lost, we can do the following: temporarily cache the 10,000 yuan, and only add it to the recipient's account after confirming that the deduction has been made from the original account. Otherwise, all operations are invalid, the deduction from the original account is canceled, and the recipient's account will not increase for no reason. We call this operation a transaction rollback, and the transfer operation is a transaction.

Characteristics of Transactions#

The world is always full of various accidents, many of which are beyond our control, such as server damage, sudden power outages, system crashes, etc. Without transactions, the reliability of database operations cannot be guaranteed. However, the premise is that transactions themselves should have comprehensive standards to ensure their reliability. Therefore, the predecessors have specified the four major characteristics of transactions, known as the ACID properties. Only when a transaction strictly passes the ACID test can it play its role.

1. Atomicity

   A transaction must be treated as an indivisible unit of work. All operations within the transaction must either be committed successfully or rolled back completely. For a transaction, it is not possible to execute only part of the operations.

2. Consistency

   The database always transitions from one consistent state to another. A transaction cannot compromise the integrity of the database or the consistency of business logic. For example, regardless of whether the transfer is successful or not, it is not possible to have an additional or reduced 10,000 yuan. The total amount remains unchanged.

3. Isolation

   Modifications made by a transaction before it is committed are usually not visible to other transactions. A transaction should not affect the effects of other transactions.

4. Durability

   Once a transaction is committed, its modifications are irreversible.

The ACID properties of transactions ensure the safety and reliability of database operations. However, actual operations are never as simple as imagined. Additionally, adding transactions requires the database system to perform more additional work, which puts certain requirements on the performance of the database system.

Isolation Levels#

In reality, ensuring complete isolation for transactions is very difficult. Complete isolation requires that only one transaction can be executed on a database at a time, which would seriously affect performance. In reality, multiple transactions are often executed concurrently.

When isolation cannot be guaranteed, the database's read and write operations may face the following situations:

1. Dirty read: A transaction reads uncommitted data. For example, transaction A reads data updated by transaction B, but transaction B rolls back, resulting in transaction A reading dirty data.

2. Non-repeatable read: A transaction reads the same data twice, but between the two reads, transaction B modifies and commits the data, causing inconsistency in the two reads by transaction A.

3. Phantom read: A transaction modifies data in the entire table. Before it is committed, transaction B inserts or deletes data in the table, causing the data read by transaction A to be inconsistent with the data it needs to modify.

To address this, the SQL standard specifies four isolation levels:

1. Read Uncommitted

   Transactions can read uncommitted data without isolation control.

2. Read Committed

   Uncommitted reads are not allowed. Before a transaction starts, it can only "see" modifications made by committed transactions. This is the default isolation level for most databases (except MySQL).

3. Repeatable Read

   Ensures that multiple reads of the same record within a transaction yield consistent results. The general method is to apply exclusive locks on records that meet certain conditions within the transaction, so that other transactions cannot modify the data being operated by the transaction. This is the default isolation level for MySQL.

4. Serializable

   The highest isolation level. It enforces transactions to be executed serially by locking each row of data read. This prevents other transactions from performing operations (including adding, deleting, and modifying) on the data. However, this level should be used with caution as excessive locks can greatly affect performance.

Blocking and Deadlocks#

When multiple transactions lock a resource, other transactions that have not been allocated the lock must wait for the lock to be released, resulting in blocking.

When the blocking time becomes permanent, a deadlock occurs.

In a transaction, if two or more transactions occupy the same resource and request a lock on the resource held by each other, a deadlock occurs.

Solutions:

1. Give up the request after the query time exceeds the lock timeout.
2. Use a lower isolation level to reduce the holding time of locks and minimize lock contention.
3. Avoid user interaction in transactions and try to access objects in a sequential manner.
4. ...

Different storage engines have implemented different deadlock detection and deadlock timeout mechanisms. Therefore, when considering solutions to deadlocks, you must take into account the implementation of the database storage engine and ensure proper transaction logging for effective troubleshooting and efficient resolution when problems occur.

The occurrence of blocking and deadlocks in transactions is actually an inevitable result of concurrent processes. If you want to learn more about concurrency and locks, you can explore related knowledge on your own.

Summary#

In this article, I introduced you to the basics of database transactions, including:

1. What a database is, what locks and transactions are.
2. The four characteristics of transactions: atomicity, isolation, consistency, and durability.
3. Situations that can occur in database read and write operations: dirty read, phantom read, and non-repeatable read.
4. Transaction isolation levels.
5. The occurrence and solutions of deadlocks.

Due to space limitations, I can only provide a brief introduction here. Databases are a vast, complex, and extremely important subject. If you are interested, I encourage you to explore the subject on your own. Please note that the content of this article may contain errors, as my knowledge is limited. I welcome any corrections from readers.

Knowledge Links#

Database learning: "SQL Learning Guide," "High Performance MySQL."