I believe that this is indeed a design flaw, albeit one that is not specific to SQL Server 2016, as all other existing implementations of temporal tables (as far as I know) have the same flaw. The problems that can arise with temporal tables because of this are fairly severe; the scenario in your example is mild compared to what can go wrong in general:
Broken foreign key references: Suppose we have two temporal tables, with table A having a foreign key reference to table B. Now let's say we have two transactions, both running at a READ COMMITTED isolation level: transaction 1 begins before transaction 2, transaction 2 inserts a row into table B and commits, then transaction 1 inserts a row in table A with a reference to the newly added row of B. Since the addition of the new row to B was already committed, the foreign key constraint is satisfied and transaction 1 is able to commit successfully. However, if we were to view the database "AS OF" some time in between when transaction 1 began and when transaction 2 began, then we would see table A with a reference to a row of B that does not exist. So in this case, the temporal table provides an inconsistent view of the database. This of course was not the intent of the SQL:2011 standard, which states,
Historical system rows in a system-versioned table form immutable
snapshots of the past. Any constraints that were in effect when a
historical system row was created would have already been checked when
that row was a current system row, so there is never any need to
enforce constraints on historical system rows.
Non-unique primary keys: Let's say we have a table with a primary key and two transactions, both at a READ COMMITTED isolation level, in which the following happens: After transaction 1 begins but before it touches this table, transaction 2 deletes a certain row of the table and commits. Then, transaction 1 inserts a new row with the same primary key as the one that was deleted. This goes through fine, but when you look at the table AS OF a time in between when transaction 1 began and when transaction 2 began, we'll see two rows with the same primary key.
Errors on concurrent updates: Let's say we have a table and two transactions that both update the same row in it, again at a READ COMMITTED isolation level. Transaction 1 begins first, but transaction 2 is the first to update the row. Transaction 2 then commits, and transaction 1 then does a different update on the row and commits. This is all fine, except that if this is a temporal table, upon execution of the update in transaction 1 when the system goes to insert the required row into the history table the generated SysStartTime will be the start time of transaction 2, while the SysEndTime will be the start time of transaction 1, which is not a valid time interval since the SysEndTime would be before the SysStartTime. In this case SQL Server throws an error and rolls back the transaction (e.g., see this discussion). This is very unpleasant, since at the READ COMMITTED isolation level it would not be expected that concurrency issues would lead to outright failures, which means that applications are not necessarily going to be prepared to make retry attempts. In particular, this is contrary to a "guarantee" in Microsoft's documentation:
This behavior guarantees that your legacy applications will continue
to work when you enable system-versioning on tables that will benefit
from versioning. (link)
Other implementations of temporal tables have dealt with this scenario (two concurrent transactions updating the same row) by offering an option to automatically "adjust" the timestamps if they are invalid (see here and here). This is an ugly workaround, as it has the unfortunate consequence of breaking the atomicity of transactions, since other statements within the same transactions will not generally have their timestamps adjusted in the same way; i.e., with this workaround, if we view the database "AS OF" certain points in time then we may see partially-executed transactions.
Solution: You've already suggested the obvious solution, which is for the implementation to use the transaction end time (i.e. the commit time) instead of the start time. Yes it is true that when we're executing a statement in the middle of a transaction, it is impossible to know what the commit time will be (as it is in the future, or might not even exist if the transaction were to be rolled back). But this doesn't mean the solution is unimplementable; it just has to be done a different way. For example, when performing an UPDATE or DELETE statement, in creating the history row the system could just put in the current transaction ID instead of a start time, and then the ID can be converted to a timestamp later by the system after the transaction commits. There is no need to go into an infinite regression of then recording the time that the timestamp was filled in or anything like that.
In the context of this sort of implementation, I would suggest that prior to the transaction being committed, any rows it adds to the history table should not be user-visible. From the user perspective, it should simply appear that these rows are added (with the commit timestamp) at the time of the commit. In particular, if the transaction never successfully commits then it should never appear in the history. Of course, this is inconsistent with the SQL:2011 standard which describes the insertions to the history (including timestamps) as occurring at the time of the UPDATE and DELETE statements (as opposed to the time of the commit). But I don't think this really matters, considering that the standard has never been properly implemented (and arguably cannot ever be) due to the problems described above, which do not seem to be addressed anywhere in the standard.
From a performance standpoint, it might seem undesirable for the system to have to go back and revisit history rows to fill in the commit timestamp. But depending on how this is done, the cost could be quite low. I'm not really familiar with how SQL Server works internally, but PostgreSQL for instance uses a write-ahead-log, which makes it so that if multiple updates are performed on the same parts of a table, those updates are consolidated so that the data only needs to be written once to the physical table pages -- and that would typically apply in this scenario. In any case, it seems like a small price to pay for having temporal tables that can preserve database consistency and transaction atomicity and also handle concurrent transactions without breaking -- when we consider that with existing implementations the system can never ensure consistency and you have to choose between atomicity and (reliable) concurrency.
Of course, since (as far as I know) this kind of system has never been implemented, I can't say for sure that it would work -- maybe there's something I'm missing -- but I don't see any reason why it couldn't work.
20160707 11:04:58
and now you update all rows with that timestamp. But this update also runs for a few seconds and finishes at20160707 11:05:02
, now, which timestamp is the correct end of the transaction? Or assume you usedRead Uncommited
at20160707 11:05:00
, and got rows returned, but laterAS OF
doesn't show them.