Chuck, ----- Original Message ----- From: "Chuck Simmons" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, July 10, 2002 1:08 AM Subject: undocumented change in behavior?
> There appears to be an undocumented change from 3.23.49 to 3.23.51, > possibly for the worse. > > Consider two sessions executing the following sequence of commands on an > innodb table (assume maximum ACID properties): > > session 1> begin; > session 1> update table set field = field + 1 where primary_key = 1; > > session 2> select * from table where primary_key = 1; > > > In version 3.23.49, session 2 returns a row relatively quickly. In > version 3.23.51, session 2 blocks. The 3.23.49 behavior is reasonable > -- the 2nd session is not required to block because the database is > allowed to choose an ordering of transactions when transactions are > concurrent and it is reasonable to choose the order where session 2 > completes before session 1. normally InnoDB performs a simple SELECT as a consistent non-locking read. That is why the query in .49 returned immediately. But how do you get it to wait in .51? Have you done SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE? In .49 that did not have any effect, but in .50 it changes all SELECTs to use LOCK IN SHARE MODE. This is documented in section 16 of http://www.innodb.com/ibman.html. Below is an example run: Session 1: ................... mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t40 set c = now() where a = 2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ............. Session 2 (started later): ............ mysql> select * from t40 where a = 2; +---+------------+---------------------+ | a | b | c | +---+------------+---------------------+ | 2 | jhgjhghghj | 2002-07-10 01:03:48 | +---+------------+---------------------+ 1 row in set (0.00 sec) mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql> select * from t40 where a = 2; ERROR 1205: Lock wait timeout exceeded; Try restarting transaction mysql> > So, it appears that version 3.23.51 has different behavior and I don't > see this documented in the list of changes. And it could be argued that > the 3.23.49 behavior was better. > > > [fyi... one other undocumented change would be the change to the client > libraries so that when an application tries to parse a group out of a > configuration file, there won't be a core dump. I think this was a bug > fix to libmysql.c, adding a '*' to the statement 'for (end= *option ; > *(end= strcend(end,'_')) ; )'.] > > chuck Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql database --------------------------------------------------------------------- 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
