MySQL Database Tables Lock during huge Update

[n3mesis125]

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>";
}

?>

[Mchl]

Try InnoDB?

[fenway]

Could you post the EXPLAINs of the equivalent select statements?