Skip to content

Database

Query Production DBs

Access

By default, developers does not have access to production DB. To get access, you need to run one of the two Temporary Access pipelines in DevOps. Note that when running, you have to give a reason why you need the access / what you plan to do. The access is revoked during the night:
- Temporary access prod-minority-owner group: Used by DevOps / Puneesh. Do not use.
- Temporary access to prod databases: This one gives you read and write access to the production DBs.

Problems

Be careful when running heavy queries against the production DBs. Queries can mainly have two kinds of bad effects on the production environment:

  1. Lock tables so that other queries has to wait. This is the most serious, since having a lock in the wrong table for even 10 seconds can block the system and have bad results.
  2. Use lots of resources (CPU, memory etc). There are two results, first is that high resource usage will result in a Datadog alert. In itself, it's no problem, except for the monitoring noise. Secondly, there might not be enough resources left for the normal DB operations resulting in slower queries for the services.

To avoid these issues, it's important to think through the query before you run it, and possibly adjust it to be less resource consuming.

To think about

  • It is much safer to run queries during non-peak hours (before US wakes up, roughly before lunch Swedish time).
  • SELECT queries
    • Even a SELECT COUNT(*) FROM table query can be slow on huge table!
    • If a select query runs for more than 10 seconds, cancel unless you are sure it doesn't have negative effects on other queries.
    • For slow queries, it can be a good idea to use NOLOCK on the query (SELECT .. FROM table WITH (NOLOCK) WHERE ..). With NOLOCK less/no locks will be taken, and it reduces the risk of affecting other queries (note that NOLOCK means the result returned might not be consistent, do not use if you need exact results and data is modified by other queries). See this for more discussion of NOLOCK SQL Server table hints – WITH (NOLOCK) best practices
  • INSERT / UPDATE / DELETE:
    • A query that runs for more than 1 second is already very slow and can have cascading effects.
    • If more than a few rows needs updating, careful planning is needed. Many rows modified can affect log files, query statistics, locking etc. One way to handle is to split the query into smaller steps, and only modify one or a couple of rows at a time.
  • CREATE / ALTER / DROP / other: Very dependent on the query. These can be both safe and very risky, depending on the exact query.

DB Design

With the increasing load and usage of our systems, the design of the database becomes even more important.

Concurrency

The more usage we have, and the bigger the tables, the more we need to pay attention to potential concurrency issues. Some cases are listed here:

Upsert / merge The built-in MERGE command to do update if row exist and insert otherwise is by default not safe from a concurrency perspective. To make things easy, it's best to always write the query safely, even if a low amount of concurrent queries is expected.

Use this pattern:

BEGIN TRANSACTION;

UPDATE dbo.table WITH (UPDLOCK, SERIALIZABLE) 
  SET ... WHERE PK = @PK;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END

COMMIT TRANSACTION;

Concurrent inserts Remember that the more users we have, the more likely it is that more than one thing happens concurrently. Unfortunately, there's no easy fit all fix.

  • In many cases it's possible to avoid problems by structuring the code in a smarter way (i.e. save to db before external service is called)
  • Avoid if at all possible any external (like Redis) locks in the code.
  • Code-local locks like mutex or C# lock is often not enough, since we run multiple instances of each service.
  • It is possible to use UnitOfWork / transactions, but currently this is quite limited. Also be aware that holding a transaction for a long time (even 1s) can have effects on other queries running.

Performance

Indexing Remember to think about if and what kinds of index is needed. With the high load possible, a missing index might take down the database! Any query that goes against a table that grows must use index.

Table sizes When designing tables, think through how much data they will contain after a while. Maybe it makes sense to only keep the latest X days of data in the table? If so, a useful pattern is to partition the table to be able to easily drop old data. Tables below 1GB / 1 million rows are too small to bother about, but when it goes above it start to become worthwhile to think more on this.

It is best to be proactive about big tables, it's much easier to migrate / change the table before it becomes huge. Therefor monitor tables sizes from time to time.

Development & Deployment

  • All changes to the database structure (DDL) should be done by scripts, committed to source control. Static configuration data in tables can also be committed. We handle these in the Minority.XXX.Db project in each area.
  • For heavy operations in the scripts, it can be better to run them "manually" on the DB instead of in the release pipeline. In this way there's less risk of timeout, easier to monitor and easier to cancel a long-running query or take other action. The changes should still be committed to source control. The script can use a conditional like IF NOT EXISTS ... THEN <do the operation> to handle both cases.

RunOnChange scripts

At the time of writing, the DbUp library does not support RunOnChange scripts, only RunOnce and RunAlways.

To address this limitation, we've developed a custom checksum journal that adds support for RunOnChange scripts. These SQL scripts will execute whenever a script changes, making them ideal for "schema-less" .sql files.

Currently, the custom checksum journal supports RunOnChange only for stored procedures, but it can be easily extended by adding more types to the configuration list available here: https://dev.azure.com/MAJORITY/Bank/_git/bank-platform?path=/Minority/Rebtel.Core.Infrastructure/Rebtel.Core.Infrastructure.DbUp/DatabaseUpgrader.cs&version=GBmaster&line=33&lineEnd=33&lineStartColumn=1&lineEndColumn=99&lineStyle=plain&_a=contents

Additional SQL script types that may be suitable for RunOnChange include:

  • Functions
  • Triggers
  • Types

Benifits of RunOnChange scripts for schema less changes:

  • Quicker to implement changes – no need to search for and copy existing stored procedures.
  • Improved git diffs during code reviews – makes it easy to spot the specific change.
  • Protected by git conflict resolution – when multiple people work on the same table, copying an existing stored procedure or altering the same file can be overlooked if a new file is created.
  • Eliminates the risk of accidentally copying the wrong file when making changes.

How to migrate stored procedures to RunOnChange

  1. Create a folder inside Migrations folder called StoredProcedures
  2. Copy all stored procedures from the production database
    • These can be exported from SSMS.
    • You will need to replace CREATE PROCEDURE with CREATE OR ALTER PROCEDURE. Note that CREATE PROCEDURE can be written in multiple ways (i.e. with multiple spaces).
    • These files might have a "bad" UTF-16LE encoding. dos2unix linux command line tool can fix easily.
  3. Create a new file for each stored procedure inside the Migrations/StoredProcedures folder
  4. Remove the existing stored procedures in /Migrations folder
  5. Its a good idea to also remove all file includes from the csproj, and replace with a single include:
      <ItemGroup>
    <EmbeddedResource Include="Migrations\**\*.sql" />
  </ItemGroup>
  6. Update the ownership checklist document that the area is migrated to new way.

Examples:

Wallet - https://dev.azure.com/MAJORITY/Bank/_git/bank-wallet/pullrequest/49289

Lithic - https://dev.azure.com/MAJORITY/Bank/_git/bank-lithic/pullrequest/49366 / Merged PR 49366: [MIB-11600] migrate to new sp structure · majority-dev/be-lithic@c3af0b7

Reporting - https://dev.azure.com/MAJORITY/Bank/_git/bank-reporting/pullrequest/49188

Phone-plan: https://dev.azure.com/MAJORITY/Bank/_git/bank-phoneplan/pullrequest/50075

Dispute - https://dev.azure.com/MAJORITY/Bank/_git/bank-dispute/pullrequest/50837