in reply to portable mysql auto_increment

You might want to take a look at DBIx::Sequence, which does a similar thing to what you want in a vaguely cross-platform way.

From the pod...

This module is intended to give easier portability to Perl database application by providing a database independant unique ID generator. This way, an application developer is not bound to use his database's SEQUENCE or auto_increment thus making his application portable on multiple database environnements.

This module implements a simple Spin Locker mechanism and is garanteed to return a unique value every time it is called, even with concurrent processes. It uses your database for its state storage with ANSI SQL92 compliant SQL. All SQL queries inside DBIx::Sequence are pre cached and very efficient especially under mod_perl.

Replies are listed 'Best First'.
Re: Re: portable mysql auto_increment
by mpeppler (Vicar) on Oct 25, 2002 at 00:04 UTC
    I took a quick look at that module and off-hand I don't see how it can guarantee to avoid race conditions.

    I'd be careful of any module that doesn't use transactions to guarantee single threaded access to a sequence table.

    Michael

[reply]

      Code looks solid to me. Can you point out where you think it would fail?

      I admit it's a bit of a pain to have to deal with an AutoCommit database handle, but if you're porting stuff between MySQL and other DBs I've found it a handy tool.

      (not saying that the transaction approach is bad, quite the opposite - just that DBIx::Sequence is useful if you have to move between databases without decent transaction/sequence support and those with.)

[reply]
        Well - to start off DBIx::Sequence doesn't work with Sybase (and probably won't work with MS-SQL).

        Running it without creating the tables first generates the following error: 

        ct_result(ct_dynamic(CS_PREPARE)) returned -205 at /usr/lib/perl5/site
+_perl/5.6.0/i386-linux/DBD/Sybase.pm line 105.
DBIx::Sequence: Server message number=208 severity=16 state=1 line=1 s
+erver=trollprocedure=DBD1text=dbix_sequence_state not found. Specify 
+owner.objectname or use sp_help to check whether the object exists (s
+p_help may produce lots of output).
 at ./sequence.pl line 5

        [download]

        Creating the tables manually and running a basic loop to get new IDs generates the following error: 

        ct_param() failed! at /usr/lib/perl5/site_perl/5.6.0/DBIx/Sequence.pm 
+line 257.
DBIx::Sequence: OpenClient message: LAYER = (1) ORIGIN = (1) SEVERITY 
+= (1) NUMBER = (16)
Message String: ct_param(): user api layer: external error: This routi
+ne cannot be called while results are pending for a command that has 
+been sent to the server.
 at ./sequence.pl line 11

        [download]
        The reason for this is that this module doesn't properly fetch all results after executing queries - and this leaves the various statement handles that it creates in an unstable state when used with DBD::Sybase.

        In addition, pre-preparing 5 or 6 queries works really well with Oracle, but is not a good idea with Sybase, because this will open 5 or 6 connections to the server for each instance of DBIx::Sequence.

        After patching the code so that it will work with Sybase I ran three concurrent processes that did nothing but fetch new IDs. The result was that only one process actually got any IDs - the other two were effectively locked out. Admittedly this is an extreme use of the module, but it does show the limits that this technique can have.

        Michael

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom