[OT] SQL question

[Peter Haworth]

It seems Malte is having two problems - how to conditionally update a
column value and how to ensure everything stays locked during the update.

I think this should work:

BEGIN TRANSACTION
INSERT INTO test2 (id,UUID,running_number) VALUES (3,:1,CASE WHEN (SELECT
count(UUID) FROM table2 WHERE UUID=:1)=0 THEN (SELECT first_number FROM
table1 WHERE UUID=:1)+1 ELSE (SELECT MAX(running_number FROM table2)+1 END;
END TRANSACTION


Pete
lcSQL Software <http://www.lcsql.com>


On Mon, Nov 11, 2013 at 12:54 PM, Mike Kerner <MikeKerner at roadrunner.com>wrote:

> This is one of many reasons why triggers exist.  There will be a single
> trigger process.  Inside of the trigger you can assign the incrementing
> values, thus you will be guaranteed to get it to act the way you want, no
> matter what happens.  The trigger locks the table while it is operating.
> In larger systems this has the potential to be a problem, or introduce
> deadlock, so you have to be careful, but there are just those cases where
> you absolutely, positively have to have something happen a certain way.
>
> You can also use a semaphore, but I would suggest you just use a trigger.
>
>
> On Mon, Nov 11, 2013 at 11:14 AM, Dr. Hawkins <dochawk at gmail.com> wrote:
>
> > On Mon, Nov 11, 2013 at 6:26 AM, Malte Brill <revolution at derbrill.de>
> > wrote:
> >
> > > The Problem is, that postGreSQL appears to be locking tables only
> during
> > > the transaction and automagically releases the lock, which is not ideal
> > in
> > > my situation.
> >
> >
> > You can use a begin/end lock to get everything done:
> >
> >    BEGIN TRANSACTION;
> >        INSERT something;
> >        SELECT something else
> >    END TRANSACTION;
> >
> > and either the whole block happens, or the db is untouched.
> >
> > You could also make the column a serial or auto-incrementing key.
> >
> > --
> > Dr. Richard E. Hawkins, Esq.
> > (702) 508-8462
> > _______________________________________________
> > use-livecode mailing list
> > use-livecode at lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> > subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
> >
>
>
>
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
>    and did a little diving.
> And God said, "This is good."
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>