C# / SQL Server: how to recurringly lock / truncate / update table while allowing another user to access data seamlessly

user_14593836 0
Viewed 101

I'm a newbie with lock / update type question.

I have market-type data that requires constant truncate table & refresh.

If I have code that:

//...fetch some data into dtable1...
// Below: Truncates existing sql table 'table1' & then bulkcopies dtable1 into 'table1'

SqlConnection _con = new SqlConnection();

using (_con = new SqlConnection(servername))
{
   SqlCommand sqlTruncate = new SqlCommand("TRUNCATE TABLE table1;", _con);
   sqlTruncate.ExecuteNonQuery();
   _con.Close();
}

using (var bulkCopy = new SqlBulkCopy(_con.....)
{
    bulkCopy.BulkCopyTimeout = 500;
    bulkCopy.DestinationTable = "table1";
    bulkCopy.WriteToServer(dtable1);
}

How/where do I put locks or something around the Truncate & Bulkcopy so another user can call the 'table1' seamlessly at anytime...so I'd imagine if Truncate AND Bulkcopy are not done yet, user calling data gets prior 'table1' data & user calling data afterwards gets new data without data ever being missing or partial due to in between update?

Thank you!

sql-server c#
2 Answers

I write trade systems now for about 22 years with databases that are terabytes in size, here are my 2 cents.

  1. set the transaction snapshot and make sure you have a fast temptdb

  2. partition the tables reducing the lock scope and set past partitions to readonly so the database knows that "past" data is stable.

  3. Set the connection applications connection string ApplicationIntend to readonly for those that read, and read-write to those that update data

  4. Remove indexes not needed, pad those indexes that you do need

  5. Make sure your indexes are sorted in a way that new rows naturally are appended, think of an index as a table, inserting and maintaining order in the indexes "data table" may actually take longer than inserting the actual data.

  6. Make sure you use the right data types, complaining 2 data types that are not 100% the same will cause SQL to create a cast function and that dramatically slows down activity.

  7. In your code buffer data and use the buffered data as much as possible

  8. Record all queries using profiler and make sure you are not generating parameters that do not match data types (see 6), some frameworks will use VarChar(max) or NVarchar(max) and that's going to be not intended.

  9. make sure you're SQL is not joining tables you do not need, a join is a lock, locking is worse than storing too much data, find a balance.

  10. think out of the box, sometimes you need to partition your data horizontally and store it on a 2nd server, especially if not related, say Nasdaq and Hongkong.

  11. Use a different database for reporting, say a read-only snapshot for anything not critical. you can use backup files as read-only databases as well, not sure you know this.

  12. as far as ETL dump data in a temp table and move the data from the "landing zone" to the "live zone", normal ETL pattern applies, google "sliding windows" for large volumes

user_80003820

One way of doing this is to create three copies of the table with the same structure.

--Bulk loads go into this table
CREATE TABLE Staging.table1(A INT PRIMARY KEY,B INT,C INT);

--Selects run against this table
CREATE TABLE dbo.table1(A INT PRIMARY KEY,B INT,C INT);

--Expired rows are switched out to here
CREATE TABLE Expired.table1(A INT PRIMARY KEY,B INT,C INT);

The bulk load goes into the staging table. When that is completed run something like the below

SET XACT_ABORT ON
BEGIN TRAN

ALTER TABLE dbo.table1
SWITCH TO  Expired.table1
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE));

ALTER TABLE Staging.table1
SWITCH TO dbo.table1

TRUNCATE TABLE Expired.table1

COMMIT

This moves the rows from the main table to the expired table, and from the staging table to the main table as a metadata only operation with no physical data movement of rows.

The ALTER TABLE ... SWITCH does require a schema modification lock on the table but the operation is very quick so once it manages to get hold of this lock it shouldn't hold it for long.

The schema modification lock is incompatible with concurrent reads though. The above low priority lock option allows new reads to come in and jump the queue ahead of the ALTER TABLE transaction instead of blocking them.

If you have a constant flow of reading transactions the ALTER TABLE may be blocked indefinitely however so you can consider changing the MAX_DURATION and ABORT_AFTER_WAIT options if you want it to be guaranteed to succeed eventually.

user_732260
Related