[Gllug] A programming question. long.

[Paul Brazier]

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.

If two people executed the above SQL (with their own connection IDs)
within a split second of each other they could never update the same
record (assuming the SQL engine works properly) as logically SQL
statements are executed in zero time.

It can even be a "persistent" connection i.e. the client application
could lay claim to the record, shut down and go back later to the same
record. Or you could erase the connID field when the connection closes.

If the client later abandons their processing, you can easily release
the record for someone else. Whereas if you had already deleted the
record this would be slightly more hassle, especially if the client
crashes or aborts before they'd done their processing, losing the record
information.

This also assumes your SQL dialect can deal with sub-SELECTS such as the
one above.
Although there's probably an equivalent syntax using joins.

-----Original Message-----
From: home at alexhudson.com 
Sent: 06 July 2001 15:53
To: alexhudson.com.home; linux.co.uk;.gllug
Subject: Re: [Gllug] A programming question. long.


On Fri, Jul 06, 2001 at 12:19:01PM +0100, Bruce Richardson wrote:
> > I don't necessarily agree with 'exclusive table lock' (bit of a
waste for
> > one row!)
> 
> Which is why I said the lock should be placed on the view, not the
> underlying table. 

But then you're locking the view, not the data - and the data is still
exposed (which is fine, so long as everything is using the view..). And
to
be honest, you don't need a view & set of triggers, because the standard
SQL
row locks are all you need.

> > successfully, which is silent data loss. Ick. It's up to the
application,
> > not the database, to remove items from the store.
> 
> No, it's the database's job IMO.

Ah :) Age old database disagreement. IMO, business logic has no place in
the
database, for one simple reason - the database can't maintain
consistency.
The database can tell you for sure that transactions proceeded okay, and
that the data fulfils a set of rules, but can't make the data obey those
rules by itself. 

> In my example the select trigger does something essential - marking
> the selected record "don't touch".

Which is what I don't understand - you seem to be using table-locking to
implement row-locking, which might be fair enough for MySQL (which
doesn't
support row-level locking), but not great for those dbs which do.

> This way the database takes care of all data validation tasks. 

The database has no idea about data validation; business logic should be
in
the software. For example, let's say that a banking application needs to
update a number of accounts at once (a transfer, perhaps). It's up to
the
application to specify that the account debited and the account credited
must be done so within one transaction. It's not for the database to
attempt
to work that out - the data within the database has meaning, and it's
the
application that determines that meaning. The database can't hold the
debit
transaction open expecting a credit transaction for the same amount -
that
requires a priori knowledge of the type of transaction. 

The database may very well be happy that the data has been correctly
stored
(as it may have been), but if it crashed at the wrong time (between two
account transactions, for example) even though the transaction log would
roll-back part of the data, the data itself might not be
self-consistent.
The database guarantees only that it stores the data as you ask; the
application needs to make sense of the data itself.

Cheers,

Alex.

-- 

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the originator.

This footnote also confirms that this email message has been checked
for the presence of computer viruses.

**********************************************************************

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug