Deadlock found when trying to get lock and timeout

  • (2 Pages)
  • +
  • 1
  • 2

27 Replies - 1879 Views - Last Post: 29 August 2015 - 01:06 AM Rate Topic: -----

#1 DocNet   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 11-October 12

Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 04:12 AM

Hey, i will crazy with this deadlocks.

In my error log cron sends me this errors:

Lock wait timeout exceeded; try restarting transaction
Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction


Not each time, but very often sends me this error and when it happends data is not inserted in tables.

When i check
SHOW OPEN TABLES, i see that table what is always in_use is my map table, and inside log i see code line where is deadlock, and that is getMap() function.

$hteam = $this->getMap($fcountries,toShort($match->home->attributes()->name));	
$ateam = $this->getMap($fcountries,toShort($match->away->attributes()->name));	



function getMap($country,$data){
	//echo $data . '<br>';
	//$this->PDO->exec("LOCK TABLES map READ");
	//$this->PDO->exec("UNLOCK TABLES");
	$fdata = $data;
	$isregion = false;
	$countries = array();
	if((strtolower($country) == 'southamerica') or (strtolower($country) == 'conmebol')){
		$countries = $this->conmebolarray;
		$isregion = true;
	}
	elseif((strtolower($country) == 'europe') or (strtolower($country) == 'uefa')){
		$countries = $this->euroarray;
		$isregion = true;
	}
	elseif((strtolower($country) == 'asia') or (strtolower($country) == 'afc')){
		$countries = $this->afcarray;
		$isregion = true;
	}
	elseif((strtolower($country) == 'africa') or (strtolower($country) == 'caf')){
		$countries = $this->cafarray;
		$isregion = true;
	}
	elseif((strtolower($country) == 'northandcentralamerica') or (strtolower($country) == 'concacaf')){
		$countries = $this->conarray;
		$isregion = true;
	}
	else{
		$countries = $country;
		$isregion = false;
	}
	$query = $this->PDO->prepare("CALL GetMap(:data, :country)");
	$res = '';
	if($isregion){
	  foreach($countries as $loopcountry){
		  $query->closeCursor();
		  $query->bindParam(':data', $fdata);
		  $query->bindParam(':country', $loopcountry);
		  $query->execute();
		  $res = $query->fetchColumn();
	  }
	}
	else{
		$query->closeCursor();
		$query->bindParam(':data', $fdata);
		$query->bindParam(':country', $country);
		$query->execute();
		$res = $query->fetchColumn();
	}
	//echo 'DATA: ' . $data. ' RES: '.$res . '<br>';
	if($res!=''){
		$query->closeCursor();
		$sql = "UPDATE map SET Country = :country WHERE Base = :base";
		$q2 = $this->PDO->prepare($sql);
		$q2->bindParam(':country', $country);
		$q2->bindParam(':base', $res);
		$q2->execute();
		$this->PDO->exec("UNLOCK TABLES");
		echo $res . '<br>';
		return $res;
	}
	else
	{
		$query->closeCursor();
		$this->fillMap($country,$fdata);
		return $fdata;
	}
}



Detailed code:

function sendOddsToDb(){	
try{
	$this->conn->connect();
	$this->PDO->beginTransaction();
	$countries = $this->OddCountries; 
	$stmt = $this->PDO->prepare($this->insOdds);
	//$dbempty = $this->countRows('odds');
	//$this->clrLeagues();					
	foreach($countries as $country){
	 $path = "{$this->base}/feeds/odds/{$country}odds.xml";
	 $odds_feed = $this->parser->parseFile($path);
	 
	 $fupddate = date('Y-m-d H:i:s',strtotime($odds_feed->attributes()->updated));
	 $newcountry = '';
	 if(isset($odds_feed->league)){
	 
	 $newcountry = $odds_feed->league->attributes()->country;
	 $fcountries = $this->filterCountries($newcountry);
	  echo $fcountries . '<br>';
	  $sql = "select UpdatedDate from odds where Country = '{$fcountries}' order by UpdatedDate desc limit 1";
	  $res = $this->PDO->query($sql);
	  $dbupd = date('Y-m-d H:i:s',strtotime($res->fetchColumn()));
	  
	  if(!$this->sameUpdated($fupddate,$dbupd)){
		foreach($odds_feed->league as $league){ //za svaku ligu u fidu
			$subid = $league->attributes()->sub_id;
			
			$sportname = '';
			$sportid = 0;
			if($odds_feed->attributes()->sport == 'soccer' || $odds_feed->attributes()->sport == 'football'){
				$sportname = 'football';
				$sportid = 1;
			}
			else{
				$sportname = NULL;
			}
			$lg2 = '';
			$lg = $league->attributes()->name;
			$ctr = $league->attributes()->country.':';
			if(!contains(strtolower($lg),strtolower($ctr))){
				$lg2 = ucfirst($ctr) . ' ' . ucfirst($lg);  
			}
			else{
				$lg2 = $league->attributes()->name;
			}
				
			$flg = $this->getMap2($lg2);
			$this->sendLeaguesToDb($league->attributes()->id, $flg ,$fcountries,$sportid,$subid,'tipgin');
			foreach($league->match as $match){ 
				
				$dbdate = date('Y-m-d',strtotime($match->attributes()->date));
				$updateddate = date('Y-m-d H:i:s',strtotime($odds_feed->attributes()->updated));
				$dbtime = date('H:i:s', strtotime($match->attributes()->time));
				
				
				$datetime = date('Y-m-d H:i:s',strtotime($dbdate . $dbtime));	
				//echo $hteam . ' ' . $ateam;
				$fixid = 0;
				if($match->attributes()->id == 0 || $match->attributes()->id == ''){
					if($match->attributes()->alternate_id == 0 || $match->attributes()->id == ''){
						$fixid = $match->attributes()->alternate_id_2;
					}
					else{
						$fixid = $match->attributes()->alternate_id;
					}
				}
				else{
					$fixid = $match->attributes()->id;
				}		
				//$m = $hteam . ' ' . $ateam;
				//var_dump('MATCH: ' . $m . ' HTEAM: ' . mb_detect_encoding($hteam). ' ATEAM: ' . mb_detect_encoding($ateam));
				if($match->odds != NULL or $match->odds != ''){			
				foreach ($match->odds as $odds) { 
				$hteam = $this->getMap($fcountries,toShort($match->home->attributes()->name));	
				$ateam = $this->getMap($fcountries,toShort($match->away->attributes()->name));	
					//var_dump($odd);
					if($odds->type != NULL or $odds->type != ''){
					foreach ($odds->type as $type) { 
						if($type->bookmaker != NULL or $type->bookmaker != ''){
						foreach ($type->bookmaker as $bookmaker) { 
							//$oldbook = ;
							if($bookmaker->attributes()->name != 'BetClick'){
							if($bookmaker->attributes()->name != 'Bodog'){
							if($bookmaker->attributes()->name != 'YouWin'){
							$homeodds = NULL;
							$drawodds = NULL;
							$awayodds = NULL;
							$numodds = NULL;
							$dbhandicap = NULL;
							$id2 = '0';
							$typename = $this->getMap2($type->attributes()->name);
							
							$fbooks = $this->filterBooks($bookmaker->attributes()->name);	
								
							if($typename == 'Over/Under' || $typename == 'Over/Under 1st Half' || $typename == 'Over/Under 2nd Half'){

								foreach ($bookmaker->total as $total) { //daj kvote za svaki mec
								
								$numodds = $total->attributes()->name;
								foreach($total->odd as $odd){
								switch ($odd->attributes()->name) {
									case 'Over':
										$homeodds = $odd->attributes()->value;
										break;
									case 'Under':
										$awayodds = $odd->attributes()->value;
										break;
								}
								
								$id2 = $numodds;
								}
							
							//echo "HOME TEAM: {$hteam} <BR>";
							//echo "AWAY TEAM: {$ateam} <BR>";
							/*
							echo 'IF ID: ' . $match->attributes()->alternate_id_2 . $league->attributes()->country . " ODDS: {$homeodds} {$drawodds} {$awayodds} TYPE: {$typename}  BOOK: {$fbooks}  ID2: {$id2}" . "<br>";
				*/
							$this->execOdds($stmt, $fixid, $updateddate,
							$typename, $fcountries,$league->attributes()->name,
							$league->attributes()->id, $subid, $league->attributes()->cup,$sportname,
							$hteam,$match->home->attributes()->id,
							$ateam,$match->away->attributes()->id, $datetime, $dbdate,
							$dbtime, $match->attributes()->status, $numodds,$homeodds,$drawodds,
							$awayodds, $fbooks, $bookmaker->attributes()->id, $dbhandicap, $id2);
							}
							}
							
							else if($typename == 'Handicap' || $typename == '3Way Handicap'){
							$homeodds = array();
							$drawodds = array();
							$awayodds = array();
							$arrhandicap = array();
							$fhandicap = '';
							
							foreach ($bookmaker->handicap as $handicap) { //daj kvote za svaki mec 
							if (strpos($handicap->attributes()->name,'+') !== false) {
							$fhandicap = str_replace('+','',(string)$handicap->attributes()->name);								
							}else{
							$fhandicap = (string)$handicap->attributes()->name;
							}
							$newhandi = '';
							foreach($handicap->odd as $odd){
									//$odd = $handicap->odd;
									switch ($odd->attributes()->name) {
									case '1':
										$newhandi = (string)$fhandicap;
										$homeodds[(string)$newhandi] = 
										(string)$odd->attributes()->value;
										break;
									case 'X':
										$newhandi = $fhandicap;
										$drawodds[(string)$newhandi] = 
										(string)$odd->attributes()->value;
										break;	
									case '2':
										if (strpos($fhandicap,'-') !== false) { //if negative 
											$newhandi = str_replace('-','',(string)$fhandicap); //to positive
											//echo 'NEGATIVE - OLD: ' . $fhandicap . ' NEW: '.$newhandi . '<br>';
										}
										elseif($fhandicap=='0'){
											$newhandi = '0';
										}
										else{ //if positive
											$newhandi = '-' . (string)$fhandicap; //to negative
											//echo 'POSITIVE - OLD: ' . $fhandicap . ' NEW: '.$newhandi . '<br>';
										}
										$awayodds[(string)$newhandi] = (string)$odd->attributes()->value; 
										break;
									}
									$arrhandicap[] = (string)$newhandi;
								}
							}
							//print_r($arrhandicap);
							//$arrhandicap[] = sort($dbhandicap);
							$inhand = '';
							$varhome = '';
							$vardraw = '';
							$varaway = '';
							
							foreach($arrhandicap as $hand){
							$inhand = (string)$hand;
							if(isset($hand)){
							//echo $typename. ': ' . $hand . '<br>';
							
							if((array_key_exists($inhand,$homeodds)) && (isset($homeodds[$inhand]))){
								$varhome = $homeodds[$inhand];
							}
							
							if((array_key_exists($inhand,$awayodds)) && (isset($awayodds[$inhand]))){
								$varaway = $awayodds[$inhand];
							}
							if((array_key_exists($inhand,$drawodds)) && (isset($drawodds[$inhand]))){
								$vardraw = $drawodds[$inhand];
							}
							
							$this->execOdds($stmt, $fixid, $updateddate,
							$typename, $fcountries,$league->attributes()->name,
							$league->attributes()->id, $subid, $league->attributes()->cup,$sportname,
							$hteam,$match->home->attributes()->id,
							$ateam,$match->away->attributes()->id, $datetime, $dbdate,
							$dbtime, $match->attributes()->status, $numodds,$varhome,
							$vardraw,$varaway, $fbooks, 
							$bookmaker->attributes()->id, $hand, $hand);
							/*echo 'ELSEIF ID: ' . $match->attributes()->id . $league->attributes()->country . " HOMEODDS: {$varhome}  DRAW: {$vardraw}  AWAYODDS: {$varaway} TYPE: {$typename}  BOOK: {$fbooks}  ID2: {$inhand}" . "<br>";													
							*/}
							}
							}
							else{
							foreach ($bookmaker->odd as $odd) { //daj kvote za svaki mec
								if($typename == 'Home/Away'){
									$typename = 'Draw No Bet';
								}
								switch ($odd->attributes()->name) {
									case '1':
									case '1X':
									case 'Yes':
										$homeodds = $odd->attributes()->value;
										break;
									case 'X':
									case 'X2':
										$drawodds = $odd->attributes()->value;
										break;
									case '2':
									case '12':
									case 'No':
										$awayodds = $odd->attributes()->value;
										break;
								}
								
								$id2 = 0;
								//echo 'ID: ' . $match->attributes()->alternate_id_2 . $league->attributes()->country . " ODDS: {$homeodds} {$drawodds} {$awayodds} TYPE: {$typename}  BOOK: {$fbooks}  ID2: {$id2}" . "<br>";
							  }
							/*
							echo "ELSE HOME TEAM: {$hteam} <BR>";
							echo "ELSE AWAY TEAM: {$ateam} <BR>";	
							*/
							$this->execOdds($stmt, $fixid, $updateddate,
							$typename, $fcountries,$league->attributes()->name,
							$league->attributes()->id, $subid, $league->attributes()->cup,$sportname,
							$hteam,$match->home->attributes()->id,
							$ateam,$match->away->attributes()->id, $datetime, $dbdate,
							$dbtime, $match->attributes()->status, $numodds,$homeodds,$drawodds,
							$awayodds, $fbooks, $bookmaker->attributes()->id, $dbhandicap, $id2);
							
							}
							}
						}
						}
						}
						}
						}
					}
					}
					}
				}
			}
		}
		//echo $country.'<br>';
	}
	}
	$this->PDO->commit(); //SAVE
	$this->conn->close();
	}
	catch(Exception $e){
echo $e;
	}
}

