0

I am thinking of using a temporal table solution for one of our application, and want to read historic data while new data is being written. Can this cause table locks? I have millions of rows to query/maybe even insert/update/delete.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Emil
  • 281
  • 1
  • 2
  • 11
  • I’m voting to close this question because it will be better answered on https://dba.stackexchange.com/ – Ian Kemp Jul 06 '22 at 09:44

1 Answers1

1

Actually, all queries can produce table locks, but this is not specific only for the Temporal Tables. The temporal tables are as all other tables in the SQL Server.

A system-versioned temporal table is a type of user table designed to keep a full history of data changes to allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

You can add an index on them, you can boost the performance of them and etc.

Default isolation level on SQL Server is READ COMMITED. This isolation level will allow new inserts in the table. Update or delete can be a problem only if records that you want to affect are locked by another query.

Emin Mesic
  • 1,681
  • 2
  • 8
  • 18
  • Can transactions on main table be delayed/deadlock if someone does maintenance transaction on temporal table (like in https://stackoverflow.com/questions/53746197/cannot-delete-rows-from-a-temporal-history-table/53749664#53749664) ? Or transactions on base and temporal tables independent? – Michael Freidgeim May 24 '22 at 10:24
  • I’ve asked it as a separate question https://stackoverflow.com/questions/72362178/can-transactions-on-main-table-be-delayed-deadlock-when-maintenance-transaction – Michael Freidgeim May 24 '22 at 11:36