mysql超时设置

interactive_timeout和wait_timeout的区别默认是28800,单位秒,即8个小时1> interactive_timeout针对交互式连接,wait_timeout针对非交互式连接。所谓的交互式连接,即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。 说得直白一点,通过mysql客户端连接数据库是交互式连接,通过jdbc连接数据库是非交互式连接。 2> 在连接启动的时候,根据连接的类型,来确认会话变量wait_timeout的值是继承于全局变量wait_timeout,还是interactive_timeout

* innodb_lock_wait_timeout:innodb的dml操作的行级锁的等待时间
* lock_wait_timeout:数据结构ddl操作的锁的等待时间

innodb_lock_wait_timeoutThe length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionlock_wait_timeoutThis variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements.当出现死锁以后,有两种策略:

* 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
* 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on,表示开启这个逻辑。


上一篇:mysql GTID


下一篇:latin1字符集的数据转换为utf8字符集