Using SELECT MAX(ID)+1 in an insert statement

I am working with the following Knowledge Tree table in MySQL - it holds reference data.

CREATE TABLE `document_types_lookup` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(100),
    `disabled` BIT DEFAULT'0' NOT NULL
);

ALTER TABLE `document_types_lookup` ADD PRIMARY KEY (`id`);

I needed to insert a few thousand rows, but since there is no auto-increment field for the ID, I needed SQL that could increment ID for me - using SQL only, no script. I found a bug report (Allow INSERT...SELECT MAX(x)+1 from the same table) that had the below snippet that used select (max(id)+1) to increment ID for an insert.

insert into foo(lfd) select (max(lfd)+1) from foo;

And while I could get that statement by itself to work (inserting only ID into a record), I could not not adapt it to add the rest of the record values.

insert into
   document_types_lookup(id, name, disabled)
values
   (select (max(id)+1) from document_types_lookup, 'a name', false);

I always got the below error.

Executing:

insert into document_types_lookup(id, name, disabled) values (select (max(id)+1) from document_types_lookup, 'a name', false)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select (max(id)+1) from document_types_lookup, 'a name', false)' at line 1
[Error Code: 1064]
[SQL State: 42000]

So, my work-around is to use two statements instead of one: an insert followed by an update, as below.

insert into
   document_types_lookup(id)
   select (max(id)+1) from document_types_lookup;

update
   document_types_lookup
set
   name = 'CORP_HMN_000_CA_',
   disabled = false
where
   name is null;

OK, see any problems with this script? You should - it's a dangerous script for two big reasons. Firstly, it isn't thread safe, i.e. using select (max(id)+1) in an insert is dangerous if there are multiple people updating the table at the same time. You have no guarantee that between the time you have selected max ID + 1 and the time the insert actually happens, a new record might have been inserted by someone else, meaning that when your insert statement finally hits, your max ID + 1 will not be unique anymore. You can solve this by locking the table before your insert and releasing it afterwards. The second reason that this is a dangerous script is because it assumes there will only ever be one record where name is null; it does this as I could think of no other way to determine what record I just inserted.

So, this script works only because a) I knew I was the only person who would be updating the table at that time and b) I knew name would have a value for all records other than the one I had just inserted.