I am getting occasional deadlock errors with CDbHttpSession and am having trouble diagnosing the problem.
I’ve added a custom error handler to record ‘SHOW ENGINE INNODB STATUS’.
The conflicting locks queries are:
DELETE FROM `tbl_session` WHERE expire<'1391467792'
and
UPDATE `tbl_session` SET `id`='b04gso5930mssafq6c3m7hrfj4' WHERE
id='tuql5l5iru9cfim5u9qt81ld40'
It is deleting sessions with expiry older than NOW.
1391467792 = February 3, 2014 at 2:49:52 PM PST
It looks to me like CDbHttpSession openSession and regenerateID are causing the deadlock?
public function openSession($savePath,$sessionName)
......
$db->createCommand()->delete($this->sessionTableName,'expire<:expire',array(':expire'=>time()));
......
}
public function regenerateID($deleteOldSession=false)
......
if($deleteOldSession)
$db->createCommand()->update($this->sessionTableName,array(
'id'=>$newID
),'id=:oldID',array(':oldID'=>$oldID));
.....
}
Yii version 1.1.13
2014/02/03 14:49:52
[system.db.CDbCommand]
CDbCommand::execute() failed: SQLSTATE[40001]: Serialization failure:
1213 Deadlock found when trying to get lock; try restarting transaction.
The SQL statement executed was: UPDATE `tbl_session` SET `id`=:id WHERE id=:oldID.
Stack trace:
#0 ../yii-1.1.13/framework/yiilite.php(9312):
CDbCommand->execute(Array)
#1 ../yii-1.1.13/framework/web/CDbHttpSession.php(112):
CDbCommand->update('tbl_session', Array, 'id=:oldID', Array)
#2 ../yii-1.1.13/framework/yiilite.php(4213):
CDbHttpSession->regenerateID(true)
#3 ../yii-1.1.13/framework/yiilite.php(3956):
CWebUser->changeIdentity('1274', 'usersemail@domain.com', Array)
#4
../protected/modules/user/models/UserLogin.php(57):
CWebUser->login(Object(UserIdentity), 0)
#5
../yii-1.1.13/framework/validators/CInlineValidator.php(42):
UserLogin->authenticate('password', Array)
#6 ../yii-1.1.13/framework/yiilite.php(9623):
CInlineValidator->validateAttribute(Object(UserLogin), 'password')
#7 ../yii-1.1.13/framework/yiilite.php(6633):
CValidator->validate(Object(UserLogin), NULL)
#8
../protected/modules/user/controllers/LoginController.php(34):
CModel->validate()
#9 ../yii-1.1.13/framework/yiilite.php(3891):
LoginController->actionLogin()
.....
REQUEST_URI=/user/login
------------------------
LATEST DETECTED DEADLOCK
------------------------
140203 14:49:52
*** (1) TRANSACTION:
TRANSACTION 10FB67A3, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log
entries 1
MySQL thread id 7460001, OS thread handle 0x4a1d3940, query id
230752337 localhost dbUserName Updating
UPDATE `tbl_session` SET `id`='b04gso5930mssafq6c3m7hrfj4' WHERE
id='tuql5l5iru9cfim5u9qt81ld40'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1124 n bits 152 index `PRIMARY` of
table `dbTableName`.`tbl_session` trx id 10FB67A3 lock_mode X
locks gap before rec insert intention waiting
Record lock, heap no 40 PHYSICAL RECORD: n_fields 5; compact format;
info bits 0
0: len 30; hex
6230347364736463676c7162746438376e63326270667666343220202020; asc
b04sdsdcglqbtd87nc2bpfvf42 ; (total 32 bytes);
1: len 6; hex 00000f79076c; asc y l;;
2: len 7; hex fe00000bad0110; asc ;;
3: len 4; hex d2fb14fa; asc ;;
4: len 30; hex
323134343833306666383235623135613538313738666561383839643237; asc
2144830ff825b15a58178fea889d27; (total 93 bytes);
*** (2) TRANSACTION:
TRANSACTION 10FB67A1, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
1067 lock struct(s), heap size 113080, 69260 row lock(s)
MySQL thread id 7460002, OS thread handle 0x49a35940, query id
230752335 localhost dbUserName updating
DELETE FROM `tbl_session` WHERE expire<'1391467792'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1124 n bits 152 index `PRIMARY` of
table `dbTableName`.`tbl_session` trx id 10FB67A1 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format;
info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
main.php
'session' => array(
'class' => 'system.web.CDbHttpSession',
'timeout' => (86400 * 30), //set timeout to 30 days
'connectionID' => 'db',
'sessionTableName' => 'tbl_session',
'sessionName' => 'sess',
'savePath' => '/tmp',
'cookieMode' => 'allow',
'cookieParams' => array(
'path' => '/',
'domain' => '.mydomain.com',
'httpOnly' => true,
),
),