A little progress every day ———— transaction control (3)

The need for recovery and replication, and its impact on the INNODB lock mechanism

MySQL through the Binlog record of the implementation of the Chengde insert, UPDATE, delete and other updated data SQL statements, and thus achieve the MySQL database recovery and master-slave replication.

MySQL5.6 supports 3 log formats:

Statement-based log format SBL

Row-based log format RBL

and mixed format

It also supports replication mode in 4:

Replication SBR based on SQL statements: This is also the first replication mode that MySQL supports

Row-based data replication RBR: This is the replication mode that is supported after MySQL5.1, with the advantage of supporting unsecured SQL replication.

Mixed replication mode: A replication mode based on SQL statements is used for secure SQL statements, and a row-by replication mode is used for unsafe SQL statements.

Replication using global transaction ID (gtids): Mainly to solve the problem of automatic synchronization between master and slave.

for SBL Recovery, it must be satisfied that no other concurrent transaction can insert any record that satisfies its locking criteria before a transaction is committed, that is, no phantom reads are allowed.

For INSERT INTO Target select * Source_tab where ... "and" CreateTable New_tab

.... Select .... from Source_tab where ... "this statement does not do anything to source_tab, but MySQL adds a shared lock to the table and does not use multiple-version data consistency reading techniques. This also guarantees the correctness of recovery and replication.

How to turn off this lock: the Innodb_locks_unsafe_for_binlog is set to "on" and InnoDB does not lock the soruce_tab. However, this generated binary log file Recovery database does not conform to the application logic, and if replication causes the master-slave database to be inconsistent.

if the application must use this SQL to implement the business logic, and do not want to have an impact on the source table concurrent Update can take the following three kinds of measures

One is to set the value of Innodb_locaks_unsafe_for_binlog to "on" to force MySQL to use multiple versions of data consistency, but at the cost of it, you may not be able to properly recover or replicate data with Binlog. It is therefore not recommended to use this approach.

The second is by using the "select* from Source_tab ... into outfile" and "load datainfile ..." statements combined to implement, in this way MySQL will not lock source_tab.

The third is the use of Binlog format based on rows and replication based on row data. 1. Consistency and lock differences of INNODB at different isolation levels

	Read uncommited	Read commited	REPEATABLE READ	Serializable
Select	Equal	None locks	None locks	Consisten Read/none Locks	Share locks
Range	None locks	None locks	Consisten Read/none Locks	Share Next-key
Update	Equal	Exclusive locks	Exclusive locks	Exclusive locks	Exclusive locks
Range	Exclusive locks Next Key	Exclusive locks Next Key	Exclusive locks Next Key	Exclusive locks Next Key
Replace	No conflict keys	Exclusive locks	Exclusive locks	Exclusive locks	Exclusive locks
There is a conflict key	Exclusive locks Next Key	Exclusive locks Next Key	Exclusive locks Next Key	Exclusive locks Next Key
Delete	Equal	Exclusive locks	Exclusive locks	Exclusive locks	Exclusive locks
Range	Exclusive locks Next Key	Exclusive locks Next Key	Exclusive locks Next Key	Exclusive locks Next Key
Insert	N/A	Exclusive locks	Exclusive locks	Exclusive locks	Exclusive locks
Select ... from.. Lock Share mode	Equal	Share locks	Share locks	Share locks	Share locks
Range	Share locks	Share locks	Share Next-key	Share Next-key
SELECT * From ... for update	Equal	Exclusive locks	Exclusive locks	Exclusive locks	Exclusive locks
Range	Exclusive locks Next Key	Exclusive locks Next Key	Exclusive locks Next Key	Exclusive locks Next Key
Insert INTO ... Select ... (Original table)	Innodb_locks_unsafe_for_binlog=off	Share Next-key	Share Next-key	Share Next-key	Share Next-key
Innodb_locks_unsafe_for_binlog=on	None locks	Consisten Read/none Locks	Consisten Read/none Locks	Share Next-key
CREATE TABLE ... select ... (Original table)	Innodb_locks_unsafe_for_binlog=off	Share Next-key	Share Next-key	Share Next-key	Share Next-key
Innodb_locks_unsafe_for_binlog=on	None locks	Consisten Read/none Locks	Consisten Read/none Locks	Share Next-key

2. When to use table locks

For the first ODB table of the inn, row-level locks should be used in most cases because transaction and row locks are often the reason why we choose inoodb tables.

The first: transactions require a large number of new or full data, the table is relatively large, if the use of the default row lock, not only this transaction is inefficient, but also can cause other transactions long time lock wait and lock conflict. In this case, you can consider using table locks to increase the execution speed of the transaction.

Second: The transaction involves many high tables, more complex, it is likely to cause deadlock, resulting in a large number of transaction rollback. This situation allows you to consider locking the tables involved in a transaction at once, thereby avoiding deadlocks and reducing the cost of the database due to transaction rollback.

