n3mesis125 Posted August 26, 2008 Share Posted August 26, 2008 Hey Folks, I'm having an issue with a webapp that my employees use for work. They track all of the work that they do in our webapp php/mysql system. However at the end of their shift, there maybe anywhere from 40-50 ppl logging out at the same time, around 3:50-4:00pm ATL. The issue is that when this happens each day, I do a query to update their login/logout time in the mysql database. HOwever, with about that many ppl doing it at once, my logins table locks and its causes a huge lag and sometimes the mysql server hangs until they are slowly all updated. I've pasted the code below for the query I'm using, is there any better way to make this query better or another way I should be going about storing the login/logout so it doesn't cause this issue? Sometimes it just causes the table not to even update that employees stamp out time and i lose their logout time for that day which isn't good late on when i run adherence reports. <?php include('../includes/sessions.inc'); $site = $_SESSION['site']; if (isset($_POST['sel_eshift'])) { require_once('../Connections/rogers.php'); mysql_select_db($database_rogers, $rogers); $setUser = $_SESSION['userID']; $shiftstart = $_SESSION['shift_date']; $servOff = $_SESSION['offset']; $setTime = time() - $servOff; $reason = $_POST['sel_eshift']; $sesstotal = $setTime - $_SESSION['startstamp']; $now = date("Y-m-d", $setTime); if ($site != "moncton") { $tbl = "logins_os"; $sess = ""; } else { $tbl = "logins"; $sess = ", `session_total`='".$sesstotal."'"; } $sql = "UPDATE `".$tbl."` SET `finish_time`='".$setTime."'".$sess." WHERE `username`='".$setUser."' AND `start_date`='".$shiftstart."'"; $res = mysql_query($sql); $sql2 = "UPDATE `online` SET `status`='0' WHERE `username`='".$setUser."'"; $res2 = mysql_query($sql2); session_destroy(); print "<script language='javascript'>\n"; print "top.close();\n"; print "</script>"; } ?> Link to comment https://forums.phpfreaks.com/topic/121438-mysql-database-tables-lock-during-huge-update/ Share on other sites More sharing options...
Mchl Posted August 26, 2008 Share Posted August 26, 2008 Try InnoDB? Link to comment https://forums.phpfreaks.com/topic/121438-mysql-database-tables-lock-during-huge-update/#findComment-626227 Share on other sites More sharing options...
fenway Posted August 26, 2008 Share Posted August 26, 2008 Could you post the EXPLAINs of the equivalent select statements? Link to comment https://forums.phpfreaks.com/topic/121438-mysql-database-tables-lock-during-huge-update/#findComment-626233 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.