transactions

Unit-Testing Databases

This past summer I was developing a basic ASP.NET/SQL Server CRUD app, and unit testing was one of the requirements. I ran into some trouble when I tried to test against the database. To my understanding, unit tests should be: stateless independent from each other repeatable with the same results i.e. no persisting changes These re...

Is it possible to share a transaction between a .Net application and a COM+ object?

I did some tests a while ago and never figured out how to make this work. The ingredients: COM+ transactional object (developed in VB6) .Net web application (with transaction) in IIS that... makes a call to the COM+ component updates a row in a SQL database Testing: Run the .Net application and force an exception. Result: The...

Transactional Design Pattern

Hi All I have a need to create a "transactional" process using an external API that does not support COM+ or .NET transactions (Sharepoint to be exact) What I need to do is to be able to perform a number of processes in a sequence, but any failure in that sequence means that I will have to manually undo all of the previous steps. In m...

Transactions best practices

How much do you rely on database transactions? Do you prefer small or large transaction scopes ? Do you prefer client side transaction handling (e.g. TransactionScope in .NET) over server side transactions or vice-versa? What about nested transactions? Do you have some tips&tricks related to transactions ? Any gotchas you...

Is SQL Server Bulk Insert Transactional?

If I run the following query in SQL Server 2000 Query Analyzer: BULK INSERT OurTable FROM 'c:\OurTable.txt' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', ROWS_PER_BATCH = 10000, TABLOCK) On a text file that conforms to OurTable's schema for 40 lines, but then changes format for the last 20 lines (lets say t...

Propagation of Oracle Transactions Between C++ and Java

We have an existing C++ application that we are going to gradually replace with a new Java-based system. Until we have completely reimplemented everything in Java we expect the C++ and Java to have to communicate with each other (RMI, SOAP, messaging, etc - we haven't decided). Now my manager thinks we'll need the Java and C++ sides to...

Looking for a SQL Transaction Log file viewer

If any of you have worked with a cool tool for viewing/querying the SQL Transaction logs, please let me know. This should show all the transactional sql statements which are committed or rolled back. For Database files, if it has some additional graphical capabilities like showing the internal Binary Tree structure of the indexes, that ...

How do distributed transactions work (eg. MSDTC)?

I understand, in a fuzzy sort of way, how regular ACID transactions work. You perform some work on a database in such a way that the work is not confirmed until some kind of commit flag is set. The commit part is based on some underlying assumption (like a single disk block write is atomic). In the event of a catastrophic error, you c...

Update schema and rows in one transaction, SQL Server 2005

I'm currently updating a legacy system which allows users to dictate part of the schema of one of its tables. Users can create and remove columns from the table through this interface. This legacy system is using ADO 2.8, and is using SQL Server 2005 as its database (you don't even WANT to know what database it was using before the att...

Nested SQL Server transaction performing cascade delete

Suppose I have a table called Companies that has a DepartmentID column. There's also a Departaments table that has as EmployeeID column. Of course I have an Employee table as well. The problem is that I want to delete a company, so first i have to delete all the employees for every departament and then all the departaments in the company...

Wrap an Oracle schema update in a transaction

I've got a program that periodically updates its database schema. Sometimes, one of the DDL statements might fail and if it does, I want to roll back all the changes. I wrap the update in a transaction like so: BEGIN TRAN; CREATE TABLE A (PKey int NOT NULL IDENTITY, NewFieldKey int NULL, CONSTRAINT PK_A PRIMARY KEY (PKey)); CREATE IN...

What are the problems of using transactions in a database?

From this post. One obvious problem is scalability/performance. What are the other problems that transactions use will provoke? Could you say there are two sets of problems, one for long running transactions and one for short running ones? If yes, how would you define them? EDIT: Deadlock is another problem, but data inconsistency migh...

JPA Multiple Transaction Managers

i have one applicationContext.xml file, and it has two org.springframework.orm.jpa.JpaTransactionManager (each with its own persistence unit, different databases) configured in a Spring middleware custom application. i want to use annotation based transactions (@Transactional), to not mess around with TransactionStatus commit, save, and ...

How to prevent an Insert query from enrolling into a Distributed Transaction?

I have a SQL Insert query inside a stored proc, for inserting rows into a linked server table. Since the stored proc is getting called within a parent transaction, this Insert statement tries to use a DTC for inserting rows into the linked server. I would like to avoid DTC from getting involved. Is there any way I can do that (like a ...

When do transactions become more of a burden than a benefit?

Transactional programming is, in this day and age, a staple in modern development. Concurrency and fault-tolerance are critical to an applications longevity and, rightly so, transactional logic has become easy to implement. As applications grow though, it seems that transactional code tends to become more and more burdensome on the scala...

SQL Server sys.databases log_reuse_wait question

I was investigating the rapid growth of a SQL Server 2005 transaction log when I found that transaction logs will only truncate correctly - if the sys.databases "log_reuse_wait" column is set to 0 - meaning that nothing is keeping the transaction log from reusing existing space. One day when I was intending to backup/truncate a log ...

Properly scoped transactions in Stored Procs

Suppose I have a stored procedure that manages its own transaction CREATE PROCEDURE theProc AS BEGIN BEGIN TRANSACTION -- do some stuff IF @ThereIsAProblem ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION END If I call this proc from an existing transaction, the proc can ROLLBACK the external transaction. BEGIN TRANSACTI...

EJB3 Transaction Propogation

I have a stateless bean something like: @Stateless public class MyStatelessBean implements MyStatelessLocal, MyStatelessRemote { @PersistenceContext(unitName="myPC") private EntityManager mgr; @TransationAttribute(TransactionAttributeType.SUPPORTED) public void processObjects(List<Object> objs) { // this method ...

IsolationLevel.RepeatableRead to prevent duplicates

I'm working on an application that is supposed to create products (like shipping insurance policies) when PayPal Instant Payment Notifications are received. Unfortunately, PayPal sometimes sends duplicate notifications. Furthermore, there is another third-party that is performing web-service updates simultaneously when they get updates...

When do transactions start when using (restful) rails

Is it the case that the entire restful verb is under a single all encompassing transaction? That is to say, if I raise a Error in the validation or callbacks at any point in the handling of a UPDATE, DELETE, or CREATE operation, is every database operation that I may have performed in previous callbacks also rolled back? Succinctly, does...