MySQL Forums :: Newbie :: A sequence solution for those that want it


Advanced Search

A sequence solution for those that want it
Posted by: Chris Preston ()
Date: March 15, 2009 11:41PM

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

Options: ReplyQuote


Subject Written By Posted
A sequence solution for those that want it Chris Preston 03/15/2009 11:41PM
Re: A sequence solution for those that want it Rick James 03/16/2009 10:04PM
Re: A sequence solution for those that want it Chris Preston 03/17/2009 03:31PM
Re: A sequence solution for those that want it Rick James 05/14/2009 10:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.