Shulou(Shulou.com)06/01 Report--

concurrency control

Lock granularity:

table-level locks

row-level locks

Lock:

Read lock: shared lock, read-only, non-writable, multiple reads do not block,

Write lock: exclusive lock, exclusive lock, one write lock blocks other read and write locks

achieve

Storage engines: implement their own lock policies and lock granularity

Server-level: locks implemented, table-level locks; users can explicitly request

Category:

Implicit locks: locks are automatically imposed by the storage engine

Explicit lock: manually requested by user

Locking policy: balancing mechanism between lock granularity and data security

Display Use Lock

LOCK TABLES

tbl_name [[AS] alias] lock_type

[, tbl_name [[AS] alias] lock_type] ...

lock_type: READ ， WRITE

UNLOCK TABLES Unlock

FLUSH TABLES tb_name[,...] [WITH READ LOCK]

Closes the table being opened (clears the query cache), usually with a global read lock before backing up

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

Add write or read lock when querying

Affairs

Transactions: A set of atomic SQL statements, or a single unit of work

Transaction log: record transaction information, realize undo,redo and other failure recovery functions

ACID Features:

A: atomicity Atomicity; all operations in the entire transaction are either executed successfully or rolled back after failure

C: consistency; the database always transitions from one consistency state to another

I: Isolation; operations performed by a transaction cannot be seen by other transactions before they are committed; isolation has multiple levels of isolation to achieve concurrency

D: Durability; once a transaction is committed, its changes are permanently stored in the database.

Example: A transfers money to B, A cannot deduct money and B does not raise money

Transaction life cycle

Add, delete and change are transactions, query select is not included in transactions

Affairs

START TRANSACTION

Closing transactions:

(1)COMMIT: Submit

(2)ROLLBACK: Roll Back

Note: Only transactional storage engines can support this type of operation

Recommendation: explicitly request and commit transactions instead of using the Autocommit feature

set [global] autocommit={1|0}

Default is 1 autocommit. If you want to save it permanently, write it to the configuration file. autocommit=0.

Transaction support savepoint: savepoint

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

transaction isolation level

Transaction isolation level: stricter from top to bottom

·READ UNCOMMITTED can read uncommitted data, resulting in dirty reads

·READ COMMITTED can read the submitted data, but the unsubmitted data is unreadable, resulting in non-repeatable reads, that is, multiple submitted data can be read, resulting in inconsistent data each time.

·REPEATABLE READ can be read repeatedly, and the data read for many times is consistent, resulting in phantom reading, that is, during the reading process, even if there are other submitted transactions to modify the data, only the old data before modification can be read. This is MySQL default setting

SERIALIZABILE can be serialized, with uncommitted read transactions blocking modify transactions, or uncommitted modify transactions blocking read transactions. Resulting in poor concurrency performance

MVCC: Multiversion concurrency control, transaction level dependent

transaction isolation level

Specify transaction isolation level:

Server variable tx_isolation specifies, defaults to REPEATABLE-READ, can be set at GLOBAL and SESSION levels

SET tx_isolation=''

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

server options

vim /etc/my.cnf

[mysqld]

transaction-isolation=SERIALIZABLE Note: Configuration names are different from variable names

concurrency control of transactions

Deadlock:

Two or more transactions occupy the same resource and request to lock the status of the resource occupied by the other party.

For example, transaction tr1 locks table1 row1, and then transaction2 locks table2 row2. The two do not interfere with each other. However, if tr1 tries to modify table2 row2 at this time, it will get stuck. If tr2 tries to modify table1 row1 again, both tr1 and tr2 will get stuck, resulting in deadlock.

MySQL automatically detects deadlocks and sacrifices one of the transactions to resolve the deadlock, usually the one with the shorter wait time

Transaction log:

Transaction logs are written with the type "append," so their operations are "sequential IO"; also commonly known as: writeahead logging

Log files: ib_logfile0, ib_logfile1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.