private function execOdds($stmt, $matchid, $upddate, $type, $country, $league, $leagueid, $subid, $cup, $sport, $hteam, $hteamid, $ateam, $ateamid, $datetime, $date, $time, $status, $numodds, $homeodds, $drawodds, $awayodds, $book, $bookid, $handicap, $id2){
	
	$event = $hteam . ' - ' . $ateam;
	
	$fcountry = $country;
	$fleague = $league;
	
	$stmt->bindParam(':fixturematchid', $matchid);$stmt->bindParam(':updateddate',$upddate);$stmt->bindParam(':type',$type);
	$stmt->bindParam(':country',$fcountry);$stmt->bindParam(':league',$fleague);$stmt->bindParam(':leagueid',$leagueid);
	$stmt->bindParam(':subid',$subid);
	$stmt->bindParam(':cup',$cup);$stmt->bindParam(':eventname',$event);$stmt->bindParam(':sportname',$sport);
	$stmt->bindParam(':hometeam', $hteam);$stmt->bindParam(':hometeamid', $hteamid);$stmt->bindParam(':awayteam', $ateam);
	$stmt->bindParam(':awayteamid', $ateamid);$stmt->bindParam(':datetime',$datetime);$stmt->bindParam(':date',$date);
	$stmt->bindParam(':time',$time);$stmt->bindParam(':status',$status);$stmt->bindParam(':numodds', $numodds);
	$stmt->bindParam(':homeodds', $homeodds);$stmt->bindParam(':drawodds',$drawodds);$stmt->bindParam(':awayodds',$awayodds);
	$stmt->bindParam(':bookmaker',$book);$stmt->bindParam(':bookmakerid',$bookid);$stmt->bindParam(':handicap', $handicap);
	$stmt->bindParam(':id2', $id2);
	$stmt->execute(); //EXEC*/
}




Is This A Good Question/Topic? 0
  • +

Replies To: Deadlock found when trying to get lock and timeout

#2 CTphpnwb   User is online

  • D.I.C Lover
  • member icon

Reputation: 3798
  • View blog
  • Posts: 13,755
  • Joined: 08-August 08

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 06:52 AM

First things first: this is WRONG: $sql = "select UpdatedDate from odds where Country = '{$fcountries}' order by UpdatedDate desc limit 1";. NEVER put data in a query. Always use prepared statements.

As for locking, $this->conn->connect(); likely creates a new MySQL session, so if you're calling sendOddsToDb() more than once in a PHP session you'll get the error you describe.
Was This Post Helpful? 0
  • +
  • -

#3 DocNet   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 11-October 12

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 08:24 AM

View PostCTphpnwb, on 27 August 2015 - 06:52 AM, said:

First things first: this is WRONG: $sql = "select UpdatedDate from odds where Country = '{$fcountries}' order by UpdatedDate desc limit 1";. NEVER put data in a query. Always use prepared statements.

As for locking, $this->conn->connect(); likely creates a new MySQL session, so if you're calling sendOddsToDb() more than once in a PHP session you'll get the error you describe.


Ok i will prepare this select statement, what is your suggestion for this->conn->connect()?
To create new Connection for this function?
Please check getMap function, inside function sendOddsToDb() i call getMap() and using same connection, is solution to add new connection for getMap()? That table is locked, sendOddsToDb sends data to odd table, getMap select/insert data to map table using same connection and PDO from class. All functions using same instance $this->conn->connect() what is declared inside contruct, i see now that is wrong.
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb   User is online

  • D.I.C Lover
  • member icon

Reputation: 3798
  • View blog
  • Posts: 13,755
  • Joined: 08-August 08

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 08:54 AM

View PostDocNet, on 27 August 2015 - 11:24 AM, said:

Please check getMap function, inside function sendOddsToDb() i call getMap() and using same connection, is solution to add new connection for getMap()? That table is locked, sendOddsToDb sends data to odd table, getMap select/insert data to map table using same connection and PDO from class. All functions using same instance $this->conn->connect() what is declared inside contruct, i see now that is wrong.

So then you're saying that $this->conn->connect() is called more than once per PHP session? If so, after the first time the table is locked the next connect will create a new PDO instance that will not have access to the locked table.
Was This Post Helpful? 0
  • +
  • -

#5 ArtificialSoldier   User is online

  • D.I.C Lover
  • member icon

Reputation: 2061
  • View blog
  • Posts: 6,318
  • Joined: 15-January 14

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 09:39 AM

Also, there's not much point in creating a prepared statement and then immediately closing the cursor. The cursor isn't open.
Was This Post Helpful? 0
  • +
  • -

#6 DocNet   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 11-October 12

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 10:15 AM

View PostArtificialSoldier, on 27 August 2015 - 09:39 AM, said:

Also, there's not much point in creating a prepared statement and then immediately closing the cursor. The cursor isn't open.


When i remove closeCursor i get buffered error..
Was This Post Helpful? 0
  • +
  • -

#7 ArtificialSoldier   User is online

  • D.I.C Lover
  • member icon

Reputation: 2061
  • View blog
  • Posts: 6,318
  • Joined: 15-January 14

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 10:20 AM

What specifically is the error message?
Was This Post Helpful? 0
  • +
  • -

#8 DocNet   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 11-October 12

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 10:21 AM

View PostCTphpnwb, on 27 August 2015 - 08:54 AM, said:

View PostDocNet, on 27 August 2015 - 11:24 AM, said:

Please check getMap function, inside function sendOddsToDb() i call getMap() and using same connection, is solution to add new connection for getMap()? That table is locked, sendOddsToDb sends data to odd table, getMap select/insert data to map table using same connection and PDO from class. All functions using same instance $this->conn->connect() what is declared inside contruct, i see now that is wrong.

So then you're saying that $this->conn->connect() is called more than once per PHP session? If so, after the first time the table is locked the next connect will create a new PDO instance that will not have access to the locked table.


Yes, i removed all calls for each function, so i add:
$con = new Connect(); 
$PDO = $con->PDO;
//prepared stuffs
$con->close();


but again i get deadlock errors..
Was This Post Helpful? 0
  • +
  • -

#9 DocNet   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 11-October 12

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 10:26 AM

View PostArtificialSoldier, on 27 August 2015 - 10:20 AM, said:

What specifically is the error message?

this

Quote

exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in /home/27425-18594.cloudwaysapps.com/jcjdkdtaur/public_html/php/class.xmltodb.php:558 Stack trace: #0 /home/27425-18594.cloudwaysapps.com/jcjdkdtaur/public_html/php/class.xmltodb.php(558): PDOStatement->execute() #1 /home/27425-18594.cloudwaysapps.com/jcjdkdtaur/public_html/php/class.xmltodb.php(239): XMLtoDB->getMap(Object(SimpleXMLElement), 'Admira') #2 /home/27425-18594.cloudwaysapps.com/jcjdkdtaur/public_html/cron5min.php(24): XMLtoDB->sendOddsToDb() #3 {main}


when i add setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,false or true) same error

This post has been edited by DocNet: 27 August 2015 - 10:29 AM

Was This Post Helpful? 0
  • +
  • -

#10 DocNet   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 11-October 12

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 10:41 AM

Only way that i can avoid deadlocks are by killing all processes.
Like this:

$result = $PDO->query("SHOW FULL PROCESSLIST");
$result->setFetchMode(PDO::FETCH_ASSOC);

while ($row=$result->fetch()) {  
$process_id=$row["Id"];
  echo $process_id;
  if ($row["Time"] >= 0 ) {
    $sql="KILL $process_id";
    $PDO->query($sql);
  }
}




What you think that i run in each cron this code?
I have cron5min.php, cron10min.php, cron60min.php, cronday.php
before run kill all processes.
Was This Post Helpful? 0
  • +
  • -

#11 ArtificialSoldier   User is online

  • D.I.C Lover
  • member icon

Reputation: 2061
  • View blog
  • Posts: 6,318
  • Joined: 15-January 14

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 10:46 AM

Quote

Only way that i can avoid deadlocks are by killing all processes.

Not really, you can also avoid the query that is locking the table in the first place, or making sure it finishes before doing something else with that table. You need to find out what that is. If you print out the process list while the table is locked it should show you which one has the lock. It sounds like you have multiple connections and multiple things going on at once and it's hard to suggest a specific solution from only looking at small pieces of the whole thing.

Quote

What you think that i run in each cron this code?
I have cron5min.php, cron10min.php, cron60min.php, cronday.php
before run kill all processes.

I think that periodically killing all MySQL processes is a terrible idea that will lead to data corruption in no time.
Was This Post Helpful? 0
  • +
  • -

#12 DocNet   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 11-October 12

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 11:17 AM

I say that map table is almost always in_use (1,2,3,4-11 as value, intersting thing before i add new instance of connection to each function i had max value 4, now i see 11).
I am almost sure that locked table is map, because error exception point at line where is getMap function $q2->execute() exactly.


currently
show open tables:
jcjdkdtaur	Get1x2	0	0
jcjdkdtaur	topscorer	0	0
jcjdkdtaur	GetOverUnder	0	0
jcjdkdtaur	team	0	0
jcjdkdtaur	historicmatches	0	0
jcjdkdtaur	sport	0	0
jcjdkdtaur	GetCurrentDay	0	0
jcjdkdtaur	teamleaguestanding	0	0
jcjdkdtaur	fixtures	0	0
jcjdkdtaur	livescore	0	0
jcjdkdtaur	map2	0	0
jcjdkdtaur	map	11	0
jcjdkdtaur	Get3WayHandicap	0	0
jcjdkdtaur	GetHandicap	0	0
jcjdkdtaur	GetHA	0	0
jcjdkdtaur	GetTopOdds	0	0
jcjdkdtaur	league	0	0
jcjdkdtaur	Sport	0	0
jcjdkdtaur	odds	0	0
jcjdkdtaur	topodds	0	0
jcjdkdtaur	utf8	0	0
jcjdkdtaur	baby	0	0
jcjdkdtaur	Player	0	0


show processlist:

471567	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	641		
471568	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	3069		
471569	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	3069		
471570	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	3069		
475843	jcjdkdtaur	localhost:56883		Sleep	54		
475983	jcjdkdtaur	localhost:56884		Sleep	54		
476535	jcjdkdtaur	localhost:56885	jcjdkdtaur	Query	0		show processlist
476950	jcjdkdtaur	localhost:56886	jcjdkdtaur	Sleep	117		
528272	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	776		
528277	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2828		
528278	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2828		
528279	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2828		
599255	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	715		
599264	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2589		
599267	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2589		
599268	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2589		
655926	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	841		
655927	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2348		
655928	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2348		
655929	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2348		
710319	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	180		
710341	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2108		
710342	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2108		
710343	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	2108		
768599	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	40		
768601	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1868		
768602	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1868		
768603	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1868		
831256	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	16		
831267	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1628		
831280	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1628		
831281	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1628		
890926	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	347		
890928	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1387		
890929	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1387		
890930	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1387		
947897	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	434		
947907	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1148		
947908	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1148		
947909	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1148		
979119	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	253		
979172	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1044		
979191	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1044		
979192	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	1044		
1020094	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	127		
1020104	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	908		
1020105	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	908		
1020106	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	908		
1072483	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	11		
1072497	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	667		
1072498	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	667		
1072499	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	667		
1144710	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	11		
1144747	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	425		
1144748	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	425		
1144749	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	425		
1205412	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	56		
1205416	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	186		
1205423	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	186		
1205424	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	186		
1245676	jcjdkdtaur	localhost	jcjdkdtaur	Query	0	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Turkey U17' COLLATE 'utf8_general
1246090	jcjdkdtaur	localhost	jcjdkdtaur	Query	1	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Vancouver Whitecaps' COLLATE 'utf
1246790	jcjdkdtaur	localhost	jcjdkdtaur	Query	1	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Serbia' COLLATE 'utf8_general_ci'
1246933	jcjdkdtaur	localhost	jcjdkdtaur	Query	0	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Belarus U21' COLLATE 'utf8_genera
1247448	jcjdkdtaur	localhost	jcjdkdtaur	Query	0	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Zilina' COLLATE 'utf8_general_ci'
1247858	jcjdkdtaur	localhost	jcjdkdtaur	Query	0	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Canada' COLLATE 'utf8_general_ci'
1248101	jcjdkdtaur	localhost	jcjdkdtaur	Query	1	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Canada' COLLATE 'utf8_general_ci'
1248330	jcjdkdtaur	localhost	jcjdkdtaur	Query	1	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Rabotnicki' COLLATE 'utf8_general
1249315	jcjdkdtaur	localhost	jcjdkdtaur	Query	1	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Antigua and Barbuda' COLLATE 'utf
1249512	jcjdkdtaur	localhost	jcjdkdtaur	Query	1	Writing to net	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Sibenik' COLLATE 'utf8_general_ci
1249525	jcjdkdtaur	localhost	jcjdkdtaur	Query	1	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Neftochimic Burgas' COLLATE 'utf8
1249538	jcjdkdtaur	localhost	jcjdkdtaur	Query	1	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Slutsk' COLLATE 'utf8_general_ci'
1249551	jcjdkdtaur	localhost	jcjdkdtaur	Query	0	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Marbella' COLLATE 'utf8_general_c
1249576	jcjdkdtaur	localhost	jcjdkdtaur	Query	0	Sending data	SELECT Base FROM map WHERE 
 (map.Base =  NAME_CONST('p_team',_utf8'Bayer Leverkusen' COLLATE 'utf8_g
1249583	jcjdkdtaur	localhost	jcjdkdtaur	Sleep	0		



For InnoDb status i have not rights to check.

If you think that you may help me, i will paste full code from cron5min.php? about(1200 lines code)

This post has been edited by andrewsw: 27 August 2015 - 11:33 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#13 DocNet   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 11-October 12

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 11:23 AM

I already googled about this exception, and mostly answer is to lock current table and write to temp table if i understand

like this

LOCK map READ, WRITE temp
//queries
UNLOCK TABLES

is this maybe only solution or is there something easier?

there is also some suggestions about process ordering

like

definition of deadlock:
process1 : use A wait B
process2 : use B wait A

so some order has example but just for delete statement, like
delete from table where col in (select col from table)


how can i order update, insert queries?

This post has been edited by DocNet: 27 August 2015 - 11:34 AM

Was This Post Helpful? 0
  • +
  • -

#14 DocNet   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 11-October 12

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 11:40 AM

Hmm i lie when sayed that i get again same deadlock exception, that was old exception, i clear log after i check before hour, and now i see just 'MySQL server has gone away' exception for some other function. There is no deadlocks, maybe we did, but we will see after a while, i will clear table now, and i will check time what is needed for insert.
Was This Post Helpful? 0
  • +
  • -

#15 ArtificialSoldier   User is online

  • D.I.C Lover
  • member icon

Reputation: 2061
  • View blog
  • Posts: 6,318
  • Joined: 15-January 14

Re: Deadlock found when trying to get lock and timeout

Posted 27 August 2015 - 12:23 PM

That "server has gone away" message is fairly useless, I've never found a good solution for it. I see it most often in times of very high usage so I chalk it up to periodic high traffic where one user got kicked out.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2