Dana, a deadlock can easily occur on a single table with row-level (or page-level) locking. What happens is something like the following: connection has lock on wants lock on alpha object A object B beta object B object A With page-level locking this would obviously be possible to happen only if the objects resided in different pages. Anyway, this type of situation cannot be resolved by the connections on their own, because they both see only their own context and end up sitting there indefinitely waiting for the object they want to be freed. Therefore, it must be handled by either the application code or the rdbms itself. Some of the other database systems I know detect this situation on their own. Oracle, for example, will roll back one of the contending connections and write a trace file plus an entry in its alert log (for an ORA-00060 error). Sadly, I don't know anything about BDB, so I can't really help you. Generally speaking I've not yet met a situation where it was necessary to sequentially lock several objects on the same table, though. I may be wrong, but as far as I can see this would seem to point at either loose design (not fully normalized - if the data is normalized you simply go and lock the (single) parent object, then all child objects of this parent are implicitly locked if all connections behave in the same way) or at sub-optimal coding (atomicity of operations should have been preserved). No offense intended, as I say, I may be totally off the beam here. Cheers, Christian Sage > -----Urspr�ngliche Nachricht----- > Von: Dana Powers [mailto:[EMAIL PROTECTED]] > Gesendet: Samstag, 15. September 2001 23:37 > An: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Betreff: Re: BDB table error > > > I've found the answer to my problem in the bdb source... > > From bdb/include/db.src: > #define DB_LOCK_DEADLOCK (-30996)/* Deadlock. */ > > So the question is: how can a bunch of simultaneous connections that only > work on 1 table create a deadlock. And how can I write my code to > avoid this > problem? > Is this an intrinsic problem with BDB, a problem with the way > mysql handles > BDB, or a problem with my application code? > > Additionaly, it would be nice if either the mysql engine returned a > string-ified version of the error code or if perror could handle > these error > codes. > > dpk > > > ----- Original Message ----- > From: "Dana Powers" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Saturday, September 15, 2001 1:52 PM > Subject: BDB table error > > > > Im getting this error from attempted inserts into a BDB table using > > mysql-3.23.42 ( same error but more often in 3.23.38 ): > > > > Could not insert new row into SESSION_DATA: Got error -30996 from table > > handler > > > > First - I have not been able to find what this error means in either the > > source code, online, or with tools like perror ( doesnt like negative > > numbers ). Does anyone have an idea what this is, or how to find out? > > > > Info on the situation: > > I've got X simultaneous connections inserting and updating > rows, and this > > error consistently pops up if X > 16. If X = 32, more than half of the > > inserts are errors. > > > > Here is the table: > > > > CREATE TABLE SESSION_DATA ( > > SESSION_ID int(10) unsigned NOT NULL auto_increment, > > SESSION_KEY varchar(32) default NULL, > > TS_ACCESS timestamp(14) NOT NULL, > > DATA text NOT NULL, > > PRIMARY KEY (SESSION_ID), > > UNIQUE KEY SESSION_DATA___SESSION_KEY (SESSION_KEY) > > ) TYPE=BerkeleyDB; > > > > Here are the sql statements used: > > > > ## Create a Session > > SET AUTOCOMMIT=0; > > INSERT INTO SESSION_DATA (SESSION_KEY,TS_ACCESS,DATA) VALUES > (NULL,NULL,''); > > UPDATE SESSION_DATA SET > SESSION_KEY='32bytesofblah',TS_ACCESS=NULL,DATA='' > > > WHERE SESSION_ID=###; ( ### was set to LAST_INSERT_ID() between sql > > statements ) > > COMMIT; > > SET AUTOCOMMIT=1; > > > > ## Update a Session ( AUTOCOMMIT=1 ) > > UPDATE SESSION_DATA SET SESSION_KEY='32bytes > ofblah',TS_ACCESS=NOW(),DATA='a > > load of text...' WHERE SESSION_ID=###; > > > > thanks for any info, > > dpk > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail [EMAIL PROTECTED] > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > > > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
