deadlock - Dev Shed

Dev Shed Forums Sponsor:

November 21st, 2004, 06:58 PM

quipo

Contributing User

Join Date: Jul 2002

Posts: 60

Time spent in forums: 1 Day 3 h 34 m 28 sec
Reputation Power: 11

deadlock

Hi,
I'm doing a single insert with a prepared query on an empty table.

I have something like:

- BEGIN TRANSACTION
- PREPARE QUERY
- EXECUTE QUERY
- COMMIT TRANSACTION

The first time I run it, everything is fine.
The second time, though, I get a deadlock error:

deadlock: violation of PRIMARY or UNIQUE KEY constraint "PK_USERS" on table "USERS"

Please note that I empty the table between the two requests.

The third time I run the query, it's fine again, and the fourth returns a deadlock error again, and so on.

Why does that happen?

TIA

November 22nd, 2004, 05:42 AM

SilverDB

Contributing User

Join Date: Sep 2004

Location: Romania

Posts: 173

Time spent in forums: 1 Day 45 m 53 sec
Reputation Power: 4

Is that the table you are trying to insert a record into ? table USERS ?
can you post some of the query's text ?

November 24th, 2004, 06:32 PM

quipo

Contributing User

Join Date: Jul 2002

Posts: 60

Time spent in forums: 1 Day 3 h 34 m 28 sec
Reputation Power: 11

sorry for the delay but I didn't get the email notice

Quote:

Originally Posted by SilverDB

Is that the table you are trying to insert a record into ? table USERS ?

yes

Quote:

Originally Posted by SilverDB

can you post some of the query's text ?

This is a php script reproducing this issue:

PHP Code:


		
			
<?php

$database = 'localhost:/path/to/driver_test.FDB';

$username = 'username';

$password = 'password';



// =============================================================================



/**

 * Print the contents of the table (debug)

 */

function dump_table(&$dbh, $table)

{

    $query = 'SELECT * FROM '.$table;

    $sth = ibase_query($dbh, $query);

    echo '<pre>Table dump: ';

    while ($row = ibase_fetch_row($sth)) {

        print_r($row);

    }

    echo '</pre><hr />';

    ibase_free_result($sth);

}



/**

 * Empty the table

 */

function empty_table(&$dbh, $table)

{

    ibase_query($dbh, 'DELETE FROM '.$table);

}



/**

 * Insert a sample row within a transaction

 */

function insert_row(&$dbh)

{

    $query = 'INSERT INTO users (user_name, user_password, subscribed, user_id, quota, weight, access_date, access_time, approved) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)';

    $trh    = ibase_trans();

    $sth    = ibase_prepare($trh, $query);

    $params = array(

        0 => $sth,

        1 => 'user_1',

        2 => 'somepassword',

        3 => 'N',

        4 => 1,

        5 => 101,

        6 => 1,

        7 => '2004-10-23',

        8 => '19:29:29',

        9 => '2004-10-23 19:29:29',

    );

    $result = call_user_func_array('ibase_execute', $params);

    ibase_commit($trh);

    return ($result) ? true : ibase_errmsg();

}



// =============================================================================



$dbh = ibase_connect($database, $username, $password);

if (!is_resource($dbh)) {

    die('cannot connect');

}

empty_table($dbh, 'users');



// =============================================================================



$err = insert_row(&$dbh);

echo '<pre>Insert row: ';

var_dump($err);

echo '</pre><hr />';



// =============================================================================



dump_table($dbh, 'users');

empty_table($dbh, 'users');

dump_table($dbh, 'users');



=============================================================================



$err = insert_row(&$dbh);

echo '<pre>Insert row: ';

var_dump($err);

echo '</pre><hr />';



// =============================================================================



dump_table($dbh, 'users');

empty_table($dbh, 'users');

dump_table($dbh, 'users');



// =============================================================================



ibase_close($dbh);

?>

and this is the table definition:

Code:

USER_ID INTEGER Primary Key(PK_USERS)
SUBSCRIBED CHARACTER(1)
QUOTA DECIMAL(10)
WEIGHT DOUBLE
ACCESS_DATE DATE
ACCESS_TIME TIME
APPROVED TIMESTAMP
USER_NAME VARCHAR(255)
USER_PASSWORD CHARACTER(200)

November 30th, 2004, 08:45 AM

fikret

Contributing User

Join Date: Aug 2004

Location: Sarajevo, Bosnia

Posts: 111

Time spent in forums: 1 Day 5 h 12 m 13 sec
Reputation Power: 7

Is your transaction commited AFTER you empty table?

--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net
TAMP R&D Team
FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation
JEDI VCS contributor
http://jedivcs.sourceforge.net/

November 30th, 2004, 08:49 AM

quipo

Contributing User

Join Date: Jul 2002

Posts: 60

Time spent in forums: 1 Day 3 h 34 m 28 sec
Reputation Power: 11

Quote:

Originally Posted by fikret

Is your transaction commited AFTER you empty table?

the 'DELETE FROM users' query is not within a transaction...

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: