Yii Framework Forum: Cdbhttpsession Deadlock Errors - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Cdbhttpsession Deadlock Errors Rate Topic: -----

#1 User is offline   freedom55 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 14-June 11

Posted 03 February 2014 - 07:35 PM

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,
	        	),
        ),

0

#2 User is offline   freedom55 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 14-June 11

Posted 07 February 2014 - 01:35 PM

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??
0

#3 User is offline   freedom55 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 14-June 11

Posted 23 March 2014 - 12:21 AM

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]
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users