buffering & transactions

It is not necessary to buffer a table for transactions to work.
Nevertheless, I usually buffer a table (or use a view), then if a
transaction is necessary, put the transaction only around the TABLEUPDATE in
order to keep the transaction short.

But I'm really guessing here. Am I approaching it in the best manner? What
general rules are there for handling buffering and transactions?

Also, I occasionally want a transaction to cover changes to several tables,
with some calculation done during the process, and even a couple nested
transactions. The whole thing could take a few seconds (in a multi-user
environment). Is there anything specific I need to look out for? Other than
the limit on transaction levels, I mean.

Mark
Fri Mar 18 02:03:03 CST 2005

Hi Paul,
The article below might give you some hints.
http://support.microsoft.com/?id=129643
hth
Mark

"Paul Pedersen" wrote:

> It is not necessary to buffer a table for transactions to work.
> Nevertheless, I usually buffer a table (or use a view), then if a
> transaction is necessary, put the transaction only around the TABLEUPDATE in
> order to keep the transaction short.
>
> But I'm really guessing here. Am I approaching it in the best manner? What
> general rules are there for handling buffering and transactions?
>
> Also, I occasionally want a transaction to cover changes to several tables,
> with some calculation done during the process, and even a couple nested
> transactions. The whole thing could take a few seconds (in a multi-user
> environment). Is there anything specific I need to look out for? Other than
> the limit on transaction levels, I mean.
>
>
>
>

Paul
Fri Mar 18 07:48:36 CST 2005

Thanks, but there's not much there that's new to me, or particularly
helpful. I'm looking for practical guidelines on how long and involved
transactions can be, their effects on other users, what can go wrong and
why, that sort of thing.

Also, it says "Transaction processing should never be used without
buffering," without saying why. I hate it when they do that. Transactions
appear to work fine without buffering, so...?



"Mark" <Mark@discussions.microsoft.com> wrote in message
news:361E93EF-EB66-4350-B584-B74D355EAF25@microsoft.com...
> Hi Paul,
> The article below might give you some hints.
> http://support.microsoft.com/?id=129643
> hth
> Mark
>
> "Paul Pedersen" wrote:
>
>> It is not necessary to buffer a table for transactions to work.
>> Nevertheless, I usually buffer a table (or use a view), then if a
>> transaction is necessary, put the transaction only around the TABLEUPDATE
>> in
>> order to keep the transaction short.
>>
>> But I'm really guessing here. Am I approaching it in the best manner?
>> What
>> general rules are there for handling buffering and transactions?
>>
>> Also, I occasionally want a transaction to cover changes to several
>> tables,
>> with some calculation done during the process, and even a couple nested
>> transactions. The whole thing could take a few seconds (in a multi-user
>> environment). Is there anything specific I need to look out for? Other
>> than
>> the limit on transaction levels, I mean.
>>
>>
>>
>>

Ook
Fri Mar 18 09:19:46 CST 2005

I always buffer tables/views in a transaction. Being in a transaction does
not take care of record locking conflicts, and if someone else locks the
record before you try to update it and you don't buffer/rlock, you will get
record is in use responce just like you do if there is no transaction. A
transaction is not a replacement for normal record locking or buffering
strategies. I always buffer, and use tableupdate() in my transactions. If
tableupdate() fails, I then rollback and abort the process.I think the
biggest difference when using transactions is that you don't have to issue a
tablerevert() if something fails, you just ROLLBACK.

I read the article, and noticed this statement: "Because the transaction
locks all records involved, as well as the index for the tables involved,
other users cannot update the table at all while a transaction is in
progress. ". This is not entirely correct. Other users can indeed update the
table while a transaction is in progress, they just can't access the records
that are involved in your transaction, and I don't think they can update
indexed fields but I'm not sure. In our own application, we demonstrated
that while a checkout process has a transaction in place, other users are
able to check in, which updates the table involved in the transaction. We
also demonstrated that while the transaction is in place, other users cannot
access the records of the client involved in the transaction. Be wary of
timing, because locking records/tables in a transaction for an extended
length of time can bring all of your users to a dead halt. A transaction
should be very fast, seconds at the most and if you have a lot of users,
seconds may be too slow.




