Re: ensuring row/table locking

["Martin Cordova S." <mcordova@xxxxxxxxxxxx>]

Aaron, as far as I understood the problem (I am not sure I did), if the
reading and writing of data may occur without user interface interactions,
then just wrap your code between BeginTrans/Commit to ensure exclusive
access to the data pages. Load Balancing should not pose a problem, because
all concurrent proceses must confront McKoi at the end, and McKoi will
properly serialize the batch processes (transactions).

Something like:
1) BeginTrans
    2) Get Recordset
    3) Update Table1
    4) Update Table2
5) Commit

Depending on the isolation level (McKoi uses the strongest), no one will be
able to touch those records while you got in your transaction, otherwise, an
error will be triggered and you will be able to safely abort and retry.

A few years ago, when using a network/relational model database called Raima
db_Vista (aka Velocis aka Birdstep), they had a very clear locking
mechanism: you had to aquire all the necesary locks at once before executing
the operations of your transaction. There was no way of executing a single
step if all the locks were not in place. It was expensive (table locks) but
effective.

Well, I am not sure I was any helpful, hope you find a solution soon.

Regards,
Martin


----- Original Message -----
From: "Aaron Westendorf" <awestendorf@titan.com>
To: <mckoidb@mckoi.com>
Sent: Tuesday, February 25, 2003 10:46 AM
Subject: Re: ensuring row/table locking


> > I would prefer using a table that works as a semaphore between different
> > process. Use this table to signal when you are "reserving" a record, and
> > clear the flag when you are done. Use a batch process to clear flags
that
> > were left "dirty" by unfinished processes.
>
> This is one of the methods we have discussed, and it seems that it might
> work the best.  Additionally, we could add the 'dirty' flag to the row
> and perform our locking in a more granular fashion.  The only problem is
> that there is still a very small chance that two processes might see the
> 'clean' state at the same time.  I suppose that this could be handled by
> only breaking out of the wait loop when the record is clean and a query
> like "UPDATE X WHERE dirty=0" returns  greater than 0.
>
> To widen the scope of my inquiry, consider the following situation.
> There are accounts in the system, and a table of properties maps the
> remaining quantity of various items purchased for each account.  We need
> a reliable way of querying the current quantity, performing an operation
> if quantity > 0, and then decrementing it.  This would be easy but our
> process may be load-balanced in a large-scale environment, and so
> several processes may be handling the requests.
>
> I'm sure someone else has come across this problem before, please share
> your insights if you can.
>
> cheers,
> Aaron
>
> --
>
> Titan Corporation
> 1020 Bay Area Boulevard - Suite 200
> Houston, TX  77058
> (281) 461-2100 x130
> (281) 488-0191 Fax
>
>
>
> ---------------------------------------------------------------
> Mckoi SQL Database mailing list  http://www.mckoi.com/database/
> To unsubscribe, send a message to mckoidb-unsubscribe@mckoi.com
>
>