标签归档:事务

MySQL 事务与锁查看

information_schema.innodb_trx

trx_id					事务ID
trx_state				事务状态
trx_started				事务执行开始时间
trx_requested_lock_id			事务等待锁ID号(等待其它事务释放锁)
trx_wait_started			事务等待锁开始时间
trx_weight						
trx_mysql_thread_id			事务线程ID
trx_query				具体的SQL
trx_operation_state			事务当前操作状态
trx_tables_in_use			事务中有多少个表被使用
trx_tables_locked				
trx_lock_structs					
trx_lock_memory_bytes			事务锁住的内存大小(B)
trx_rows_locked				事务锁住的行数
trx_rows_modified			事务更改的行数
trx_concurrency_tickets			事务并发数
trx_isolation_level			事务隔离级别
trx_unique_checks			是否唯一性检查
trx_foreign_key_checks			是否外键检查
trx_last_foreign_key_error		最后的外键错误
trx_adaptive_hash_latched		
trx_adaptive_hash_timeout		

注:trx_started记录了事务开始的时间,如果过去了很长时间,可能是异常事务。trx_wait_started记录了等待时间,如果等待了很长时间,可能是异常事务。对应等待锁的事务,trx_query记录了具体的SQL语句。trx_mysql_thread_id可以定位到具体的线程(回话ID)

information_schema.innodb_locks

lock_id							锁ID
lock_trx_id						拥有锁的事务ID
lock_mode						锁模式
lock_type						锁类型
lock_table						被锁的表
lock_index						被锁的索引(类型)
lock_space						被锁的表空间号
lock_page						被锁的页号
lock_rec						被锁的记录号
lock_data						被锁的数据(对应索引编号,一般是ID号)

事务可以持有多个锁。锁类型有S和X。根据索引类型不同,lock_rec和lock_data可以定位行号。当开启一个事务,对相关行上锁,这个时候的锁不会出现在innodb_locks表中,只有相关的锁被其它事务等待时,产生了锁等待,才会把锁与等待的锁插入此表(换个说法就是此表是用来存放有依赖关系的锁的)

information_schema.innodb_lock_waits

requesting_trx_id					请求锁的事务ID
requested_lock_id					请求锁的锁ID
blocking_trx_id						当前拥有锁的事务ID
blocking_lock_id					当前拥有锁的锁ID
requesting_thd_id					请求锁的线程ID
blocking_thd_id						当前拥有锁的线程ID

记录依赖关系。请求锁等待持有锁。

1 当要查看有哪些线程时,直接运行show full processlist即可,这个命令动态列出当前的线程状态
2 当要查看有哪些事务时(比如检查有哪些事务长时间未结束),可以直接查看innodb_trx表,这个表中的trx_started记录了开始事务的时间。
3 当要查看是否有锁等待时,可以查看innodb_locks,只要有记录,就说明产生了锁等待,具体是哪个依赖哪个,需要查看innodb_lock_waits的关系。

一般来说,产生了锁等待,如果超时,事务会自动释放,但是如果事务开启了,单长时间没有结束,就应该去innodb_trx查看确认(从线程基本无法查看到已经开启了事务)。

获得导致行锁等待和行锁等待超时的会话:

select l.* from ( select 'Blocker' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, lw.requesting_thd_id Blockee_id, lw.requesting_trx_id Blockee_trx from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.blocking_lock_id union select 'Blockee' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, null, null from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.requested_lock_id) l order by role desc, trx_state desc;

对于复杂的多个会话相互行锁等待情况,建议先终止 Role 为 Blocker 且 trx_state 为 RUNNING 的会话;终止后再次检查,如果仍旧有行锁等待,再终止新结果中的 Role 为 Blocker 且 trx_state 为 RUNNING 的会话。

对于标识为 Blocker 的会话(持有锁阻塞其他会话的 DML 操作,导致行锁等待和行锁等待超时),确认业务可以接受其对应的事务回滚的情况下,可以将其终止。比如,可以通过 Kill 命令来今后会话终止。

PHP框架Phalcon 之 模型 事务处理

Transactions 事务
When a process performs multiple database operations, it is often that each step is completed successfully so that data integrity can be maintained. Transactions offer the ability to ensure that all database operations have been executed successfully before the data are committed to the database.

Transactions in Phalcon allow you to commit all operations if they have been executed successfully or rollback all operations if something went wrong.

–Manual Transactions 手动事务
If an application only uses one connection and the transactions aren’t very complex, a transaction can be created by just moving the current connection to transaction mode, doing a rollback or commit if the operation is successfully or not:
如果应用只使用一个链接,事务不会非常复杂,通过移动当前链接的事务模式就可以创建一个事务,接下来就是提交或回滚:

<?php

