Concurrent Transactions vs Simultaneous Transactions

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!

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.

StackExchange Database Administrators Q#8519, answer score: 5