mysql LAST_INSERT_ID with parameter sequence implementation

While creating threadsafe sequence in mysql, I came across mysql documentation - https://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id, suggesting to create sequence like this -

1. mysql> CREATE TABLE sequence (id INT NOT NULL);
2. mysql> INSERT INTO sequence VALUES (0);
3. mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
4. mysql> SELECT LAST_INSERT_ID();

My question is instead of step 4, why can't i query sequence table directly to get the id every time i need to fetch a new id? Like this - SELECT id FROM sequence; Is there any downside of directly querying the sequence table vs LAST_INSERT_ID as suggested in docs?

My mysql implementation looks like this -

CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);



 DELIMITER //
 CREATE PROCEDURE nextVal(OUT nextval INT)
   BEGIN
   UPDATE sequence SET id=LAST_INSERT_ID(id+1);
   SELECT id into nextval FROM sequence;
   END //
 DELIMITER ;

And to generate new id, i can use this

   call nextVal(@output1);
   select @output1;

Edit1: Updating sequence creation process, to be lock free, after talking to all folks that replied. I have also made the table more generic to accommodate multiple sequences in single table and used function instead of procedure

 CREATE TABLE sequences (
  name CHAR(20),
  id BIGINT DEFAULT 1,
  increment TINYINT,
  UNIQUE KEY(name)
);


/* Call nextval('seqname'), and it returns the next value. */
/* If the named sequence does not yet exist, it is created with initial value 1 and increment 1 */
DELIMITER //
CREATE FUNCTION nextval (seqname CHAR(20))
RETURNS BIGINT
BEGIN
INSERT INTO sequences(name,id,increment) VALUES (seqname,LAST_INSERT_ID(1),1)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id+increment);
RETURN LAST_INSERT_ID();
END
//


/* Testing */
SELECT nextval('seq1');
SELECT nextval('seq2');
insert into sequences(name,id,increment) values ('seq3', 1000, 5);
SELECT nextval('seq3');

Solution

Your procedure won't work if another client runs it at the same time, because they're both updating the same row of the sequence table. You'd need to run both the UPDATE and SELECT in a transaction to prevent overlap.

LAST_INSERT_ID() is managed on a per-connection basis, so each client gets their own sequence, without having to lock each other out with transactions.