Transactions in VFP

[cvsubbarao]

I want some information regarding begin transaction and end transaction.

suppose I have a table called orders. I want to update some records in that table with in the begin transaction and end transaction like this

begin transaction
update orders set status = 'ia' where status = 'na'
end transaction

my problem is, if any of the record participates in the above transaction is previously locked by another user then what is the status of my transaction.

Is it wait to release all locks for all records before it commits the transaction or gives the error message?

 

[DSummZZZ]

All transactions or a single transaction after a BEGIN TRANSACTION must be completed successfully in order for the END TRANSACTION to complete. This includes updates in other tables you have begun, not just the currently selected one. Otherwise, you either get an error, or the opportunity to rollback or commit all transactions or the single transaction depending on which method of buffering you are using. If there are going to be multiple users accessing your tables, it would be a better choice in my opinion to do a row-by-row update instead of trying to update the entire table at once.

Dave S.

 

[cvsubbarao]

Hi Dave
Thanks for your reply.
I want some more information for this.
My order table contains already crossed half million records and I want update some thousand records at a time.
And also the order table is going increase day by day.
If I go one by one record at a time it is taking more time in a loop.
Let me know is there any procedure to update a batch of records in short time and check those records has already locked.

thanks

subba

 

[EdLeafe]

You have a couple of options. One is to use SET REPROCESS to control the number of times or seconds that VFP will retry the lock before failing. This way, and short-duration locks the batch encounters won't cause the whole update to fail. The other is to FLOCK() the file before the update. While this will avoid locking conflicts, it might prevent other processes from changing data that wouldn't otherwise conflict with the batch.

== Ed Leafe