如何在PHP + MySQL中正确实现自定义会话持久性? [英] How to properly implement a custom session persister in PHP + MySQL?
问题描述
我正在尝试在PHP + MySQL中实现自定义会话持久性.大部分内容都很简单-创建数据库表，执行读/写功能，调用session_set_save_hander()等.甚至还有几本教程为您提供示例实现.但是，所有这些教程都以某种方式方便地忽略了有关会话持久性的一个小细节-锁定.现在，这才是真正的乐趣开始！
I'm trying to implement a custom session persister in PHP + MySQL. Most of the stuff is trivial - create your DB table, make your read/write functions, call session_set_save_hander(), etc. There are even several tutorials out there that offer sample implementations for you. But somehow all these tutorials have conveniently overlooked one tiny detail about session persisters - locking. And now that's where the real fun starts!
我研究了PHP的 session_mysql PECL扩展的实现.它使用MySQL的函数get_lock()和release_lock().看起来不错，但我不喜欢它的执行方式.该锁在 read 函数中获取，并在 write 函数中释放.但是，如果写函数从未被调用怎么办?如果脚本以某种方式崩溃，但MySQL连接保持打开状态(由于池化或其他原因)怎么办?还是如果脚本进入致命的僵局该怎么办?
I looked at the implementation of session_mysql PECL extension of PHP. That uses MySQL's functions get_lock() and release_lock(). Seems nice, but I don't like the way it's doing it. The lock is acquired in the read function, and released in the write function. But what if the write function never gets called? What if the script somehow crashes, but the MySQL connection stays open (due to pooling or something)? Or what if it the script enters a deadly deadlock?
我只是有问题，其中脚本打开了一个会话，然后尝试通过NFS共享flock()文件，而另一台计算机(承载该文件)也做同样的事情.结果是flock() -over-NFS调用在每次调用中阻塞脚本约30秒钟.它处于20次迭代的循环中！由于这是一个外部操作，因此PHP的脚本超时不适用，并且每次访问该脚本时，会话均被锁定10分钟以上.而且，幸运的是，这是每5秒由AJAX聊天箱轮询一次的脚本...主要展示片.
I just had a problem where a script opened a session and then tried to flock() a file over an NFS share, while the other computer (that hosted the file) was also doing the same thing. The result was that the flock()-over-NFS call was blocking the script for about 30 seconds on each call. And it was in a loop of 20 iterations! Since that was an external operation, PHP's script timeouts didn't apply, and the session got locked for over 10 minutes every time this script was accessed. And, as luck would have it, this was the script that got polled by an AJAX shoutbox every 5 seconds... Major showstopper.
我已经对如何更好地实现它有了一些想法，但是我真的很想听听其他人的建议.我在PHP方面没有太多的经验，无法知道阴影中隐约可见哪些细微的边缘情况，有一天有可能危及整件事.
I already have some ideas on how to implement it in a better way, but I would really like to hear what other people suggest. I haven't had that much experience with PHP to know what subtle edge cases loom in the shadows which could one day jeopardize the whole thing.
已添加:
好的，似乎没有人建议.好吧，这是我的主意.我想对这可能会出错的地方提出意见.
OK, seems that nobody has anything to suggest. OK then, here's my idea. I'd like some opinon on where this could go wrong.
- 使用InnoDB存储引擎创建会话表.即使在群集情况下，这也应确保对行进行适当的锁定.该表应具有 ID ，数据， LastAccessTime ， LockTime ， LockID .我在这里省略了数据类型，因为它们非常直接地遵循需要存储在其中的数据. ID 将是PHP会话的ID. 数据当然将包含会话数据. LastAccessTime 是一个时间戳，将在每次读/写操作时更新，GC将使用该时间戳删除旧会话. LockTime 将是会话中获取的最后一个锁的时间戳，而 LockID 将是该锁的GUID.
- 当请求 read 操作时，将执行以下操作:
- Create a session table with InnoDB storage engine. This should ensure some proper locking of rows even under clustered scenarios. The table should have the columns ID, Data, LastAccessTime, LockTime, LockID. I'm omitting the datatypes here because they follow quite directly from the data that needs to be stored in them. The ID will be the ID of the PHP session. Data will of course contain the session data. LastAccessTime will be a timestamp which will be updated on each read/write operation and will be used by GC to delete old sessions. LockTime will be a timestamp of the last lock that was acquired on the session, and LockID will be a GUID of the lock.
- When a read operation is requested, there will be the following actions taken:
- 执行
INSERT IGNORE INTO sessions (id, data, lastaccesstime, locktime, lockid) values ($sessid, null, now(), null, null);-如果会话行不存在，它将创建会话行，但是如果已经存在，则不执行任何操作； - 在变量$ guid中生成随机锁定ID；
- 执行
UPDATE sessions SET (lastaccesstime, locktime, lockid) values (now(), now(), $guid) where id=$sessid and (lockid is null or locktime < date_add(now(), INTERVAL -30 seconds));-这是一个原子操作，它将在会话行上获得锁(如果未锁定或锁已过期)，或者什么也不做. - 用
mysql_affected_rows()检查是否已获得锁定.如果获得了，请继续.如果不是，请每0.5秒重新尝试一次该操作.如果在40秒内仍未获得锁定，请引发异常.
- Execute
INSERT IGNORE INTO sessions (id, data, lastaccesstime, locktime, lockid) values ($sessid, null, now(), null, null);- this will create the session row if it is not there, but do nothing if it is already present; - Generate a random lock id in the variable $guid;
- Execute
UPDATE sessions SET (lastaccesstime, locktime, lockid) values (now(), now(), $guid) where id=$sessid and (lockid is null or locktime < date_add(now(), INTERVAL -30 seconds));- this is an atomic operation which will either obtain a lock on the session row (if it's not locked or the lock is expired), or will do nothing. - Check with
mysql_affected_rows()if the lock was obtained or not. If it was obtained - proceed. If not - re-attempt the operation every 0.5 seconds. If in 40 seconds the lock is still not obtained, throw an exception.
UPDATE sessions SET (lastaccesstime, data, locktime, lockid) values (now(), $data, null, null) where id=$sessid and lockid=$guid;这是另一个原子操作，它将使用新数据更新会话行，并在仍然具有锁的情况下删除该锁，但不执行任何操作如果锁已经被拿走了.gc操作时，只需删除lastaccesstime太旧的所有行.
UPDATE sessions SET (lastaccesstime, data, locktime, lockid) values (now(), $data, null, null) where id=$sessid and lockid=$guid; This is another atomic operation which will update the session row with the new data and remove the lock if it still has the lock, but do nothing if the lock was already taken away.gc operation is requested, simply delete all rows with lastaccesstime too old.有人能看到这个缺陷吗?
Can anyone see flaws with this?
推荐答案
好.答案会更长一些-耐心点！ 1)我要写的内容都是基于最近几天所做的实验.可能有些旋钮/设置/内部操作我可能不知道.如果发现错误/或不同意，请大喊大叫！
Ok. The answer is going to be a bit longer - so patience! 1) Whatever I am going to write is based on the experiments I have done over last couple of days. There may be some knobs/settings/inner working I may not be aware of. If you spot mistakes/ or do not agree then please shout!
2)首先澄清-何时读取和写入会议数据
2) First clarification - WHEN SESSION DATA is READ and WRITTEN
即使脚本中有多个$ _SESSION读取，会话数据也将仅被读取一次.从会话读取是基于每个脚本的.此外，数据获取是基于session_id而非键进行的.
The session data is going to be read exactly once even if you have multiple $_SESSION reads inside your script. The read from session is a on a per script basis. Moreover the data fetch happens based on the session_id and not keys.
2)第二次澄清-总是在脚本结尾处写信
2) Second clarification - WRITE ALWAYS CALLED AT END OF SCRIPT
A)始终触发对会话save_set_handler的写入，即使对于仅从会话读取"并且从未执行任何写入操作的脚本也是如此. B)在脚本末尾或显式调用session_write_close时，写入操作仅触发一次.同样，写操作基于session_id而不是键
A) The write to session save_set_handler is always fired, even for scripts that only "read" from session and never do any writes. B) The write is only fired once, at the end of the script or if you explicitly call session_write_close. Again, the write is based on session_id and not keys
3)第三次澄清:为什么我们需要锁定
3) Third Clarification : WHY WE NEED Locking
- 这大惊小怪的是什么?
- 我们真的需要锁定会话吗?
- 我们真的需要包裹读和写的大锁吗?
解释大惊小怪
脚本1
- 1:$ x = S_SESSION ["X"];
- 2:睡眠(20);
- 3:if($ x == 1){
- 4://做点事情
- 5:$ _ SESSION ["X"] = 3；
- 6:}
- 4:退出；
脚本2
- 1:$ x = $ _SESSION ["X"];
- 2:if($ x == 1){$ _SESSION ["X"] = 2; }
- 3:退出；
不一致之处在于脚本1正在根据会话变量(line:3)值执行某项操作，而该值已在脚本1已经运行时被另一个脚本更改了.这是一个基本示例，但它说明了这一点.您正在基于不再为真的事物进行决策的事实.
The inconsistency is that script 1 is doing something based on a session variable (line:3) value that has changed in by another script while script-1 was already running. This is a skeleton example but it illustrates the point. The fact that you are taking decisions based on something that is no longer TRUE.
当您使用PHP时，默认的会话锁定(请求级别锁定)script2将在第1行被阻止，因为它无法从文件中读取脚本1在第1行开始读取的内容.因此，对会话数据的请求被序列化.当script2读取值时，可以保证读取新值.
when you are using PHP default session locking (Request Level locking) script2 will block on line 1 because it cannot read from the file that script 1 started reading at line1. So the requests to session data are serialized. When script2 reads a value, it is guaranteed to read the new value.
说明4:PHP会话同步与可变同步不同
Clarification 4: PHP SESSION SYNCHRONIZATION IS DIFFERENT FROM VARIABLE SYNCHRONIZATION
很多人都在谈论PHP会话同步，就好像它是变量同步一样，一旦您覆盖了变量值，对内存位置的写操作就会发生，并且任何脚本中的下一个读取操作都会获取新值.正如我们从澄清#1中看到的-这是不正确的.该脚本使用整个脚本中从脚本开始处读取的值，即使某些其他脚本更改了值，正在运行的脚本也要等到下一次刷新时才能知道新值.这是非常重要的一点.
Lot of people talk about PHP session synchronization as if it is like a variable synchronization, the write to memory location happening as soon as you overwrite variable value and the next read in any script will fetch the new value. As we see from CLARIFICATION #1 - That is not true. The script uses the values read at the start of the script throughout the script and even if some other script has changed the values, the running script will not know about new values till next refresh. This is a very important point.
此外，请记住，即使使用PHP大锁定，会话中的值也会更改.诸如先完成的脚本将覆盖值"之类的说法不是很准确.价值变化还不错，我们追求的是前后矛盾，也就是说，在我不知情的情况下，价值不应变化.
Also, keep in mind that values in session changes even with PHP big locking. Saying things like, "script that finishes first will overwrite value" is not very accurate. Value change is not bad, what we are after is inconsistency, namely, it should not change without my knowledge.
澄清5:我们真的需要大锁吗?
CLARIFICATION 5: Do we REALLY NEED BIG LOCK?
现在，我们真的需要Big Lock(请求级别)吗?就像在数据库隔离中一样，答案是它取决于您想做的事情.使用$ _SESSION的默认实现，恕我直言，只有大的锁才有意义.如果要使用我在整个脚本开始时读取的值，那么只有大的锁才有意义.如果我将$ _SESSION实现更改为始终"获取新鲜"值，则不需要大锁.
Now, do we really need Big Lock (request level)? The answer, as in the case of DB isolation, is that it depends on how you want to do things. With the default implementation of $_SESSION, IMHO, only the big lock makes sense. If I am going to the use the value that I read at the beginning throughout my script then only the big lock makes sense. If I change the $_SESSION implementation to "always" fetch "fresh" value then you do not need BIG LOCK.
假设我们实现了会话数据版本控制方案，例如对象版本控制.现在，脚本2写入将成功，因为脚本1尚未达到写入点. script-2写入会话存储并将版本增加1.现在，当脚本1尝试写入会话时，它将失败(第5行)-尽管可行，但我认为这不是理想的选择.
Suppose we implement a session data versioning scheme like object versioning. Now, script 2 write will succeed because script-1 has not come to write point yet. script-2 writes to session store and increments version by 1. Now, when script 1 tries to write to session, it will fail (line:5) - I do not think this is desirable, though doable.
==================================
===================================
从(1)和(2)中可以看出，无论脚本多么复杂，对会话进行X次读取和Y次写入，
From (1) and (2), it follows that no matter how complicated your script, with X reads and Y writes to session,
- 会话处理程序的read()和write()方法仅被调用一次
- 他们总是被称为
现在，网络上有一些自定义的PHP会话处理程序，它们试图进行可变"级别的锁定等.我仍在尝试找出其中的一些.但是，我不赞成使用复杂的方案.
Now, there are custom PHP session handlers on net that try to do a "variable"-level locking etc. I am still trying to figure some of them. However I am not in favor of complex schemes.
假设带有$ _SESSION的PHP脚本应该可以在Web页面上运行并以毫秒为单位进行处理，那么我认为额外的复杂性是不值得的. 像Peter Zaitsev在这里提到的，选择写入后进行提交更新的选择应该可以解决问题.
Assuming that PHP scripts with $_SESSION are supposed to be serving web pages and are processed in milli-seconds, I do not think the additional complexity is worth it. Like Peter Zaitsev mentions here, a select for update with commit after write should do the trick.
在这里，我包括了为实现锁定而编写的代码.使用一些竞赛模拟"脚本对其进行测试将是很好的.我相信它应该起作用.我在网上找不到很多正确的实现.如果您能指出错误，那就太好了.我是用裸露的mysqli完成的.
Here I am including the code that I wrote to implement locking. It would be nice to test it with some "Race simulation" scripts. I believe it should work. There are not many correct implementations I found on net. It would be good if you can point out the mistakes. I did this with bare mysqli.
<?php
namespace com\indigloo\core {
use \com\indigloo\Configuration as Config;
use \com\indigloo\Logger as Logger;
/*
* @todo - examine row level locking between read() and write()
*
*/
class MySQLSession {
private $mysqli ;
function __construct() {
}
function open($path,$name) {
$this->mysqli = new \mysqli(Config::getInstance()->get_value("mysql.host"),
Config::getInstance()->get_value("mysql.user"),
Config::getInstance()->get_value("mysql.password"),
Config::getInstance()->get_value("mysql.database"));
if (mysqli_connect_errno ()) {
trigger_error(mysqli_connect_error(), E_USER_ERROR);
exit(1);
}
//remove old sessions
$this->gc(1440);
return TRUE ;
}
function close() {
$this->mysqli->close();
$this->mysqli = null;
return TRUE ;
}
function read($sessionId) {
Logger::getInstance()->info("reading session data from DB");
//start Tx
$this->mysqli->query("START TRANSACTION");
$sql = " select data from sc_php_session where session_id = '%s' for update ";
$sessionId = $this->mysqli->real_escape_string($sessionId);
$sql = sprintf($sql,$sessionId);
$result = $this->mysqli->query($sql);
$data = '' ;
if ($result) {
$record = $result->fetch_array(MYSQLI_ASSOC);
$data = $record['data'];
}
$result->free();
return $data ;
}
function write($sessionId,$data) {
$sessionId = $this->mysqli->real_escape_string($sessionId);
$data = $this->mysqli->real_escape_string($data);
$sql = "REPLACE INTO sc_php_session(session_id,data,updated_on) VALUES('%s', '%s', now())" ;
$sql = sprintf($sql,$sessionId, $data);
$stmt = $this->mysqli->prepare($sql);
if ($stmt) {
$stmt->execute();
$stmt->close();
} else {
trigger_error($this->mysqli->error, E_USER_ERROR);
}
//end Tx
$this->mysqli->query("COMMIT");
Logger::getInstance()->info("wrote session data to DB");
}
function destroy($sessionId) {
$sessionId = $this->mysqli->real_escape_string($sessionId);
$sql = "DELETE FROM sc_php_session WHERE session_id = '%s' ";
$sql = sprintf($sql,$sessionId);
$stmt = $this->mysqli->prepare($sql);
if ($stmt) {
$stmt->execute();
$stmt->close();
} else {
trigger_error($this->mysqli->error, E_USER_ERROR);
}
}
/*
* @param $age - number in seconds set by session.gc_maxlifetime value
* default is 1440 or 24 mins.
*
*/
function gc($age) {
$sql = "DELETE FROM sc_php_session WHERE updated_on < (now() - INTERVAL %d SECOND) ";
$sql = sprintf($sql,$age);
$stmt = $this->mysqli->prepare($sql);
if ($stmt) {
$stmt->execute();
$stmt->close();
} else {
trigger_error($this->mysqli->error, E_USER_ERROR);
}
}
}
}
?>
要注册对象会话处理程序，
To register the object session Handler,
$sessionHandler = new \com\indigloo\core\MySQLSession();
session_set_save_handler(array($sessionHandler,"open"),
array($sessionHandler,"close"),
array($sessionHandler,"read"),
array($sessionHandler,"write"),
array($sessionHandler,"destroy"),
array($sessionHandler,"gc"));
ini_set('session_use_cookies',1);
//Defaults to 1 (enabled) since PHP 5.3.0
//no passing of sessionID in URL
ini_set('session.use_only_cookies',1);
// the following prevents unexpected effects
// when using objects as save handlers
// @see http://php.net/manual/en/function.session-set-save-handler.php
register_shutdown_function('session_write_close');
session_start();
这是使用PDO完成的另一个版本.这将检查sessionId是否存在，并进行更新或插入.我还从open()中删除了gc函数，因为它在每次加载页面时不必要地触发了SQL查询.可以通过cron脚本轻松完成陈旧的会话清除.如果您使用的是PHP 5.x，则应为要使用的版本.让我知道您是否发现任何错误！
Here is another version done with PDO. This one checks for existence of sessionId and does update or Insert. I have also removed the gc function from open() as it unnecessarily fires a SQL query on each page load. The stale session cleanup can easily be done via a cron script. This should be the version to use if you are on PHP 5.x. Let me know if you find any bugs!
=======================================
=========================================
namespace com\indigloo\core {
use \com\indigloo\Configuration as Config;
use \com\indigloo\mysql\PDOWrapper;
use \com\indigloo\Logger as Logger;
/*
* custom session handler to store PHP session data into mysql DB
* we use a -select for update- row leve lock
*
*/
class MySQLSession {
private $dbh ;
function __construct() {
}
function open($path,$name) {
$this->dbh = PDOWrapper::getHandle();
return TRUE ;
}
function close() {
$this->dbh = null;
return TRUE ;
}
function read($sessionId) {
//start Tx
$this->dbh->beginTransaction();
$sql = " select data from sc_php_session where session_id = :session_id for update ";
$stmt = $this->dbh->prepare($sql);
$stmt->bindParam(":session_id",$sessionId, \PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetch(\PDO::FETCH_ASSOC);
$data = '' ;
if($result) {
$data = $result['data'];
}
return $data ;
}
function write($sessionId,$data) {
$sql = " select count(session_id) as total from sc_php_session where session_id = :session_id" ;
$stmt = $this->dbh->prepare($sql);
$stmt->bindParam(":session_id",$sessionId, \PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetch(\PDO::FETCH_ASSOC);
$total = $result['total'];
if($total > 0) {
//existing session
$sql2 = " update sc_php_session set data = :data, updated_on = now() where session_id = :session_id" ;
} else {
$sql2 = "insert INTO sc_php_session(session_id,data,updated_on) VALUES(:session_id, :data, now())" ;
}
$stmt2 = $this->dbh->prepare($sql2);
$stmt2->bindParam(":session_id",$sessionId, \PDO::PARAM_STR);
$stmt2->bindParam(":data",$data, \PDO::PARAM_STR);
$stmt2->execute();
//end Tx
$this->dbh->commit();
}
/*
* destroy is called via session_destroy
* However it is better to clear the stale sessions via a CRON script
*/
function destroy($sessionId) {
$sql = "DELETE FROM sc_php_session WHERE session_id = :session_id ";
$stmt = $this->dbh->prepare($sql);
$stmt->bindParam(":session_id",$sessionId, \PDO::PARAM_STR);
$stmt->execute();
}
/*
* @param $age - number in seconds set by session.gc_maxlifetime value
* default is 1440 or 24 mins.
*
*/
function gc($age) {
$sql = "DELETE FROM sc_php_session WHERE updated_on < (now() - INTERVAL :age SECOND) ";
$stmt = $this->dbh->prepare($sql);
$stmt->bindParam(":age",$age, \PDO::PARAM_INT);
$stmt->execute();
}
}
}
?>
这篇关于如何在PHP + MySQL中正确实现自定义会话持久性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
