1 Topic by XuMiX

  • XuMiX
  • Senior Member
  • Offline
  • From: Russia.Novosibirsk.ToStreet()
  • Registered: 2004-05-17
  • Posts: 130

Topic: DB deadlocks if using postgres

well, actually i don't know if it's a punbb bug, or smth mine...
so..lets begin far away: i've modified update_users_online() to let me make the current online users location page (this is my PunOnline mod)

function update_users_online()
{
    global $db, $pun_config, $pun_user;

    $now = time();

    $pathinfo = pathinfo($_SERVER['PHP_SELF']);
    $current_page = $pathinfo['basename'];
    $current_ip = get_remote_address();
    $current_page_full = $_SERVER['PHP_SELF'];

    if ($current_page == "viewforum.php" || $current_page == "viewtopic.php" || $current_page == "profile.php" || $current_page == "post.php" || $current_page == "edit.php")
    {
        if (isset($_GET['id'])) { $current_page_id = intval($_GET['id']); }
        else if (isset($_GET['pid'])) {
            $current_topic_id = $db->query('SELECT topic_id FROM '.$db->prefix.'posts WHERE id=\''.intval($_GET["pid"]).'\'') or error('Unable to fetch topic info', __FILE__, __LINE__, $db->error());
            $tmp = $db->result($current_topic_id, 0);
            $current_page_id = ($tmp != '') ? $tmp : '0' ;
        }
        else if (isset($_GET['tid']))
        { $current_page_id = intval($_GET['tid']); }
        else if (isset($_GET['fid']))
        { $current_page_id = intval($_GET['fid']); }
        else $current_page_id = 0;
    }
    else  $current_page_id = 0;

    if($pun_user['id'] > 1)
    {
        $db->query('UPDATE '.$db->prefix.'online SET current_page=\''.$current_page_full.'\', current_ip=\''.$current_ip.'\', current_page_id=\''.$current_page_id.'\' WHERE user_id=\''.$pun_user['id'].'\'') or error('Unable to update online list', __FILE__, __LINE__, $db->error());

    }
    else $db->query('UPDATE '.$db->prefix.'online SET current_page=\''.$current_page_full.'\', current_ip=\''.$current_ip.'\', current_page_id=\''.$current_page_id.'\' WHERE ident=\''.$current_ip.'\'') or error('Unable to update online list', __FILE__, __LINE__, $db->error());

    // Fetch all online list entries that are older than "o_timeout_online"
    $result = $db->query('SELECT * FROM '.$db->prefix.'online WHERE logged<'.($now-$pun_config['o_timeout_online'])) or error('Unable to delete from online list', __FILE__, __LINE__, $db->error());
    while ($cur_user = $db->fetch_assoc($result))
    {
        // If the entry is a guest, delete it
        if ($cur_user['user_id'] == '1')
        $db->query('DELETE FROM '.$db->prefix.'online WHERE ident=\''.$db->escape($cur_user['ident']).'\'') or error('Unable to delete from online list', __FILE__, __LINE__, $db->error());
        else
        {
            // If the entry is older than "o_timeout_visit", update last_visit for the user in question, then delete him/her from the online list
            if ($cur_user['logged'] < ($now-$pun_config['o_timeout_visit']))
            {
                //$db->query('UPDATE '.$db->prefix.'users SET last_visit='.$cur_user['logged'].' WHERE id='.$cur_user['user_id']) or error('Unable to update user visit data', __FILE__, __LINE__, $db->error());
                // MOD: MARK TOPICS AS READ - 1 LINE MODIFIED CODE FOLLOWS
                $db->query('UPDATE '.$db->prefix.'users SET last_visit='.$cur_user['logged'].', read_topics=NULL WHERE id='.$cur_user['user_id']) or error('Unable to update user visit data', __FILE__, __LINE__, $db->error());

                $db->query('DELETE FROM '.$db->prefix.'online WHERE user_id='.$cur_user['user_id']) or error('Unable to delete from online list', __FILE__, __LINE__, $db->error());
            }
            else if ($cur_user['idle'] == '0')
            $db->query('UPDATE '.$db->prefix.'online SET idle=1 WHERE user_id='.$cur_user['user_id']) or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
        }
    }
}

So now, if 10+ users are online i sometimes have db errors like this 

ERROR:  deadlock detected
DETAIL:  Process 38554 waits for ShareLock on transaction 29676567; blocked by process 38553.
        Process 38553 waits for ShareLock on transaction 29676569; blocked by process 38554.
STATEMENT:  DELETE FROM pun_online WHERE ident='?.?.?.?'

or

ERROR:  deadlock detected
DETAIL:  Process 673 waits for ShareLock on transaction 14821791; blocked by process 674.
        Process 674 waits for ShareLock on transaction 14821789; blocked by process 673.
STATEMENT:  UPDATE pun_online SET idle=1 WHERE user_id=?

i don't know if the bug is reproducible at vanilla punbb install, but if look at the queries in the errors i see that these are original punbb ones
can anyone help with debugging this or a solution ?

XuMiX's Website

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: DB deadlocks if using postgres

Hmm. I just read this: http://www.postgresql.org/docs/7.4/stat … cking.html

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order. That was the reason for the previous deadlock example: if both transactions had updated the rows in the same order, no deadlock would have occurred. One should also ensure that the first lock acquired on an object in a transaction is the highest mode that will be needed for that object. If it is not feasible to verify this in advance, then deadlocks may be handled on-the-fly by retrying transactions that are aborted due to deadlock.

I will try to look into it, but I'm not making any promises smile

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website