[drupal-devel] [feature] Change LOCK in db_next_id (MySQL)

[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:   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.