problem with BDB table, error -30996
Hi, Has anyone got the following error on a BDB table : read_const: Got error -30996 when reading table We did some load testing on a particular BDB table and got this when we tried to run 20 concurrent queries on the table. The query in question works fine when the database instance is not loaded. Does anyone have a list of BDB error codes handy? I've searched the web and the mailing list archives and seems like no one ever responded to the same question about what this error means in previous postings :( Any help appreciated. Thanks! Cheers, Geoffrey __ Geoffrey Soh, Software Architect Ufinity - http://www.ufinity.com Leading Enterprise Access Management Software! 9 Scotts Road, Pacific Plaza, #06-01, Singapore 228210 Tel : +65 830-0341 Fax : +65 737-0213 __ - 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] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Hi!
Dana == Dana Powers [EMAIL PROTECTED] writes:
Dana Thanks for the reply! I can understand the situation with muti statement
Dana transactions, but I'm not using transactions ( at least from my
Dana perspective ); Im only issuing single 'atomic' statements. It would make
Dana much more sense if mysql internally detected the deadlock, released the
Dana locks and tried to process the statement again. It just seems so
Dana counter-intuitive that i can connect, issue a single, isolated select
Dana statement and get a deadlock. I dont know if this is possible, but I would
Dana love something like this in the mysql engine:
Dana retry-deadlock:
Dana error = do_query();
Dana if ( ( auto-commit == on ) ( error == DEADLOCK ) ( ++tries
Dana threshold ) ) {
Dana warn('Deadlock detected. Retrying query.');
Dana goto retry-deadlock;
Dana }
Dana Of course this could go into the application code, but it would have to go
Dana around every single query, transactional or not, and that seems so
Dana cumbersome given that the solution is the same in every case.
Dana dpk
Currenty the only way we can affect how locks works with BDB tables is
to use the --bdb-lock option.
This is passed to BDB with db_env-set_lk_detect().
We have actually a variable to do retry of locks:
In ha_berkeley.cc, line 82:
ulong berkeley_trans_retry=1;
Could you try to change the above to 10 and see if this helps.
This retry count is only used for insert/delete/update but it may
still help solve some of your problems (If it does, we will make this
variable available from the command line).
One problem in doing the lock detection in the server is that the you
may get a lock after you have already sent some data to the client.
It would on the other hand not that hard to do retries on the client
side if something goes wrong. I shall put this on our TODO.
Regards,
Monty
-
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]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Hi!
Dana == Dana Powers [EMAIL PROTECTED] writes:
Dana Certainly I understand how the deadlock can occur, but the question is why
Dana is this acceptable behaviour? Is the application developer expected to
Dana handle this case? If so, how? and shouldnt this be included in the manual
Dana under BDB problems? If Im supposed to wrap _every_ sql call I make to a BDB
Dana table with something like: 'do { query } while ( !deadlock );' why would I
Dana even bother trying to use BDB tables? This seems to me like a _fundamental_
Dana weakness of the BDB table type, and should at least be documented as such...
We shall will add som more documentation about this ASAP.
In MySQL 3.23.43 you will get the following error:
Error: 1213 (ER_LOCK_DEADLOCK)
Deadlock found when trying to get lock; Try restarting transaction,
Anyway, the problem you are experiencing is something that is normal
with all transactional databases. When you have many clients that is
reading/updating the same table there is always cases when you will
get deadlocks or other failure in the transaction and you have to redo
the whole transaction in the application.
Regards,
Monty
-
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]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Thanks for the reply! I can understand the situation with muti statement
transactions, but I'm not using transactions ( at least from my
perspective ); Im only issuing single 'atomic' statements. It would make
much more sense if mysql internally detected the deadlock, released the
locks and tried to process the statement again. It just seems so
counter-intuitive that i can connect, issue a single, isolated select
statement and get a deadlock. I dont know if this is possible, but I would
love something like this in the mysql engine:
retry-deadlock:
error = do_query();
if ( ( auto-commit == on ) ( error == DEADLOCK ) ( ++tries
threshold ) ) {
warn('Deadlock detected. Retrying query.');
goto retry-deadlock;
}
Of course this could go into the application code, but it would have to go
around every single query, transactional or not, and that seems so
cumbersome given that the solution is the same in every case.
dpk
- Original Message -
From: Michael Widenius [EMAIL PROTECTED]
To: Dana Powers [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, September 17, 2001 2:19 PM
Subject: Re: BDB table error
Hi!
Dana == Dana Powers [EMAIL PROTECTED] writes:
Dana Certainly I understand how the deadlock can occur, but the question
is why
Dana is this acceptable behaviour? Is the application developer expected
to
Dana handle this case? If so, how? and shouldnt this be included in the
manual
Dana under BDB problems? If Im supposed to wrap _every_ sql call I make
to a BDB
Dana table with something like: 'do { query } while ( !deadlock );' why
would I
Dana even bother trying to use BDB tables? This seems to me like a
_fundamental_
Dana weakness of the BDB table type, and should at least be documented as
such...
We shall will add som more documentation about this ASAP.
In MySQL 3.23.43 you will get the following error:
Error: 1213 (ER_LOCK_DEADLOCK)
Deadlock found when trying to get lock; Try restarting transaction,
Anyway, the problem you are experiencing is something that is normal
with all transactional databases. When you have many clients that is
reading/updating the same table there is always cases when you will
get deadlocks or other failure in the transaction and you have to redo
the whole transaction in the application.
Regards,
Monty
-
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 [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB table error
Dana, if you have page level locking, you can easily get a deadlock from an atomic insert done as a single transaction, and another user doing an atomic select in another single transaction. It happens this way: User A User B insert recordselect record from secondary to primary index index, get S-lock on page 2 get X-lock on page 1 select record from primary index get X-lock on page 1 - MUST WAIT insert record to secondary index get X-lock on page 2 -MUST WAIT - DEADLOCK Regards, Heikki http://www.innodb.com Ok, so the problem Im seeing is that any table that has an index other than the primary key is prone to deadlocks. I cant think of any reason that this is acceptable behaviour ( i.e. not a bug ), because I am doing 4 atomic operations on a single table with no user level locks.Here is a test: CREATE TABLE bdb_test ( id int unsigned not null auto_increment primary key, key1 char(32) not null, val1 char(32), unique (key1) ) TYPE=BDB; ( replacing unique(key1) with index(key1) also results in deadlocks ). Now run the following 4 queries in parallel: ## Insert a new row with 2 key values - 1 auto_incremented and 1 specified INSERT INTO bdb_test (key1) VALUES(CONCAT(CONNECTION_ID(),' - some extratext')); ## Update a non key column in row UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID(); ## Select based on secondary key SELECT * FROM bdb_test WHERE key1=CONCAT(CONNECTION_ID(),' - some extratext'); ## Select based on primary keySELECT * FROM bdb_test WHERE id=LAST_INSERT_ID(); I get deadlocks on my machine ( Intel w/ Dual 400 + 512MB RAM, running linux ) with 64 simultaneous threads. It appears that as the table gets larger ( and more pages created for locking, hehe ), the probability that we get a deadlock grows. - 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
Re: BDB table error
Certainly I understand how the deadlock can occur, but the question is why
is this acceptable behaviour? Is the application developer expected to
handle this case? If so, how? and shouldnt this be included in the manual
under BDB problems? If Im supposed to wrap _every_ sql call I make to a BDB
table with something like: 'do { query } while ( !deadlock );' why would I
even bother trying to use BDB tables? This seems to me like a _fundamental_
weakness of the BDB table type, and should at least be documented as such...
dpk
- Original Message -
From: Heikki Tuuri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, September 16, 2001 2:14 AM
Subject: Re: BDB table error
Dana,
if you have page level locking, you can easily
get a deadlock from an atomic insert done as
a single transaction, and another user doing
an atomic select in another single transaction.
It happens this way:
User A User B
insert recordselect record from secondary
to primary index index, get S-lock on page 2
get X-lock on page 1
select record from primary index
get X-lock on page 1 - MUST WAIT
insert record to
secondary index
get X-lock on page 2 -MUST WAIT - DEADLOCK
Regards,
Heikki
http://www.innodb.com
Ok, so the problem Im seeing is that any table that has an index other
than
the primary key is prone to deadlocks.
I cant think of any reason that this is acceptable behaviour ( i.e. not a
bug ), because I am doing 4 atomic operations on a single table with no
user
level locks.Here is a test:
CREATE TABLE bdb_test ( id int unsigned not null auto_increment primary
key,
key1 char(32) not null, val1 char(32), unique (key1) ) TYPE=BDB;
( replacing unique(key1) with index(key1) also results in deadlocks ).
Now run the following 4 queries in parallel:
## Insert a new row with 2 key values - 1 auto_incremented and 1
specified
INSERT INTO bdb_test (key1) VALUES(CONCAT(CONNECTION_ID(),' - some
extratext'));
## Update a non key column in row
UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID();
## Select based on secondary key
SELECT * FROM bdb_test WHERE key1=CONCAT(CONNECTION_ID(),' - some
extratext');
## Select based on primary keySELECT * FROM bdb_test WHERE
id=LAST_INSERT_ID();
I get deadlocks on my machine ( Intel w/ Dual 400 + 512MB RAM, running
linux ) with 64 simultaneous threads.
It appears that as the table gets larger ( and more pages created for
locking, hehe ), the probability that we get a deadlock grows.
-
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
Re: BDB table error
I've found the answer to my problem in the bdb source... From bdb/include/db.src: #defineDB_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
Re: BDB table error
I can totally understand that, but the problem is, my queries are only interested in one row each ( accessed by primary key ). Perhaps it has to do with the extra unique index? The flow is this: insert row. grab newly created primary key ( autoincrement ) + generate a unique 32 byte char string based on the primary key. update row to set unique char string. commit | rollback Could it be that bdb needs to grab a page lock on the index as well? hmm, that might explain it. dpk - Original Message - From: Christian Sage [EMAIL PROTECTED] To: Dana Powers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, September 15, 2001 3:08 PM Subject: AW: BDB table error Dana, a deadlock can easily occur on a single table with row-level (or page-level) locking. What happens is something like the following: connectionhas lock on wants lock on alpha object Aobject B beta object Bobject 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 - 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] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: BDB table error
As I said, I don't know anything about BDB, but I don't see how they could possibly prevent chaos without locking both all table pages and all index pages that are being modified by a transaction. And indeed, that would make a deadlock possible for multi-phase updates on the same row and thereby quite neatly explain the behaviour you're seeing. Stupid question on the side (just being nosy): why do you need to generate a unique char string from the primary key? I mean, what is the benefit when it was unique already? An ugly solution to your problem, by the way, might be to split the table into two: if you don't really need the auto-incremented primary key for SELECTs, you could move it to a separate table that contains only this one field. The flow would then be: - insert row into generator table. - grab newly created primary key (autoincrement) and generate a unique 32 byte char string based on it. - insert data row with generated char string as primary key. - commit/rollback This would still give you the unique seed for the generation of your char string, and the insert into the real table would be atomic again. Depends on the needs of your application, of course, whether this is workable. And, yes, from a design point of view it IS ugly. cs -Ursprüngliche Nachricht- Von: Dana Powers [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 16. September 2001 00:14 An: Christian Sage Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Betreff: Re: BDB table error I can totally understand that, but the problem is, my queries are only interested in one row each ( accessed by primary key ). Perhaps it has to do with the extra unique index? The flow is this: insert row. grab newly created primary key ( autoincrement ) + generate a unique 32 byte char string based on the primary key. update row to set unique char string. commit | rollback Could it be that bdb needs to grab a page lock on the index as well? hmm, that might explain it. dpk - Original Message - From: Christian Sage [EMAIL PROTECTED] To: Dana Powers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, September 15, 2001 3:08 PM Subject: AW: BDB table error Dana, a deadlock can easily occur on a single table with row-level (or page-level) locking. What happens is something like the following: connectionhas lock on wants lock on alpha object Aobject B beta object Bobject 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 - 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] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