"Paul Pedersen" <no-reply@swen.com> wrote in message
news:OrXguR3KFHA.592@TK2MSFTNGP10.phx.gbl...
> It is not necessary to buffer a table for transactions to work.
> Nevertheless, I usually buffer a table (or use a view), then if a
> transaction is necessary, put the transaction only around the TABLEUPDATE
in
> order to keep the transaction short.
>
> But I'm really guessing here. Am I approaching it in the best manner? What
> general rules are there for handling buffering and transactions?
>
> Also, I occasionally want a transaction to cover changes to several
tables,
> with some calculation done during the process, and even a couple nested
> transactions. The whole thing could take a few seconds (in a multi-user
> environment). Is there anything specific I need to look out for? Other
than
> the limit on transaction levels, I mean.
>
>
>

Paul
Fri Mar 18 10:15:16 CST 2005

Thank you.

In my current case, I have some buffered tables and some not buffered. But
the unbuffered ones are simple INSERTs, so there should be no conflict.

I need to make a dozen or so updates in a transaction, but unfortunately,
each of those involves several sub-updates. Each of these is an inventory
adjustment where parts, components of parts, and components of components up
to nine levels deep must adjusted. I'm trying to keep the transaction as
short as possible but there's just no getting around some of this stuff.
I'll have to keep REPROCESS fairly short, to avoid locking out other users
for too long a time. And I hope I don't run into deadlocks...

One thing I discovered, not surprising I guess, is that transactions do not
hold across data sessions. If you start a transaction in one data session,
pass some processing to another, then rollback in the first, the work done
in the second does not roll back.




"Ook" <usenet@nospam.zootal.nomorespam.com> wrote in message
news:eR$$r38KFHA.3616@TK2MSFTNGP09.phx.gbl...
>I always buffer tables/views in a transaction. Being in a transaction does
> not take care of record locking conflicts, and if someone else locks the
> record before you try to update it and you don't buffer/rlock, you will
> get
> record is in use responce just like you do if there is no transaction. A
> transaction is not a replacement for normal record locking or buffering
> strategies. I always buffer, and use tableupdate() in my transactions. If
> tableupdate() fails, I then rollback and abort the process.I think the
> biggest difference when using transactions is that you don't have to issue
> a
> tablerevert() if something fails, you just ROLLBACK.
>
> I read the article, and noticed this statement: "Because the transaction
> locks all records involved, as well as the index for the tables involved,
> other users cannot update the table at all while a transaction is in
> progress. ". This is not entirely correct. Other users can indeed update
> the
> table while a transaction is in progress, they just can't access the
> records
> that are involved in your transaction, and I don't think they can update
> indexed fields but I'm not sure. In our own application, we demonstrated
> that while a checkout process has a transaction in place, other users are
> able to check in, which updates the table involved in the transaction. We
> also demonstrated that while the transaction is in place, other users
> cannot
> access the records of the client involved in the transaction. Be wary of
> timing, because locking records/tables in a transaction for an extended
> length of time can bring all of your users to a dead halt. A transaction
> should be very fast, seconds at the most and if you have a lot of users,
> seconds may be too slow.
>
>
>
>
> "Paul Pedersen" <no-reply@swen.com> wrote in message
> news:OrXguR3KFHA.592@TK2MSFTNGP10.phx.gbl...
>> It is not necessary to buffer a table for transactions to work.
>> Nevertheless, I usually buffer a table (or use a view), then if a
>> transaction is necessary, put the transaction only around the TABLEUPDATE
> in
>> order to keep the transaction short.
>>
>> But I'm really guessing here. Am I approaching it in the best manner?
>> What
>> general rules are there for handling buffering and transactions?
>>
>> Also, I occasionally want a transaction to cover changes to several
> tables,
>> with some calculation done during the process, and even a couple nested
>> transactions. The whole thing could take a few seconds (in a multi-user
>> environment). Is there anything specific I need to look out for? Other
> than
>> the limit on transaction levels, I mean.
>>
>>
>>
>
>

Dan
Fri Mar 18 10:55:25 CST 2005

The admonishment about buffering for transactions is because with dirty
reads on unbuffered data, changes are committed as soon as they're made.
Unless you want to hold a transaction open for an entire editing session,
and I KNOW you know better.

In other words, they just didn't say it very well. <g>

