[Gllug] A programming question. long.

[Bruce Richardson]

On Fri, Jul 06, 2001 at 04:48:38PM +0100, Paul Brazier wrote:
> Isn't adding your connection ID to the record being edited the simplest
> method?
> 
> All you need is:
> 
> UPDATE table
> SET connId = 1234     # supplied by the client
> WHERE field = (SELECT max(field) FROM table WHERE connID IS NULL)
> 
> assuming "connID" uniquely identifies the connection and "field" is the
> column used to define the "top" row.
> 
> This will in effect enable each connection to 
> a) select their row and 
> b) prevent others from also selecting it
> and it does this in one SQL statement which has its own implicit
> transaction.

Well, I wouldn't trust the client to provide the connection id - the
server can work that out and check permissions.  But otherwise it works
- see my last reply to Alex for potential drawbacks.

And if I did do it that way I would still do it as part of a larger
trigger procedure.  The whole process, from selecting/updating the
record to deleting it or releasing it, should be run by the one trigger
operation.  That way you guarantee that the data is consistent even if
the application crashes or loses it's connection to the database server.

-- 
Bruce

If the universe were simple enough to be understood, we would be too
simple to understand it.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 261 bytes
Desc: not available
URL: <http://mailman.lug.org.uk/pipermail/gllug/attachments/20010706/1be92daa/attachment.pgp>