Cdbhttpsession Deadlock Errors

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,

	        	),

        ),

Can anyone help with this? I could really use a hand.

Used to happen once a month in production. Now seems like it is happening once per day.

Is it a bug with CDbHttpSession not reissuing the transaction??

Still getting these dead lock errors with CWebUser.

[code][2014/03/22 22:15:52 [error] [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.

2014/03/22 22:15:52 [error] [exception.CDbException] exception

‘CDbException’ with message 'CDbCommand failed to execute the SQL

statement: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found

when trying to get lock; try restarting transaction’ in

/var/www/yii/yii-1.1.13/framework/yiilite.php:8893

Stack trace:

#0 /var/www/yii/yii-1.1.13/framework/yiilite.php(9312):

CDbCommand->execute(Array)

#1 /var/www/yii/yii-1.1.13/framework/web/CDbHttpSession.php(112):

CDbCommand->update(‘tbl_session’, Array, ‘id=:oldID’, Array)

#2 /var/www/yii/yii-1.1.13/framework/yiilite.php(4213):

CDbHttpSession->regenerateID(true)

#3 /var/www/yii/yii-1.1.13/framework/yiilite.php(3956):

CWebUser->changeIdentity(‘1’, ‘email@me…’, Array)

#4

xxxx.../modules/user/models/UserLogin.php(57):

CWebUser->login(Object(UserIdentity), 0)/code]

I have the same issue with yii cache "Deadlock found when trying to get lock" messages. I have tried adding an index to the expire column but with no luck.