I wrap transactions around tablupdate(). The main benefit, of course, is
that ROLLBACK restores you to buffered/changed data so you can investigate
which change caused the tableupdate() to fail. There are cases where another
user's changes will make tableupdate() fail but it really doesn't matter to
your current transaction, so you can write code to make tableupdate()
succeed on a second attempt.

Dan


Paul Pedersen wrote:
> Thanks, but there's not much there that's new to me, or particularly
> helpful. I'm looking for practical guidelines on how long and involved
> transactions can be, their effects on other users, what can go wrong
> and why, that sort of thing.
>
> Also, it says "Transaction processing should never be used without
> buffering," without saying why. I hate it when they do that.
> Transactions appear to work fine without buffering, so...?
>
>
>
> "Mark" <Mark@discussions.microsoft.com> wrote in message
> news:361E93EF-EB66-4350-B584-B74D355EAF25@microsoft.com...
>> Hi Paul,
>> The article below might give you some hints.
>> http://support.microsoft.com/?id=129643
>> hth
>> Mark
>>
>> "Paul Pedersen" wrote:
>>
>>> It is not necessary to buffer a table for transactions to work.
>>> Nevertheless, I usually buffer a table (or use a view), then if a
>>> transaction is necessary, put the transaction only around the
>>> TABLEUPDATE in
>>> order to keep the transaction short.
>>>
>>> But I'm really guessing here. Am I approaching it in the best
>>> manner? What
>>> general rules are there for handling buffering and transactions?
>>>
>>> Also, I occasionally want a transaction to cover changes to several
>>> tables,
>>> with some calculation done during the process, and even a couple
>>> nested transactions. The whole thing could take a few seconds (in a
>>> multi-user environment). Is there anything specific I need to look
>>> out for? Other than
>>> the limit on transaction levels, I mean.

Paul
Fri Mar 18 11:24:35 CST 2005

Thanks.

I open a transaction only when saving. But in this unusual case, "Save" is
an extended process involving calculations and several updates to various
tables. (Just what transactions were made for, I think.) Some of those
updates are simple inserts (sql), so offhand I don't see a need to buffer
those. I have them in TRY blocks and can rollback if necessary.



"Dan Freeman" <spam@microsoft.com> wrote in message
news:OP86Jt9KFHA.580@TK2MSFTNGP15.phx.gbl...
> The admonishment about buffering for transactions is because with dirty
> reads on unbuffered data, changes are committed as soon as they're made.
> Unless you want to hold a transaction open for an entire editing session,
> and I KNOW you know better.
>
> In other words, they just didn't say it very well. <g>
>
> I wrap transactions around tablupdate(). The main benefit, of course, is
> that ROLLBACK restores you to buffered/changed data so you can investigate
> which change caused the tableupdate() to fail. There are cases where
> another
> user's changes will make tableupdate() fail but it really doesn't matter
> to
> your current transaction, so you can write code to make tableupdate()
> succeed on a second attempt.
>
> Dan
>
>
> Paul Pedersen wrote:
>> Thanks, but there's not much there that's new to me, or particularly
>> helpful. I'm looking for practical guidelines on how long and involved
>> transactions can be, their effects on other users, what can go wrong
>> and why, that sort of thing.
>>
>> Also, it says "Transaction processing should never be used without
>> buffering," without saying why. I hate it when they do that.
>> Transactions appear to work fine without buffering, so...?
>>
>>
>>
>> "Mark" <Mark@discussions.microsoft.com> wrote in message
>> news:361E93EF-EB66-4350-B584-B74D355EAF25@microsoft.com...
>>> Hi Paul,
>>> The article below might give you some hints.
>>> http://support.microsoft.com/?id=129643
>>> hth
>>> Mark
>>>
>>> "Paul Pedersen" wrote:
>>>
>>>> It is not necessary to buffer a table for transactions to work.
>>>> Nevertheless, I usually buffer a table (or use a view), then if a
>>>> transaction is necessary, put the transaction only around the
>>>> TABLEUPDATE in
>>>> order to keep the transaction short.
>>>>
>>>> But I'm really guessing here. Am I approaching it in the best
>>>> manner? What
>>>> general rules are there for handling buffering and transactions?
>>>>
>>>> Also, I occasionally want a transaction to cover changes to several
>>>> tables,
>>>> with some calculation done during the process, and even a couple
>>>> nested transactions. The whole thing could take a few seconds (in a
>>>> multi-user environment). Is there anything specific I need to look
>>>> out for? Other than
>>>> the limit on transaction levels, I mean.
>
>

