5

Chapter 6 of David Kroenke's Database Concepts: Database Administration

Is my understanding correct that in a database, simultaneous transactions don't really happen. Instead, it is a concurrent transaction that is happening fast that users think it is a simultaneous transaction?

The reason I ask is because I was thrown off by the question on my assignment asking how many CPUs are required to perform a simultaneous transaction. I answered only one cpu is required since it is really a concurrent transaction that is happening and not a simultaneous one?

Thoughts, anyone?

EDIT: ~ jcolebrand --

@gbn and @Mark,

I appreciate your input, however, I absolutely have no idea about what those terms are because this course I am currently taking is an introductory course to database management and my question was only discussed in the book in 1 paragraph and did not elaborate any further. I might be able to understand your responses in time, but now, i cannot just yet. Thanks though!

|improve this question
  • What are the respective definitions of Simultaneous Transaction and Concurrent Transaction according to the book and/or teacher? That will define the "right" answer in the given context. – pst Nov 29 '11 at 4:31
  • @ pst This is what the book says: When two transactions are being processed against a database at the same time, they are termed concurrent transactions. Although it might appear to the users that concurrent transactions are being processed simultaneously, this cannot be true because the CPU of the machine processing the database can only execute one instruction at a time. @NullUserException sure thanks! – momo Nov 29 '11 at 4:41
4

Your assignment question is poor and the expected answer probably incorrect. Feel free to send the lecturer our way to discuss!

From the book:

Explain the difference between concurrent transactions and simultaneous transactions. How many CPUs are required for simultaneous transactions?

I guess the expected differentiation is:

  • Concurrent transactions = multiple transactions active at the same time.
  • Simultaneous transactions = multiple transactions executing at the same time.

Naff question in my opinion.

Is my understanding correct that in a database, simultaneous transactions don't really happen

Transactions do of course run concurrently and simultaneously. The question is whether or not simultaneous commits can occur, which is what @gbn is addressing in his answer.

@gbn is correct that two simultaneous commits cannot occur if you consider the transaction to be committed at the point the WAL entry is hardened to disk. From a crash recovery perspective, the transaction is definitely committed at this point. In the event of a failure occurring, this transaction would be rolled forward (redo), not rolled back (undo). As two writes to disk cannot occur at the same time, we could argue that two simultaneous commits cannot occur.

However, you could also argue that a transaction is committed at the point the RDBMS is notified by the IO subsystem that the WAL write has completed successfully AND has released any locks it was holding for the duration of the transaction. Therefore, in a multi-cpu environment simultaneous commits can and will occur.

The mechanics of how this can occur for SQL Server is explained in Performance Tuning Waits and Queues, section titled 'Execution Model (simplified)'. Again for the SQL Server folk, some background reading on the WRITELOG wait type is useful to understand the concepts.

|improve this answer
  • The RDBMS has be notified separately by the WAL writes in the order they happened "Write 1, Ack1, Write 2, Ack2" or "Write 1, Write 2, Ack1, Ack2". – gbn Nov 29 '11 at 13:09
  • Nope. Think SQL Server, the batch will move to the waiter list while the IO work is done. When the IO completes, the batch moves from waiting to runnable, two could then be picked up by different schedulers at the same time. – Mark Storey-Smith Nov 29 '11 at 13:12
  • @MarkStorey-Smith based on your answer, it is most likely that what happens is a concurrent and not a simultaneous one? and thanks for the links, i will definitely read on it to understand and learn more. – momo Nov 29 '11 at 20:04
  • It's RDBMS dependent but I would say that in a multi-cpu environment both transactions and commits can occur concurrently and simultaneously. Heck, with multiple execution pipelines in modern CPU architectures, possibly even on a single CPU. – Mark Storey-Smith Nov 29 '11 at 20:11
3

Simultaneous won't happen because of the Write Ahead Log (WAL)

The WAL (a.k.a Transaction Log, Bin Log etc depending on RDBMS) forces all data changes to be serializable for later replay or rollback.

On a two core server two writes can be simultaneous because there may be no contention until the WAL write where one must happen before the other.

Higher up, latches and locks may serialize data changes on the same row/page/table.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.