small test on transaction atomicity  
Author Message
H5N1





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

Need help with small test on table in database with transaction. 
Could somebody show state of table after execution this code: 
CLOSE ALL 
 CLOSE DATABASES 
 OPEN DATABASE "c:\path to database\data1.dbc" SHARED 
 BEGIN TRANSACTION 
 	UPDATE data1!table1 SET name = 'BBBB BBBB BBBB BBBB BBBB' 
 	= Messagebox('END TRANSACTION') 
  
 END TRANSACTION
if in table1 before execution was 500K records with value "AAAA" and VFP process was killed after click OK in messagebox and before all trasaction bufers was stored in dbf file (2-3 seconds after click OK)  
I interested in "select count(*), name from data1!table1 group by name" output


Visual FoxPro2  
 
 
AndyKr





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

I don't understand your point here. If the transaction is not completed, then the data in the table will not be updated. So the result will be as if you had made no changes.

Doesn't matter if the process is killed or not, changes are not written to the underlying data until the END TRANSACTION is executed. If that line executes, then the changes are made, if it doesn't, for whatever reasoin, then the changes are NOT made.



 
 
Alex Feldstein





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

To add to Andy's answer, there is a ROLLBACK command too.

The idea of BEGIN TRANSACTION is mostly to be used when there are updates to several tables where you need to post all or nonel. So if for example the second one fails, then you issue a ROLLBACK and that undoes the updates to the first file.

In VFP in your example you do not need BEGIN TRANSACTION. You can easily accomplish that with table buffering and issuing a TABLEUPDATE() ( or TABLEREVERT() ). They are different that batch updates (several files) with BEGIN TRANSACTION.

It gets a little more complicated when the data is external (e.g. SQL Server) where you'd have to uissue a BEGIN TRANSACTION in the server. But that is not germaine to a pure VFP/DBF case and this discussion.

If unsure, you can check VFP's Help for:

Buffering

TABLEUPDATE()

BEGIN TRANSACTION

HTH


 
 
H5N1





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

Thanks for reply !

I have a dispute about transaction support in vfp, what's why I ask for such strange test. In our disput we found that actualy there are no transaction support in vfp. Reason - there are no vfp control on write process after "end transaction". after "end transaction" vfp process try to write buffer in dbf, if this process crashes or the computer is rebooted in this moment there will be inconsistent data in dbf file.


 
 
AndyKr





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

>> Reason - there are no vfp control on write process after "end transaction".

Ummm pardon me, but this is not a VFP problem.

Once the data is "committed" to disk (which is what the END TRANSACTION command does), VFP has no actual control over how (or even when) the operating system writes the data to the relevant physical file(s).