class RobotsController extends Phalcon\Mvc\Controller
{
    public function saveAction()
    {
        $this->db->begin();

        $robot = new Robots();

        $robot->name = "WALL·E";
        $robot->created_at = date("Y-m-d");
        if ($robot->save() == false) {
            $this->db->rollback();
            return;
        }

        $robotPart = new RobotParts();
        $robotPart->robots_id = $robot->id;
        $robotPart->type = "head";
        if ($robotPart->save() == false) {
            $this->db->rollback();
            return;
        }

        $this->db->commit();
    }
}

–Implicit Transactions 隐式事务
Existing relationships can be used to store records and their related instances, this kind of operation implicitly creates a transaction to ensure that data are correctly stored:

<?php

$robotPart = new RobotParts();
$robotPart->type = "head";

$robot = new Robots();
$robot->name = "WALL·E";
$robot->created_at = date("Y-m-d");
$robot->robotPart = $robotPart;

$robot->save(); //Creates an implicit transaction to store both records

(涉及到多张表,默认悄悄开启事务)

–Isolated Transactions 原子事务
Isolated transactions are executed in a new connection ensuring that all the generated SQL, virtual foreign key checks and business rules are isolated from the main connection. This kind of transaction requires a transaction manager that globally manages each transaction created ensuring that they are correctly rolled back/committed before ending the request:
原子事务在一个新链接中执行用来确保所有产生的SQL,虚拟外键检测和业务逻辑是原子性的。这种类型的事务要求一个事务管理器来全局管理每个事务的创建以确保它们在请求结束(这个请求是只请求一个事务吧?)前正确回滚和提交:

<?php

use Phalcon\Mvc\Model\Transaction\Manager as TxManager,
    Phalcon\Mvc\Model\Transaction\Failed as TxFailed;

try {

    //Create a transaction manager
    $manager = new TxManager();

    // Request a transaction
    $transaction = $manager->get();

    $robot = new Robots();
    $robot->setTransaction($transaction);
    $robot->name = "WALL·E";
    $robot->created_at = date("Y-m-d");
    if ($robot->save() == false) {
        $transaction->rollback("Cannot save robot");
    }

    $robotPart = new RobotParts();
    $robotPart->setTransaction($transaction);
    $robotPart->robots_id = $robot->id;
    $robotPart->type = "head";
    if ($robotPart->save() == false) {
        $transaction->rollback("Cannot save robot part");
    }

    //Everything goes fine, let's commit the transaction
    $transaction->commit();

} catch(TxFailed $e) {
    echo "Failed, reason: ", $e->getMessage();
}

(这里引入了事务管理器,开启事务通过调用事务管理器的get方法,按照之前的说明,它是构建一个新链接,然后在它之上操作)

Transactions can be used to delete many records in a consistent way:

<?php

use Phalcon\Mvc\Model\Transaction\Manager as TxManager,
    Phalcon\Mvc\Model\Transaction\Failed as TxFailed;

try {

    //Create a transaction manager
    $manager = new TxManager();

    //Request a transaction
    $transaction = $manager->get();

    //Get the robots will be deleted
    foreach (Robots::find("type = 'mechanical'") as $robot) {
        $robot->setTransaction($transaction);
        if ($robot->delete() == false) {
            //Something goes wrong, we should to rollback the transaction
            foreach ($robot->getMessages() as $message) {
                $transaction->rollback($message->getMessage());
            }
        }
    }

    //Everything goes fine, let's commit the transaction
    $transaction->commit();

    echo "Robots were deleted successfully!";

} catch(TxFailed $e) {
    echo "Failed, reason: ", $e->getMessage();
}

Transactions are reused no matter where the transaction object is retrieved. A new transaction is generated only when a commit() or rollback() is performed. You can use the service container to create the global transaction manager for the entire application:
事务是重用的。一个新的事务仅在commit() or rollback()被执行时产生。你可以使用服务容器创建一个全局事务管理器。

<?php

$di->setShared('transactions', function(){
    return new \Phalcon\Mvc\Model\Transaction\Manager();
});

Then access it from a controller or view:

<?php

class ProductsController extends \Phalcon\Mvc\Controller
{

    public function saveAction()
    {

        //Obtain the TransactionsManager from the services container
        $manager = $this->di->getTransactions();

        //Or
        $manager = $this->transactions;

        //Request a transaction
        $transaction = $manager->get();

        //...
    }

}

While a transaction is active, the transaction manager will always return the same transaction across the application.
但是一个事务是激活的,事务管理在整个应用中将总是返回相同事务。
(Phalcon\DI\FactoryDefault是Phalcon MVC默认实现的DI,它会初始化一个事务管理器,因为可能产生多个事务,所以才需要管理)