[drupal-devel] [feature] Change LOCK in db_next_id (MySQL)
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: tostinni Status: patch 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 tostinni
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: chx Status: patch Hm , tostinni is this applicable for MyISAM tables? I thought select for update is for InnoDB. chx Previous comments: ------------------------------------------------------------------------ April 27, 2005 - 20: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
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: tostinni Status: patch 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 tostinni Previous comments: ------------------------------------------------------------------------ April 27, 2005 - 14: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 - 15:10 : chx Hm , tostinni is this applicable for MyISAM tables? I thought select for update is for InnoDB.
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: Dries Status: patch 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? Dries Previous comments: ------------------------------------------------------------------------ April 27, 2005 - 21: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 - 22:10 : chx Hm , tostinni is this applicable for MyISAM tables? I thought select for update is for InnoDB. ------------------------------------------------------------------------ April 28, 2005 - 00: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
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: Dries Status: patch 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). Dries Previous comments: ------------------------------------------------------------------------ April 27, 2005 - 21: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 - 22:10 : chx Hm , tostinni is this applicable for MyISAM tables? I thought select for update is for InnoDB. ------------------------------------------------------------------------ April 28, 2005 - 00: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 - 19: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?
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.diff (702 bytes)
From MySQL Reference Manual, SELECT Syntax [1]:
"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 [2] 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. [1] http://dev.mysql.com/doc/mysql/en/select.html#id2961943 [2] http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html 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 [3] 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 ;) [3] 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).
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
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: tostinni Status: patch Ok, sorry, I thought that MySQL doc would be more explicit regarding ROW locks. As I didn't encounter errors with MySQL 3, I thought lock just apply correctly. BTW, I read the doc and had seen the LAST_INSERT_ID that menesis proposed, but as it didn't make any lock, I thought it wouldn't be an acceptable way to handle the db_next_id integrity. tostinni Previous comments: ------------------------------------------------------------------------ April 27, 2005 - 14: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 - 15:10 : chx Hm , tostinni is this applicable for MyISAM tables? I thought select for update is for InnoDB. ------------------------------------------------------------------------ April 27, 2005 - 17: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 - 12: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 1, 2005 - 16: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 - 05: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 ------------------------------------------------------------------------ May 2, 2005 - 05:43 : menesis 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.
participants (4)
-
chx -
Dries
-
menesis
-
tostinni