On Mon, Apr 29, 2002 at 12:52:23PM +0100, Steve Hay wrote: > Hi, > > In addition to the deadlock problems that I'm having, as described > in previous e-mails (still not solved...), it appears that the > transaction_isolation level setting is not being honoured, at least > when using BDB tables.
That's correct. The docs need updating, but the transaction isolation level setting only applies to InnoDB tables (in a limited fashion) and Gemini tables (if you're using NuSphere's MySQL distribution). > On a simple "test" database created with "CREATE TABLE x (id INT) > TYPE=BDB" running with the default transaction_isolation level set to > READ-COMMITTED try the following: > > (1) Establish one connection with "mysql -u root test". > > (2) Disable autocommit with "set autocommit=0;". > > (3) Perform a SELECT on the database's only table with "select id from x;". > > (4) Establish a second connection with "mysql -u root test". > > (5) Attempt to perform an INSERT with "insert into x (id) values (1);". > > The second connection now waits until you return to the first connection > and issue "commit;" (or "rollback;"). > > In other words you cannot INSERT into table x in the second > connection while there is an uncommitted transaction still active in > the first connection which had done a SELECT on that table. Here's my semi-informed guess. Since your first query "touches" all the pages in the database (BDB uses page locking), it probably sets a shared lock on all of them. But your insert needs to get an exclusive lock on the last page in the database so the record can be added. It cannot get that lock until the first query releases its lock on that page. > This behaviour would be expected if the transaction_isolation level > was SERIALIZABLE, but not when it is READ-COMMITTED. BDB works in READ-COMMITTED all the time, except that it'll act like serializable in this case. > Even explicitly setting the transaction isolation level in each > connection with "set global transaction isolation level read > committed" makes no difference. Correct. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 81 days, processed 2,094,145,894 queries (298/sec. avg) --------------------------------------------------------------------- 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