Of course, these two transactions can not be too much, or should consider the use of MyISAM table.

InnoDB Use table locks to note two points:

1. Although locktables can be used to add table-level locks to InnoDB, it must be stated that table locks are not managed by the InnoDB storage engine but are owned by the MySQL server, only when autocommit=0, innodb_table_locks= 1 o'clock InnoDB just know MySQL plus table lock, MySQL can also feel innodb whether added row lock. Or there will be a deadlock.

2. In the use of locktables to InnoDB table lock to note that the autocommit set to 0, otherwise MySQL will not lock the table, do not use unlock table to release the table lock before the transaction ends. Because the Unlock table commits the transaction, a commit or rollback cannot release the tables and locks with lock tables, and you must release the table lock using Unlcok tables. 3. Deadlock

In InnoDB, when a single SQL component transaction is coarse, the lock is acquired progressively, which determines the likelihood of a deadlock in the InnoDB.

After a deadlock occurs, the InnoDB is generally automatically detected and a transaction is released, the lock is returned, another transaction gets the lock, and the transaction continues to complete.

However, in the case of an external lock or a table lock, InnoDB does not automatically detect the deadlock completely, which needs to be resolved by setting the lock wait timeout parameter innodb_lock_wait_timeout. This parameter is used to set the lock wait timeout time. Several methods of avoiding deadlock

1. In the application, if the program does not pass access to multiple tables, should be as far as possible to agree to access the table in the same order, which can greatly reduce the chance of deadlock.

2. In the process of processing data in bulk, if the implementation of the data ordering, to ensure that each thread in a fixed order to process records, can also greatly reduce the likelihood of deadlock.

3. In a transaction, if you want to update records, you should directly request a sufficient level of locks, that is, exclusive locks, but not the first to apply for shared locks, and then when sharing the time to apply for exclusive locks.

4. Under the Repeatable-read isolation level, if two threads record the same condition with a select ... for update plus exclusive lock, two threads will lock successfully if no record is met for that condition. If all two threads do this, a deadlock will occur. In this case, the isolation level is changed to read committed, which can be avoided.

5. When the isolation domain is read committed, if two threads first execute select ... for update, determine if there is a match, and if not, insert the record. In this case, if a duplicate row cannot be inserted because of a primary key, the second thread will be repeatedly faulted for the primary key after the first thread is successfully inserted, but the second thread has acquired an exclusive lock and a deadlock condition occurs if a third thread requests an exclusive lock. In this case, you can do the insert operation directly, then catch the primary key repeat exception, or when you encounter a primary key error, always perform a rollback release to acquire an exclusive lock.

While the deadlock is greatly reduced by measures such as the related and SQL optimizations described above, deadlocks are difficult to avoid entirely. You can determine the cause of the last deadlock by show ENGINE INNODB STATUS command.

mysql>show engine InnoDB status\g;

 

....

2015-08-25 16:24:29 7fb07b24a700

(1) TRANSACTION:

TRANSACTION 146929, ACTIVE 137 sec Startingindex Read

MySQL tables in use 1, locked 1

Lock wait 4 lock struct (s), heap size 1248,202 row lock (s)

MySQL thread ID 8, OS thread handle0x7fb07b28b700, query ID 2818824 localhost root updating

Update actor Set last_name= ' MONROE T ' whereactor_id=178

(1) Waiting for this LOCK to begranted:

Record LOCKS Space ID 236 page No 3 n bits272 index ' PRIMARY ' of table ' Sakila '. ' Actor ' Trx ID 146929 lock_mode X locksrec But not gap waiting

Record lock, heap no 179 physical record:n_fields 6; Compact format; Info bits 0

0:len 2; Hex 00b2; ASC;;

1:len 6; Hex 0000000229ac; ASC);;

2:len 7; Hex c60000020808dc; ASC;;

3:len 4; Hex 4c495341; ASC LISA;;

4:len 6; Hex 4d4f4e524f45; ASC MONROE;;

5:len 4; Hex 43f23ed9; ASC C >;;

(2) TRANSACTION:

TRANSACTION 146928, ACTIVE 170 sec Startingindex Read

MySQL tables in use 1, locked 1

4 lock struct (s), heap size 1248, rowlock (s)

MySQL thread ID 7, OS thread handle 0x7fb07b24a700,query ID 2818825 localhost root updating

Update actor Set last_name= ' MONROE T ' whereactor_id=178

(2) holds the LOCK (S):

Record LOCKS Space ID 236 page No 3 n bits272 index ' PRIMARY ' of table ' Sakila '. ' Actor ' Trx ID 146928 Lock Mode S

Record lock, heap No 1 physical record:n_fields 1; Compact format; Info bits 0

0:len 8; Hex 73757072656d756d; ASC supremum;;

.......