If your system crashes during the time interval between the instruction to commit the data to disk and the actual completion of the write operation (in other words while the data is in the operating system's memory cache) there is nothing that ANY application can do (not even SQL Server or Oracle!)

The absence of a persistent transaction log makes the tracing of such errors more difficult in VFP, but, as I say, I don't see how any application could deal with this issue - because the operation is not under the application's control at that point in time.

What makjes you think such a thing even COULD occur In all my years (which are many) I have never even heard of such a thing being an issue.



 
 
H5N1





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

Any server side rdbms can guarantee acid transaction. In oracle on commit server process write transaction number (SCN) in UNDO log. It is only 1 block and this is atomic operation. If this operation fail recovery process will rollback this transcation, if SCN was writen on disk (in REDO) than recovery process will roll this transaction on data files. Similar things happen in MSSQL.

>What makjes you think such a thing even COULD occur

Becouse hardware is not ideal, hardware fails happen even on specialized server hardware. I think similar problems occur on desktops. You can get inconsistent data in dbf on common network error or desktop reboot.


 
 
AndyKr





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

No I am sorry, but I have to disagree with you here. Once the OS has confirmed the write operation to the DBMS the transaction is closed and is deemed correct.

The scenario you describe in which the operating system crashes after the execution of a COMMIT but before the physical disk write (because the OS is caching changes)would not leave an open transaction on the DBMS.

Since the recovery operation will only detect errors in transactions that are NOT closed - unless you explicitly go back to a saved checkpoint and then roll forward again - you would see exactly the same result.

I absolutely agree that VFP does NOT support persistent transaction logs and checkpointing (but then it makes no claim to do so) but you cannot say that it does not support transactions because of that.

 



 
 
H5N1





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

You can try the simple test from first message and see that vfp will leave incosistent data in dbf.

>system crashes after the execution of a COMMIT but before the physical disk write (because the OS is caching changes)would not leave an open transaction on the DBMS.

I mean system crash after commit and after physical disk write process start. crash after write process start and before write process and.

In my defenition transaction must guarantee atomicy in any case, vfp can't give this guarantee.


 
 
AndyKr





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

>In my defenition transaction must guarantee atomicy in any case, vfp can't give this guarantee.

I understand, but what you are describing is, I believe, more properly referred to as "DURABILITY" not atomicity.

VFP is Atomic (if the process completes, then the transaction is either committed or rolled back as an atomic unit), but not Durable (because it no means of persisting information between sessions) which is why it is not a true ACID database.

The distinction is important because Atomicity and Durability are quite separate issues. There is no doubt that If durability is a requirement for you, then VFP is NOT a database you should be using - it is not, and has never made any claim to be, durable. But please, don't confuse things by saying that it is not atomic.

Having said that, I have to say that in most environments, Durability is the least significant of all of the ACID components and that hundreds of thousands of applications function perfectly well without issue. (Not saying that it isn't, or can't be an issue, just that it is rarely a critical one).



 
 
H5N1





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

Wikipedia:

Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction. Typically, all transactions are written into a log that can be played back to recreate the system to its state right before the failure. A transaction can only be deemed committed after it is safely in the log.

My opinion that atomicy closer describe this phantom:

Atomicity refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are. The transfer of funds can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won't be debited if the other is not credited as well.


 
 
AndyKr





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

>> My opinion that atomicy closer describe this phantom:

You are, of course, entitled to your opinion.

However, the definitions are quite clear, and nothing in the definition of Atomicity refers to, or implies, logging, or handling system failure (even the example you quote does not do that).

That is the function of the transaction log, and is described by property of Durability (...and will survive system failure...). The two are not interchangeable - which is presumably why there are two properties to begin with...

There is little value in arguing about this as clearly you are interpreting the definition differently from me. So if we can't even agree on the definition, there is no way we can agree on the conclusion.



 
 
H5N1





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

Hm ... I can't find connection between ACID and transaction log. RDBMS Interbase support acid transaction without any logging.

I (and many others) understand durability as guarantee what commited transaction will not disapired after system crash. Our durability definition says about "once the user has been notified of success" but we talk about moment before write process will send success notification.

Offcourse all above only my opinion.


 
 
AndyKr





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

>> Of course all above only my opinion.

Yes, looks like it is all a matter of opinion and that we are both right.

I found several different definitions for atomicity, some that matched your interpretation, (specifically stating that coping with hardware failure is part of Atomicity) and others that fitted with my conception (that Atomiocity relates to transaction status i.e. either "Complete => Committed" or "Incomplete => Rolled Back" ).

As far as I can see, it depends entirely on which database you are talking about and so it looks more like it really relates to the implementation and so means whatever the database manufacturer chooses it to mean. [shrug]



 
 
djun_kang





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

Can anyone help me with this problem I always get this problem at the time vfp commit .

AT least every tow or three days i get this error. I am really frustrated. I dont know how to handle this situation. I can repair the table but i need to know how to prevent this from happening again


 
 
Tamar E. Granor





PostPosted: Visual FoxPro General, small test on transaction atomicity Top

Make sure all the computers on your network have an uninterruptible power supply.

Tamar