Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
I am doing some preliminary research for an upcoming project.... 1
- Thread starter pzmgmb
- Start date
-
1
- #3
The basics of database transactions:
1. You start a transaction with BeginTransaction on the connection object.
2. You would then ideally only have INSERT and/or UPDATE operations in your code. You would be best not to have any SELECTs or other SQL commands that don't modify the state of the database in there -- they just slow things down.
3. Call Commit on the SqlTransaction object you got when you called BeginTransaction, if everything went OK and the data needs to be written to the database. Until you call Commit, the data is stored in the database's transaction log -- only when you Commit it is it actually written to the tables. In the meantime, locks are created to keep other users out of the area that you're changing.
4. Call Rollback on the SqlTransaction object if something went wrong and you don't want to write the info to the tables. It will be as if all the INSERT & UPDATE commands never happened.
5. You might be able to nest transactions, but probably not. Don't try it.
6. Make sure your code between the BeginTransaction and Commit is as short & sweet as possible. Do not do any lengthy operations like validating XML documents while inside a transaction. While you are in a transaction, other database users are encountering the locks that you created. The shorter your transaction times, the better performing your system will be as a whole.
Chip H.
____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
1. You start a transaction with BeginTransaction on the connection object.
2. You would then ideally only have INSERT and/or UPDATE operations in your code. You would be best not to have any SELECTs or other SQL commands that don't modify the state of the database in there -- they just slow things down.
3. Call Commit on the SqlTransaction object you got when you called BeginTransaction, if everything went OK and the data needs to be written to the database. Until you call Commit, the data is stored in the database's transaction log -- only when you Commit it is it actually written to the tables. In the meantime, locks are created to keep other users out of the area that you're changing.
4. Call Rollback on the SqlTransaction object if something went wrong and you don't want to write the info to the tables. It will be as if all the INSERT & UPDATE commands never happened.
5. You might be able to nest transactions, but probably not. Don't try it.
6. Make sure your code between the BeginTransaction and Commit is as short & sweet as possible. Do not do any lengthy operations like validating XML documents while inside a transaction. While you are in a transaction, other database users are encountering the locks that you created. The shorter your transaction times, the better performing your system will be as a whole.
Chip H.
____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
- Thread starter
- #4
ThatRickGuy
Programmer
Chip is the man. an excellent description. In addition I would like to say that keeping your connection object open as little as possible is also a great practice.
Open Connection
Get Data
Close Connection
Use Data
By only opening your connections for the short time frame you use them you reduce memory and network overhead. You also reduce the likely hood of hitting locked records (as Chip mentioned) and with some proper coding you can work with a much smaller number of concurrent connection licenses.
-Rick
----------------------
Open Connection
Get Data
Close Connection
Use Data
By only opening your connections for the short time frame you use them you reduce memory and network overhead. You also reduce the likely hood of hitting locked records (as Chip mentioned) and with some proper coding you can work with a much smaller number of concurrent connection licenses.
-Rick
----------------------
- Thread starter
- #6
- Status
