Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to solve the SQL SERVER Temporal Table and related strange problems. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

SQL SERVER 2016 has a new feature, Temporal table, whose main function is to keep a complete record of the changes in the data table and allow the change analysis of the data through this table.

Enabling temporal tables in CAMAIN produces a new table with the suffix history before the name of the original table.

Main functions:

Review all data changes and perform data forensics if necessary

Reconstruct the state of the data as ever before

Trend of calculation over time

Maintain a slowly changing dimension for decision support applications

Recover from unexpected data changes and application errors

Let's take a look at how temporal does this. We do DML operations in a table with temporal turned on to change the data in one row.

You can see that the data in the history table has started to record all the original data for this row before the data was modified.

And then we change the data here.

Then, when querying the history table, this time record the record status before the change.

It is important to know that when the data is inserted, it will be recorded in the fields in the original table, but there will be no records in the history table. Updating records and deleting records will record these operations. At the same time, there is also a way of MERGE is to split the operation into DELETE, UPDATE, INSERT to record the corresponding rows.

As a matter of fact, there are problems with such advanced things and new functions that began in 2016.

The above problems are often reflected in HOT table, which is mentioned in the official TECH of MICORSOFT, and some people have proposed solutions, of course, Microsoft does not think of this BUG.

The reason for this problem is that we are now doing a simulation, and we have two SESSION An and B, and we both have to operate on one of the tables, CACONTRACT.

Unfortunately, the sentence in An is written like this.

UPDATE CACONTRACT SET NUM = 2 WHERE ID IN (select NUM FROM CAMAIN where num = 3)

There seems to be no problem, but we can think of him as a transaction, if the processing time is 0.2ms, but B SESSION is faster, for example, he processes UPDATE CACONTRACT SET BC = 2 WHERE ID = '000003DJKHJ'

It seems that the two operations will not actually affect, but because the B operation is faster than the An operation, for example, the SysStartTime of ID 000003DJKHJ in the table is marked 10:15. 27997 subtle.

And A SESSION also needs to write my processing in sysstarttime after the operation is finished.

SysStartTime makes a change operation, changing the SysStartTime to 10 SysStartTime 1527987, which is obviously illogical. A record SysStartTime is updated earlier than the current time, which is logically impassable, so this operation An is called forbidden. Especially on very hot watches.

Just talking but not practicing fake tricks, let's simulate the above situation.

Follow me

1 set up a test table on your test system

REATE TABLE dbo.Orders

(

[OrderId] INT NOT NULL PRIMARY KEY CLUSTERED

, [OrderValue] DECIMAL (1951)

, [ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START

, [ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END

, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory))

GO

2 Please allow the following script

BEGIN TRAN

WAITFOR DELAY '00VOUGUR 15'

UPDATE dbo.Orders

SET [OrderValue] = [OrderValue] + 1

COMMIT TRAN

3 Please execute the following statement in another query window

INSERT dbo.Orders ([OrderId], [OrderValue])

VALUES (1,9.99), (2,9.99)

GO

SELECT * FROM dbo.Orders

GO

This means that the data is inserted later, but inserted first, and the update is done first, and the actual operation is later. This sequential system is naturally unbearable.

To solve such a problem:

1 make the DML operation on the error table fast enough to prevent this from happening (but in very complex systems, this is difficult)

2 in some operations, you want to use holdlock operations (in fact, it is artificially reducing the processing performance of the system)

3 in the very hot table, stop using this new Microsoft feature and wait for Microsoft to update the BUG in the new version.

This is the end of how to solve the SQL SERVER Temporal Table and related weird problems. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.