From: Michel Levy
To: rupesh sharma
Visual FoxPro 9 SP2
Databases, Tables and SQL Server
Tuesday, December 25, 2012 12:56:08 PM
This message was rated by:
# Ratings: 2
> hi all,
> i have developed some software as per client requirement but i did not use any transaction.
> i want to use is there any need to use transaction in multiuser application or not?
> now i am using sql as database and before this i was using vfp database. is there need in sql too? please guide me.
> thanks in advance.
> Rupesh Kumar Sharma
> Dhanisha Infotech
> Jaipur, India
transaction should be used in all case, even in a single user mode. In the single user mode, it is not critical if you don't transact, because you may substitute the transacation by some piece of code.
As soon as you start in a multiuser mode, transaction is needed, even when a single table is modified.
What is a transaction? what is it intended to? how does it run on a real RGDB as SQL Server?
at a first glance, a transaction isolate a batch of commands on data (manipulations and/or definitions) in an isolated process. Isolated means that all event external to the transaction may never know what is in the transaction nore be reached by what run inside the transaction, and all processes inside the transaction may never be reached by external events or processes nore know whatever about thes external processes. Transaction is ISOLATED.
But more important is that all processes inside a transaction will either all success, either all fail. Transaction is ATOMIC.
And transaction is also CONSISTENT: it will never let the data in an inconsitent state, as child tables with some lines no referencing to parent lines in parent tables. If some object depends on others, they will be always in correspondance.
And also, a transaction is DEFINITIVE: when it is commited, data are really written on the physical support, in a permanent way.
We say that a transaction is ACID (Atomic, Consistent, Isolated, Definitive).
In a SQL Server, a transaction always internally run through 12 steps involving data files, log files, and RAM. (you may look at
, that's the slideshow I give at our meeting about it).
In a SQL Server, all is allways inside a transaction
. it may be explicit (BEGIN TRANSACTION) or implicit. If you don't ask for an explicit transaction in your VFP code, SQL Server will create a transaction when it receives your commands (most often when it creates its internal thread on your connection request). If the connection is broken with success (i.e. not accidentally), then the transaction is committed. If an accidental end of connection occurs, then your transaction is rollbacked.
So, if you open a connection and let it open for use in all your app, you need to explicitely begin and commit your transactions inside this connection. If you open a connection for each request on the server and close this connection as soon you have processed the job, then you may rely on implicit transactions on the server.
Of course, best practice is to open a transaction for each batch of request having some consistencty, explicitly begin and commit or rollback the transaction, and close the connection. That is what we call disonnected mode. open connection, select some data, close connection. modify data on client side, open a new connection, begin a new transaction, send your data, commit or rollback, close connection.
Posted by rupesh sharma @ 12/25/2012 7:35:21 AM
Posted by anand kulkarni @ 12/25/2012 8:32:38 AM
Posted by David Mustakim @ 12/25/2012 11:59:07 AM
Posted by anand kulkarni @ 12/25/2012 12:11:54 PM
Posted by Anil Sharma @ 12/25/2012 10:06:06 AM
Posted by David Mustakim @ 12/25/2012 11:45:25 AM
Posted by Anil Sharma @ 12/25/2012 12:50:09 PM
Posted by Rick Hodgin @ 12/25/2012 12:07:20 PM
Posted by Michel Levy @ 12/25/2012 12:56:08 PM
Posted by rupesh sharma @ 12/25/2012 4:13:08 PM
Posted by mk sharma @ 12/25/2012 1:05:19 PM
Posted by Michel Levy @ 12/25/2012 1:57:18 PM
Posted by mk sharma @ 12/25/2012 2:29:14 PM
Posted by David Mustakim @ 12/25/2012 3:06:48 PM
Posted by Michel Levy @ 12/25/2012 3:34:37 PM