Dan
Fri Mar 18 14:07:48 CST 2005

If it works for you, rock on. <s>

Dan

Paul Pedersen wrote:
> Thanks.
>
> I open a transaction only when saving. But in this unusual case,
> "Save" is an extended process involving calculations and several
> updates to various tables. (Just what transactions were made for, I
> think.) Some of those updates are simple inserts (sql), so offhand I
> don't see a need to buffer those. I have them in TRY blocks and can
> rollback if necessary.

Paul
Fri Mar 18 21:18:45 CST 2005

Well, I HOPE it works (I'm sure I'll find out eventually, but I'd rather
know sooner than later), which brings me back to the original question! To
wit, is there something wrong with my logic, and what problems might I run
into?

But you and Ook have made a couple suggestions and observations, for which I
am grateful.



"Dan Freeman" <spam@microsoft.com> wrote in message
news:e6jppY$KFHA.2540@TK2MSFTNGP10.phx.gbl...
> If it works for you, rock on. <s>
>
> Dan
>
> Paul Pedersen wrote:
>> Thanks.
>>
>> I open a transaction only when saving. But in this unusual case,
>> "Save" is an extended process involving calculations and several
>> updates to various tables. (Just what transactions were made for, I
>> think.) Some of those updates are simple inserts (sql), so offhand I
>> don't see a need to buffer those. I have them in TRY blocks and can
>> rollback if necessary.
>
>

Thomas
Sat Mar 19 23:11:07 CST 2005

Paul Pedersen schrieb:
> Thanks.
>
> I open a transaction only when saving. But in this unusual case, "Save" is
> an extended process involving calculations and several updates to various
> tables. (Just what transactions were made for, I think.) Some of those
> updates are simple inserts (sql), so offhand I don't see a need to buffer
> those. I have them in TRY blocks and can rollback if necessary.

In vfp6 we ran into trouble using transactions on a save process that
was coded to include (yes, I know ... not my design<g>) BizObject checks
on the data to be saved and some optional protocols and/or audit trails.

Dunno if there still is a problem, but if you get chr(0) in your saved
tables, this can be one reason. But there are some other (even
documented at MS) ways to get invalid data.

my 0.02 EUR

thomas

Al
Mon Mar 21 14:52:36 CST 2005

As I am sure you know, it is very useful to use buffering and transactions
to insure
a complete transaction either occurs or not. In the old days we did this by
elaborate
locking schemes and they tended to limit the number of simutaneous users to
40 or so.

The fact that a transaction locks records and indexs can also limit users
becaues of the
time involved.
Our solution to this was to recognize that many of the updates (eg.
inventory) can
be regarded as "incremental" updates. That is, the inventory must be
incremented by 1 (or -1)
and it does not matter what the value is when this occurs. Someone else may
have added to
the inventory while you were doing your sale transaction (say from 5 to 12)
and you need to
adjust it by -3 (your sale) when you post. Since you don't care if you
adjust it before he does
(from 5 to 2) or after (from 12 to 9) it does NOT have to be part of the
transaction but just
has to be sure of occuring.
So we use transaction to insure the invoice posted (both header and detail)
and then adjust
each inventory item (could be any number) one at a time with reprocess set
to -1 (with a custom
escape routine) to insure that all item inventory is adjusted.
This means the transaction itself is very fast and solves the contention
issue

al


