[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Is INSERT...SELECT atomic? [REPOST]
From:       Allon Bendavid <allon () imacination ! com>
Date:       2004-01-30 18:50:44
Message-ID: BC3FE784.1E064%allon () imacination ! com
[Download RAW message or body]

Does anyone know this?

In the 4.0.17 and later build, the target table of an INSERT...SELECT can be
the same as the SELECT table (insert into table1...select ...from table1).

Does the process essentially happen in a natural lock?  In other words,
could the table change between the data retrieved in the select and the time
the insert gets executed?

We used to use something like this:

Lock tables table1 write;
Select @a:=max(column) from table1;
Insert into table1 (column) values (@a+1);
Unlock tables;

Could that now be done as:

Insert into table1 (column) select max(column)+1 from table1;

-Allon


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]