Issue status update for http://drupal.org/node/21439 Project: Drupal Version: 4.6.0 Component: base system Category: feature requests Priority: normal Assigned to: Anonymous Reported by: tostinni Updated by: menesis Status: patch Attachment: http://drupal.org/files/issues/db_next_id_0.diff (1.11 KB) misread my own quote, missed LAST_INSERT_ID in the query. Updated diff. menesis Previous comments: ------------------------------------------------------------------------ April 27, 2005 - 22:12 : tostinni Attachment: http://drupal.org/files/issues/database.mysql.inc.patch (600 bytes) Some ISP doesn't let user to make LOCK on tables (I'm thinking at free.fr a famous french free ISP). This patch provide another method to lock tables using SELECT ... FOR UPDATE ------------------------------------------------------------------------ April 27, 2005 - 23:10 : chx Hm , tostinni is this applicable for MyISAM tables? I thought select for update is for InnoDB. ------------------------------------------------------------------------ April 28, 2005 - 01:03 : tostinni Well, MySQL doc [1] doesn't specify that it's restricted to MyISAM tables type, in fact I try this in a DB I got at free.fr which restrict tables types to MyISAM and it worked. So I guess it's fine ;) [1] http://dev.mysql.com/doc/mysql/en/select.html ------------------------------------------------------------------------ May 1, 2005 - 20:15 : Dries SELECT ... FOR UPDATE uses row-level locking which requires MySQL 4 as far as I know. The syntax is ANSI SQL though. Are you using MySQL 3 or MySQL 4? ------------------------------------------------------------------------ May 2, 2005 - 00:34 : Dries I wrote a mail to drupal-devel about making MySQL 4 a requirement. If we agree to drop support for MySQL 3, the proposed patch might improve performance too (although it will not be noticable). ------------------------------------------------------------------------ May 2, 2005 - 13:13 : menesis Attachment: http://drupal.org/files/issues/db_next_id.diff (702 bytes)
From MySQL Reference Manual, SELECT Syntax [2]:
"If you use FOR UPDATE on a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. " Meaning FOR UPDATE clause is allowed on any engine, but it only works on InnoDB; MyISAM just ignores that, and no locking occurs. I found a useful tip in Locking Reads SELECT ... FOR UPDATE [3] page: "In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table: UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1); SELECT LAST_INSERT_ID(); The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table. " Tried to modify db_next_id(), diff attached, but not sure how to test if that works. Just an idea. [2] http://dev.mysql.com/doc/mysql/en/select.html#id2961943 [3] http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html