"Paul Pedersen" <no-reply@swen.com> wrote in message
news:eGcJxW9KFHA.2748@TK2MSFTNGP09.phx.gbl...
> Thank you.
>
> In my current case, I have some buffered tables and some not buffered. But
> the unbuffered ones are simple INSERTs, so there should be no conflict.
>
> I need to make a dozen or so updates in a transaction, but unfortunately,
> each of those involves several sub-updates. Each of these is an inventory
> adjustment where parts, components of parts, and components of components
> up to nine levels deep must adjusted. I'm trying to keep the transaction
> as short as possible but there's just no getting around some of this
> stuff. I'll have to keep REPROCESS fairly short, to avoid locking out
> other users for too long a time. And I hope I don't run into deadlocks...
>
> One thing I discovered, not surprising I guess, is that transactions do
> not hold across data sessions. If you start a transaction in one data
> session, pass some processing to another, then rollback in the first, the
> work done in the second does not roll back.
>
>
>
>
> "Ook" <usenet@nospam.zootal.nomorespam.com> wrote in message
> news:eR$$r38KFHA.3616@TK2MSFTNGP09.phx.gbl...
>>I always buffer tables/views in a transaction. Being in a transaction does
>> not take care of record locking conflicts, and if someone else locks the
>> record before you try to update it and you don't buffer/rlock, you will
>> get
>> record is in use responce just like you do if there is no transaction. A
>> transaction is not a replacement for normal record locking or buffering
>> strategies. I always buffer, and use tableupdate() in my transactions. If
>> tableupdate() fails, I then rollback and abort the process.I think the
>> biggest difference when using transactions is that you don't have to
>> issue a
>> tablerevert() if something fails, you just ROLLBACK.
>>
>> I read the article, and noticed this statement: "Because the transaction
>> locks all records involved, as well as the index for the tables involved,
>> other users cannot update the table at all while a transaction is in
>> progress. ". This is not entirely correct. Other users can indeed update
>> the
>> table while a transaction is in progress, they just can't access the
>> records
>> that are involved in your transaction, and I don't think they can update
>> indexed fields but I'm not sure. In our own application, we demonstrated
>> that while a checkout process has a transaction in place, other users are
>> able to check in, which updates the table involved in the transaction. We
>> also demonstrated that while the transaction is in place, other users
>> cannot
>> access the records of the client involved in the transaction. Be wary of
>> timing, because locking records/tables in a transaction for an extended
>> length of time can bring all of your users to a dead halt. A transaction
>> should be very fast, seconds at the most and if you have a lot of users,
>> seconds may be too slow.
>>
>>
>>
>>
>> "Paul Pedersen" <no-reply@swen.com> wrote in message
>> news:OrXguR3KFHA.592@TK2MSFTNGP10.phx.gbl...
>>> It is not necessary to buffer a table for transactions to work.
>>> Nevertheless, I usually buffer a table (or use a view), then if a
>>> transaction is necessary, put the transaction only around the
>>> TABLEUPDATE
>> in
>>> order to keep the transaction short.
>>>
>>> But I'm really guessing here. Am I approaching it in the best manner?
>>> What
>>> general rules are there for handling buffering and transactions?
>>>
>>> Also, I occasionally want a transaction to cover changes to several
>> tables,
>>> with some calculation done during the process, and even a couple nested
>>> transactions. The whole thing could take a few seconds (in a multi-user
>>> environment). Is there anything specific I need to look out for? Other
>> than
>>> the limit on transaction levels, I mean.
>>>
>>>
>>>
>>
>>
>
>

Paul
Mon Mar 21 15:35:33 CST 2005

You know, I was wondering about something like this, but didn't put a whole
lot of thought into it, and haven't heard of anyone else doing it.

But you've made this sound like quite an attractive idea! I could add a
field called "onhand increment" or something like that, and make whatever
changes to that field, outside the transaction and without worrying about
contention. Or perhaps have an "inventory changes" table, and just add
records to that, and let some other process handle the actual updating when
it gets around to it.

Thanks!



