On Wednesday 2002-02-13 at 08:15:14 +0800, Frank Wang wrote:

> I know we have to keep transaction open as short as possible,
> here is a simple situation I'd like to ask gurus here about a proper
> management of transaction:
>
> A time keepping program runs 24 hours a day to capture
> time in/out of workers, when user swipes his card, the program
> has to verify a IB server to see if he is a valid user before inserting
> the record to a text file, if we keep a transaction open for validating
> purpose, things are simple but the transaction will be opened all the
> time. if we start the transaction to verify , rollback afterward, this
> has to be done for every swipe which seems an expensive operation,
> any idea?

Only connecting to the database in the first instance is a
time-consuming operation. What you should be doing is something like
this:

1. Connect to the database at startup time

2. Process timekeeping events.

3. Disconnect from the database at shutdown time (which would be
infrequent in your case).


Step 2 above would consist of something like:

(a) Run a SELECT statement or stored procedure to verify valid user
(b) Commit the transaction (even if you only issued a SELECT
statement).

This assumes that a transaction will start automatically when you
issue the SELECT statement (as opposed to a transaction being already
open), and that the commit does not cause a new transaction to be
automatically opened.

Starting a transaction, issuing a SELECT, and committing the
transaction should be a very fast operation.

>
> Also, what does the 'Oldest transaction' mean? thanks.

I believe it's OIT ("Oldest Interesting Transaction"). You shouldn't
need to worry about it if you don't keep your transactions open.


David Trudgett