The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database Transaction Wraps and Multiple Assignment

I'm designing a database that supports multiple assignment and thus possibly obviates the need for a transaction wrap. This works fine for updates and deletes, but for technical reasons I want to avoid implementing it for inserts. Can anyone give me an example of where it would be critcal to treat the addition of rows to say, two tables (versus updating)as an atomic transaction?
Freddy Hayek
Monday, January 10, 2005
"Can anyone give me an example of where it would be critcal to treat the addition of rows to say, two tables (versus updating)as an atomic transaction?"


Oops..  #2 didn't happen.  That might not be a dealbreaker however depending on what you are doing.
Almost Anonymous Send private email
Monday, January 10, 2005
Almost Anon,

Of course. As you note, it may not matter given the application. I'm looking for a specific example where it WOULD matter. (Like the example of transfering money between two accounts, which involves two updates)
Freddy Hayek
Monday, January 10, 2005
A transfer should be, at a minimimum, to row inserts.
1) insert the withdrawal transaction
2) insert the deposit transaction.

Of course, as you say, there may be balance updates as well but the two inserts should be in one transaction.

Monday, January 10, 2005
What is "multiple assignment"?  Is it like a journal that can be replayed on crash, or something?
i like i
Tuesday, January 11, 2005
Moving Stock from one place to another, anything transactional really where you have multiple lines per transactional document.

Unless the earlyness of the hour has meant I've missed the point.
Simon Lucy Send private email
Tuesday, January 11, 2005

Thanks... I see what you mean. Obviously I'm no expert in transaction management (Don't ask why I'm designing a dbms!)

i like i:

"multiple transaction" just means (I think!) that a client can send the server , say, 3 table inserts, and the server will execute all three "simultaneously" as a transaction - either all or none will be executed.

Simon Lucy:

No, you didn't miss the point - I didn't ask a good question. Let me attempt to reprhase:

My dbms implementation can support multiple transactions, even on inserts (so I can handle David's example above), but not what I call "dependent" inserts. Say I want to add one record to a table which is auto-keyed, get back the key, and then add 10 related records to another table. The second insert depends on the first insert (it needs to get the value of the key and replicate it 10 times for the second insert).

It seems this would need a server-side script, or a special syntax of some sort, or a conventional wrap.... so I guess my question is does anyone have an example of where this is critical? Apolgies if this is still kind of a silly question.
Freddy Hayek
Tuesday, January 11, 2005're talking about stored procedures, I think, when you say "transaction wraps".

Assuming you have a good reason for writing your own DBMS...

And the scenario is:

insert a customer record and get the new customer ID
insert an order record for the customer, using the customer ID

The 2 common ways of dealing with multiple dependent inserts are Sequences and auto-incrementing columns.

A sequence lets you basically get the ID upfront, then use the value in your insert (so you already know the ID before you need it for the 2 inserts).  Something like @customerID = next_sequence('Customer') could be the syntax.

Auto-incrementing columns rely on the DB itself to supply the value automatically on the insert, so you don't know the value upfront. Some databases provide a function to get the "last" value auto-created via an @Identity variable. 

In both approaches, you're typically lookng at a stored procedure when you use the functions, since IDs need to be guaranteed unique.  Or your DB could just implement a "special function" syntax in the SQL parser.

OK, I have to ask - why are you implementing your own DBMS?
Dave C Send private email
Tuesday, January 11, 2005
Dave C,

Thanks for the response. As to your question, I use an array based programming language. Everyone who uses an array language eventually writes his own rdms.
Freddy Hayek
Tuesday, January 11, 2005
>> Everyone who uses an array language eventually writes his own rdms <<

Why is that then?
David Aldridge Send private email
Tuesday, January 18, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz