Christof, I'm going to have to have a strong coffee now and a rest after reading that last post!!! The old brain cells have refused to take in all the info in one swoop!
Dave -----Original Message----- From: ProFox [mailto:[email protected]] On Behalf Of Wollenhaupt, Christof Sent: 02 March 2016 08:30 To: [email protected] Subject: Re: SQL Server Transactions Lots of variables here... First of all, in VFP you can set SQL Server transactions to manual or automatic. The default is automatic, so each SQL batch sent from VFP is automatically committed. If you change to manual, you need to either commit or rollback on your own. If you don't, there's another setting in VFP that defines whether closing a connection with a transaction performs an implicit commit or rollback. Both settings are changed with SQLSETPROP. the name of the first setting is "Transactions", the name of the second one is "DisconnectRollback". Furthermore, you can start transactions in VFP using BEGIN TRANSACTION, END TRANSACTION and ROLLBACK, and in SQL Server. Both transactions exist simultaneously and are independent of each other. You can commit on SQL Server and rollback in VFP without a problem. To complicate things, there are two ways to control transaction on SQL Server. You have SQLCOMMIT() and SQLROLLBACK() for the connection handle, or you can send the corresponding SQL Statements with SQLEXEC. Those are BEGIN TRANSACTION, COMMIT, and ROLLBACK. Nested transactions are handled differently. In VFP you can start a nested transaction. ROLLBACK will only undo changes of that nested transaction. You can rollback an inner transaction and commit the outer one. COMMIT in the inner transaction will only commit to the outer transaction. If you COMMIT on the inner and then ROLLBACK on the outer, nothing gets saved in VFP. Not so in SQL Server. Here there's only one level of transactions. You can start multiple transactions which increments a transaction count. However, ROLLBACK will always roll back all transactions at once and COMMIT is ignored unless you are on the outer transaction. What data you read is dependent on the isolation level of the OTHER transaction, not your own one. If another client either changes the isolation level or uses on of the query hints that change the isolation level for a single query then, yes, someone could read all those changes. Everyone else has to wait for you to finish the transaction, even for read operations. It's different if you were not talking about MS SQL Server (which I kind of assumed here). -- Christof --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