"Al Marino" <intelliform@NOTab12THISverizon.net> wrote in message
news:ujgwpflLFHA.3076@tk2msftngp13.phx.gbl...
> As I am sure you know, it is very useful to use buffering and transactions
> to insure
> a complete transaction either occurs or not. In the old days we did this
> by elaborate
> locking schemes and they tended to limit the number of simutaneous users
> to 40 or so.
>
> The fact that a transaction locks records and indexs can also limit users
> becaues of the
> time involved.
> Our solution to this was to recognize that many of the updates (eg.
> inventory) can
> be regarded as "incremental" updates. That is, the inventory must be
> incremented by 1 (or -1)
> and it does not matter what the value is when this occurs. Someone else
> may have added to
> the inventory while you were doing your sale transaction (say from 5 to
> 12) and you need to
> adjust it by -3 (your sale) when you post. Since you don't care if you
> adjust it before he does
> (from 5 to 2) or after (from 12 to 9) it does NOT have to be part of the
> transaction but just
> has to be sure of occuring.
> So we use transaction to insure the invoice posted (both header and
> detail) and then adjust
> each inventory item (could be any number) one at a time with reprocess set
> to -1 (with a custom
> escape routine) to insure that all item inventory is adjusted.
> This means the transaction itself is very fast and solves the contention
> issue
>
> al
>
>
> "Paul Pedersen" <no-reply@swen.com> wrote in message
> news:eGcJxW9KFHA.2748@TK2MSFTNGP09.phx.gbl...
>> Thank you.
>>
>> In my current case, I have some buffered tables and some not buffered.
>> But the unbuffered ones are simple INSERTs, so there should be no
>> conflict.
>>
>> I need to make a dozen or so updates in a transaction, but unfortunately,
>> each of those involves several sub-updates. Each of these is an inventory
>> adjustment where parts, components of parts, and components of components
>> up to nine levels deep must adjusted. I'm trying to keep the transaction
>> as short as possible but there's just no getting around some of this
>> stuff. I'll have to keep REPROCESS fairly short, to avoid locking out
>> other users for too long a time. And I hope I don't run into deadlocks...
>>
>> One thing I discovered, not surprising I guess, is that transactions do
>> not hold across data sessions. If you start a transaction in one data
>> session, pass some processing to another, then rollback in the first, the
>> work done in the second does not roll back.
>>
>>
>>
>>
>> "Ook" <usenet@nospam.zootal.nomorespam.com> wrote in message
>> news:eR$$r38KFHA.3616@TK2MSFTNGP09.phx.gbl...
>>>I always buffer tables/views in a transaction. Being in a transaction
>>>does
>>> not take care of record locking conflicts, and if someone else locks the
>>> record before you try to update it and you don't buffer/rlock, you will
>>> get
>>> record is in use responce just like you do if there is no transaction. A
>>> transaction is not a replacement for normal record locking or buffering
>>> strategies. I always buffer, and use tableupdate() in my transactions.
>>> If
>>> tableupdate() fails, I then rollback and abort the process.I think the
>>> biggest difference when using transactions is that you don't have to
>>> issue a
>>> tablerevert() if something fails, you just ROLLBACK.
>>>
>>> I read the article, and noticed this statement: "Because the transaction
>>> locks all records involved, as well as the index for the tables
>>> involved,
>>> other users cannot update the table at all while a transaction is in
>>> progress. ". This is not entirely correct. Other users can indeed update
>>> the
>>> table while a transaction is in progress, they just can't access the
>>> records
>>> that are involved in your transaction, and I don't think they can update
>>> indexed fields but I'm not sure. In our own application, we demonstrated
>>> that while a checkout process has a transaction in place, other users
>>> are
>>> able to check in, which updates the table involved in the transaction.
>>> We
>>> also demonstrated that while the transaction is in place, other users
>>> cannot
>>> access the records of the client involved in the transaction. Be wary of
>>> timing, because locking records/tables in a transaction for an extended
>>> length of time can bring all of your users to a dead halt. A transaction
>>> should be very fast, seconds at the most and if you have a lot of users,
>>> seconds may be too slow.
>>>
>>>
>>>
>>>
>>> "Paul Pedersen" <no-reply@swen.com> wrote in message
>>> news:OrXguR3KFHA.592@TK2MSFTNGP10.phx.gbl...
>>>> It is not necessary to buffer a table for transactions to work.
>>>> Nevertheless, I usually buffer a table (or use a view), then if a
>>>> transaction is necessary, put the transaction only around the
>>>> TABLEUPDATE
>>> in
>>>> order to keep the transaction short.
>>>>
>>>> But I'm really guessing here. Am I approaching it in the best manner?
>>>> What
>>>> general rules are there for handling buffering and transactions?
>>>>
>>>> Also, I occasionally want a transaction to cover changes to several
>>> tables,
>>>> with some calculation done during the process, and even a couple nested
>>>> transactions. The whole thing could take a few seconds (in a multi-user
>>>> environment). Is there anything specific I need to look out for? Other
>>> than
>>>> the limit on transaction levels, I mean.
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>