A sequence solution for those that want it

[Chris Preston]

Hi,

Since I didn't see any good solutions to providing sequences on MySql, I wrote my own as in countless times before over the years, for a project I'm doing moving from Postgres (bleh, yeck, two optimizers 'cause neither work, pthpth) to MySql.

So, to whom it may concern, I provide the following solution minus curval, as I didn't bother writing curval since I don't need it, but it's trivial enough to write your own.


CREATE TABLE `SEQUENCE_VALUE` (
`SequenceName` VARCHAR(30) NOT NULL,
`SequenceValue` BIGINT NOT NULL,
CONSTRAINT `PK_SEQUENCE_VALUE` PRIMARY KEY (`SequenceName`)
);

DELIMITER |

create function nextval(theSequenceName varchar(30))
returns bigint
begin

declare prevSequence bigint;
declare nextSequence bigint;
declare isDone boolean;

set isDone=false;

while ( isDone=false ) do
select SequenceValue into prevSequence
from SEQUENCE_VALUE a
where
a.SequenceName=theSequenceName;

set nextSequence=prevSequence+1;

update SEQUENCE_VALUE a
set SequenceValue=nextSequence
where a.SequenceValue=prevSequence
and a.SequenceName=theSequenceName;

if ( row_count()=1 )
then
set isDone=true;
end if;

end while;

return nextSequence;

end |

If you are using a version of mysql wherein row_count() is not extant, you can simply do a select where the value is the new value and count the number of rows returning such that it's 1 or 0 and modify your 'if' logic accordingly.

This is, of course, sub-optimal compared to having sequences built into the dbms; however, it suffices as an alternative. I have not investigated the effect of using myisam for the sequence_value entity; however, my guess is that there is a way to handle this table atomically but without blocking on the update lock such that the row is updated in place and the lock is released within a transaction. If such a set of magical configuration mechanisms exist, it should suffice to provide equivalent atomic sequence values minus the prolonged serialization of a competing transaction's execution just like good old Oracle - But who knows or can say? :)


enjoy,


--cbp