What is database concurrency control? What is the main method of database concurrency control?

A database is a shared resource that can be used by multiple users. These user programs can be executed serially, one by one,

Only one user program runs at a time,

Perform access to the database,

Other user programs must wait until this user process

Access to the database after the end of the sequence. But if a user program involves a lot of data input

/

Output interchange, the

The database system spends most of its time in idle state.

So

In order to make full use of database resources,

Play a database share

The characteristics of resources,

Multiple users should be allowed to access the database in parallel.

However, this will result in multiple user program concurrent access

The same data situation,

If you do not control concurrency, you may be able to access and store incorrect data.

Destroying the database

Consistency

Therefore, the database management system must provide concurrency control mechanism.

Concurrency control mechanism is good or bad is to measure a data

One of the important signs of library management system performance.

Dm

Use blocking mechanisms to solve concurrency problems. It can be guaranteed to have multiple running user programs at any time,

However, all user programs run in environments that are completely isolated from each other.

One

Preliminary knowledge of concurrency control

(

One

)

Concurrency control Overview

Concurrency control is a transaction (

Transaction

) for the unit.

1.

Units of concurrency control

――

Transaction

A transaction is a logical unit of work for a database, which is a user-defined set of action sequences. A transaction can be a set of

Sql

Statement, a

Sql

Statement or the entire program.

The start and end of a transaction can be controlled by the user,

If the user does not explicitly define a transaction,

The database system

Automatically divides transactions by default.

Transactions should have

4

Properties: atomicity, consistency, isolation, and persistence.

（

1

) atomicity

The atomicity of a transaction guarantees that the transaction contains a set of update operations that are atomic, which means that the operation is an integer

Body

Do it all or no to the database,

cannot be partially completed.

This nature can be even after a system crash

To ensure that

Database recovery will take place after the system crashes.

Transactions that are used to restore and revoke a system crash in an active state

The impact on the database,

Thus guaranteeing the atomicity of the transaction.

The system changes any actual data on the disk before

The information that modifies the operation information itself is logged to disk.

When a crash occurs,

The system was able to record the event according to these operations.

The state of affairs in which

This determines that all modifications made by the firm are revoked,

Or will the modified operation be re-

Yes.

（

2

) consistency

Consistency requires that after the transaction is completed,

Transition a database from one consistent state to another.

It is a

Consistency rules are based on logical attributes,

For example, in the operation of a transfer,

The amount of each account must be balanced,

This rule is a way to

is a mandatory requirement for programmers,

Thus

Consistency is closely related to atomicity.

Consistency of transactions

The transaction property requires that the transaction consistency is still satisfied in the case of concurrent execution.

It's logically not independent,

It by the thing

The separation of services.

（

3

）

Isolation of

Isolation means that the execution of a transaction cannot be disturbed by other transactions.

That is, the operation within a transaction and the data used

Other transactions that are concurrent are isolated,

Each transaction that executes concurrently cannot interfere with each other.

It requires that even if there are multiple transactions

Concurrent execution, it appears that each successful transaction executes as a serial schedule. Another method of calling this property is serializable,

This means that any interleaved operation that is allowed by the system is equivalent to a serial dispatch.

Serial scheduling means that each time the dispatch of a

Transaction, the additional transaction operation cannot begin until all operations of one transaction are not completed. Due to performance reasons,

We need to schedule the interleaving operation,

But we also want the effect of these interleaved operations and a certain serial dispatch

is the same.

Dm

Implementing this mechanism is to exclude other things by adding appropriate locks to the data access objects of the transaction

Concurrent operations on the same database object.

（

4

) Persistence

System-provided durability guarantees require that once a transaction is committed,

Then the changes made to the database will be persistent,

Whatever happens

Any machine or system failure should not have any effect on it. For example, ATM machines (

Atm

) in payment to the customer

A sum of money,

You don't have to worry about losing a customer's withdrawal record.

The persistence of a transaction guarantees that the impact of the transaction on the database is persistent

, even if the system crashes. As mentioned in the atomic Nature, the system provides this assurance by making a record.

Dm

There is no statement that explicitly defines a transaction start, the first executable

Sql

Statement

(

Except

CONNECT

Statement outside

)

The beginning of the implied transaction,

But the end of the transaction can be explicitly controlled by the user.

In

Dm

The following scenarios are ended

(

Is

Often, non-normal

)

A transaction:

（

1

When the properties of a connection are set to autocommit, each execution of a statement is committed;

（

2

) encountered

Commit/rollback

Statement, then submit

/

rolling back a transaction;

（

3

) when the system's

Ddl

When the auto-commit switch is on (on by default), it encounters

Ddl

Statement will automatically submit the

Ddl

Statement and the previous

Dml

And

Ddl

Operation

（

4

) The program where the transaction ends and the user exits;

（

5

) When the system is not properly terminated;

Description

Dm

Provided in the configuration file

Ddl

Auto-commit switch for statement

Ddl_auto_commit

。

When this match

The value of the set item is

1

(by default), all

Ddl

The statement is automatically committed when the value of this configuration item is

0

When, in addition to

CreateDatabase

、

Alterdatabase

And

Createschema

All the statements outside the

Ddl

Statement

are not automatically submitted.

Dm

Consistency in a transaction is based on transactions.

Dm

Use commit and rollback respectively to make modifications to the database permanent

and repeal, but either commit and rollback,

Dm

Ensure that the database is consistent at the beginning and end of each transaction. For

To improve the flexibility of transaction management,

Dm

Provides a set save point (

SavePoint

) statement and rollback to the Save point language

Sentence

Save points provide a flexible rollback,

Transactions can be rolled back to a savepoint during execution.

Before the Save point

Operation is valid, and subsequent operations are rolled back.

Dm

Also have the above

4

Properties: atomicity, consistency, isolation, and persistence.

2.

Inconsistencies in concurrency and data

If there is no lock and multiple users access a database at the same time,

When their transactions use the same data at the same time, they may

A problem occurs that results in inconsistencies in the data in the database.

One of the most common examples of concurrent operations is the train

/

Booking operation in the aircraft booking system. In the system, for example, a

Sequence of activities:

①

A conductor reads out the balance of a flight ticket

A

, set

A=16

；

Ii

b Conductor read out the balance of ticket number on the same flight

A

Also

16

；

③

A conductor sells a ticket and modifies the balance of the ticket

A=a-1=15,

Put

A

Write back to the database;

Download documents to PC while saving to cloud knowledge for easier management

1 Download voucher 5.0 points ( documents above 99%)

Download

14 pages left unread, read on

Database training, Dahne MySQL database training, 120-day crash masterAdvertisingView Details

What is database concurrency control? What is the main method of database concurrency control?