Welcome Guest, Not a member yet? Register   Sign In
GET_LOCK and database sessions causing issues with AWS serverless database
#1

Hi,

We look after a busy CodeIgniter site which is setup with autoscaling on AWS. Everything generally works great but we recently attempted to move to their new serverless database offering which would allow the database to scale in the same way as the webservers.

All seemed like it would work, however after going live the database wasn't able to scale because of locks on the database which weren't being released. We're using database sessions currently and I can see that this means GET_LOCK is called on page load, however I can't see where RELEASE_LOCK is being called and I wonder if this is why we're getting the database so 'locked up' ?

I've read online about how AJAX can cause issues with database sessions and locking, and this could be related, but I wonder if there's anything we can do about this as currently we're unable to switch to the serverless database.

We could go back to sessions in files, but for other reasons database sessions are preferrable.

Any advice would be appreciated.

Thanks,
Tom
Reply
#2

You can read more about in the manual:
https://codeigniter.com/user_guide/libra...oncurrency

If you want to trigger RELEASE_LOCK you will need to close it manually. $this->session->close();
https://dev.mysql.com/doc/refman/5.7/en/...tions.html

"A lock obtained with GET_LOCK() is released explicitly by executing RELEASE_LOCK() or implicitly when your session terminates (either normally or abnormally). Locks obtained with GET_LOCK() are not released when transactions commit or roll back."

GET_LOCK are set to timeout after 300 seconds. And will be released after that time, if not being used.
Reply
#3

Thanks for the reply. It sounds then that perhaps because we had the session timeout set to 0 this could be causing the locks to not be released. or perhaps database stored sessions and AWS serverless just don't work together.
Reply
#4

Another thing to remember is that the sessions were only written for MySQL and PostgreSQL Databases.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#5

@tom18a That timeout are hardcoded in the system folder, so in case you didn't modify that code you have 300 seconds.
Reply
#6

(This post was last modified: 01-16-2020, 01:10 PM by blasto333.)

You might want to consider using redis (AWS elasticache) for sessions. It would require you install phpredis and then create application/libraries/MY_Session.php as below

You might want to tweak some of the settings for your environment. Then you don't need to rely on the mysql locking (or mysql in general) for sessions.

Redis can be setup mutli-az also so if it fails the other one will take over.

PHP Code:
<?php
class MY_Session extends CI_Session 
{
 public function 
__construct(array $params = array())
 {
 
$CI =& get_instance();
 
 
//Settings for redis sessions
 
ini_set('session.save_handler','redis');
 
$redis_host = isset($_SERVER['HTTP_HOST']) && $_SERVER['HTTP_HOST'] == 'localhost' '127.0.0.1:6379' 'REDIS-URL.com:6379';
 
ini_set('session.save_path','tcp://'.$redis_host.'?prefix=APPSESSION:');
 
ini_set('redis.session.locking_enabled',1);
 
 
//How long should the lock live (in seconds)? Defaults to: value of max_execution_time.
 
ini_set('redis.session.lock_expire',60);
 
 
//How long to wait between attempts to acquire lock, in microseconds (µs)?. Defaults to: 2000
 
ini_set('redis.session.lock_wait_time',10000);
 
 
//Maximum number of times to retry (-1 means infinite). Defaults to: 10
 
ini_set('redis.session.lock_retries',150);
 
 
// No sessions under CLI
 
if (is_cli())
 {
 
log_message('debug''Session: Initialization under CLI aborted.');
 return;
 }
 elseif ((bool) 
ini_get('session.auto_start'))
 {
 
log_message('error''Session: session.auto_start is enabled in php.ini. Aborting.');
 return;
 }
 
 
// Configuration ...
 
$this->_configure($params);
 
$this->_config['_sid_regexp'] = $this->_sid_regexp;

 
// Sanitize the cookie, because apparently PHP doesn't do that for userspace handlers
 
if (isset($_COOKIE[$this->_config['cookie_name']])
 && (
 ! 
is_string($_COOKIE[$this->_config['cookie_name']])
 OR ! 
preg_match('#\A'.$this->_sid_regexp.'\z#'$_COOKIE[$this->_config['cookie_name']])
 )
 )
 {
 unset(
$_COOKIE[$this->_config['cookie_name']]);
 }

 
session_start();

 
// Is session ID auto-regeneration configured? (ignoring ajax requests)
 
if ((empty($_SERVER['HTTP_X_REQUESTED_WITH']) OR strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) !== 'xmlhttprequest')
 && (
$regenerate_time config_item('sess_time_to_update')) > 0
 
)
 {
 if ( ! isset(
$_SESSION['__ci_last_regenerate']))
 {
 
$_SESSION['__ci_last_regenerate'] = time();
 }
 elseif (
$_SESSION['__ci_last_regenerate'] < (time() - $regenerate_time))
 {
 
$this->sess_regenerate((bool) config_item('sess_regenerate_destroy'));
 }
 }
 
// Another work-around ... PHP doesn't seem to send the session cookie
 // unless it is being currently created or regenerated
 
elseif (isset($_COOKIE[$this->_config['cookie_name']]) && $_COOKIE[$this->_config['cookie_name']] === session_id())
 {
 
setcookie(
 
$this->_config['cookie_name'],
 
session_id(),
 (empty(
$this->_config['cookie_lifetime']) ? time() + $this->_config['cookie_lifetime']),
 
$this->_config['cookie_path'],
 
$this->_config['cookie_domain'],
 
$this->_config['cookie_secure'],
 
TRUE
 
);
 }

 
$this->_ci_init_vars();
 
log_message('info'"Session: Class initialized using native php."); 
 }
 



Also if you want to migrate session from database to redis add this to the bottom of index.php while you are still using database sessions. Let it run like this for a day or so to move your sessions to redis. Then when you make the switch you don't log anyone out.


PHP Code:
//Keep storing data in redis sessions
$redis = new Redis();
$redis_host = isset($_SERVER['HTTP_HOST']) && $_SERVER['HTTP_HOST'] == 'localhost' '127.0.0.1' 'REDIS-URL.com:6379';
$redis->connect($redis_host6379);
$redis->set("APPSESSION:".session_id(), session_encode(),(int)ini_get('session.gc_maxlifetime')); 
Reply
#7

Thanks! That's really helpful. A colleague also just suggested using AWS elasticache, so I think this might be the route to take and you're examples will be very useful. I don't suppose anyone else on here has experience using CodeIgniter in conjunction with AWS serverless? I'm assuming it's the sessions that are causing the problem but I don't know for sure.
Reply
#8

I haven’t used serverless aurora but what error are you getting?
Reply
#9

I think the error relates to serverless being able to scale. So when traffic comes in, it just isn't scaling. AWS support have said the reason for this is locked tables or long running connections. Which made me think that perhaps the sessions were the problem, but that's just a guess. They have said that serverless just isn't going to work for us but haven't been any more helpful than that in working out why exactly.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB