The Lost Update Problem in Concurrent Transactions

The Lost Update Problem in Concurrent Transactions
Rate this post

The lost update problem occurs when 2 concurrent transactions try to read and update the same data. Let’s understand this with the help of an example.

Suppose we have a table named “Product” that stores id, name, and ItemsinStock for a product.

It is used as part of an online system that displays the number of items in stock for a particular product and so needs to be updated each time a sale of that product is made.

Read More

Counting references to a record in a table via Foreign Keys

Counting references to a record in a table via Foreign Keys
1 (20%) 1 vote

I have recently needed to solve the task for my own purpose: to calculate the number of external records linked by a foreign key for each record in a table (File). The task was solved for the specific structure of the File table, but if necessary, the solution can be reworked to a universal one.

I’ll clarify that the solution was developed for an unloaded database, without millions of records and an every minute update, so there was not much concern about the performance.

Read More

Configuring Database Mail Notifications in MS SQL Server

Configuring Database Mail Notifications in MS SQL Server
Rate this post

Introduction

Often, there is a need to somehow inform administrators about the problems with a server. Notifications are generally divided into 2 types:

1) real-time notifications, i.e. those that must come immediately when a problem occurs

2) delayed notifications, i.e. those that come after a fairly long time (more than 1 hour) after a problem occurs. Read More

Synchronizing database structure between applications

Synchronizing database structure between applications
Rate this post

Anyone who has ever developed applications that use a database has probably faced the problem of updating the database structure when the application is deployed and updated.

The most common approach is to create a set of SQL scripts to modify the database structure from version to version. Of course, there are paid tools, but they do not always solve the problem of full automation of the update.

Read More

Understanding Dirty Read Problem with SQL Server

Understanding Dirty Read Problem with SQL Server
4.4 (88.57%) 7 votes

One of the most common problems that occur while running concurrent transactions is the Dirty Read problem. A dirty read occurs when one transaction is permitted to read data that is being modified by another transaction which is running concurrently but which has not yet committed itself.

If the transaction that modifies the data commits itself, the dirty read problem doesn’t occur. However if the transaction that modifies the data is rolled back after the other transaction has read the data, the latter transaction has dirty data that doesn’t actually exist. Read More

Implementing OOP Inheritance in Classes that work with SQL and Entity Framework

Implementing OOP Inheritance in Classes that work with SQL and Entity Framework
2.5 (50%) 2 votes

This article is about creating a data model that would nicely fit SQL and contain “proper” OOP inheritance. I must say that I encountered this problem at various times on different projects, and I solved it in different ways. The names of the approaches are taken from the terminology that has developed on the relevant projects.

Read More

More about Introduction of Time zones in long-lived Project

More about Introduction of Time zones in long-lived Project
Rate this post

Some time ago, we started to adapt the system to the new market that requires support for time zones. Initial research was described in the previous article. Now the approach has slightly evolved under the influence of realities. This article describes the problems encountered during the discussions and the final decision that is implemented.

Read More

Introducing Common Table Expressions in SQL Server

Introducing Common Table Expressions in SQL Server
Rate this post

Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.

Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.

Read More

Working with SQL Server in Hybrid Cloud Scenarios. Part 2

Working with SQL Server in Hybrid Cloud Scenarios. Part 2
3 (60%) 2 votes

As a rule, impersonal information is stored in a public cloud, and the personalized part – in a private cloud. The question thus arises – how to combine both parts to return a single result at a user’s request? Suppose there is a table of customers divided vertically. The depersonalized columns were included in the table located in Windows Azure SQL Database, and columns with sensitive information (e.g., full name) remained in the local SQL Server. Both tables must be linked by the CustomerID key. Because they are located in different databases on different servers, the JOIN statement will not work. As a possible solution, we have considered the scenario, when the linkage was implemented on the local SQL Server. It served as a kind of entry point for the applications, and the cloud-based SQL Server was set up on it as a linked server. In this article, we will consider the case when both, the local and cloud servers, are equal in terms of the application, and the data merging occurs directly in it, i.e. at the business logic level.

Read More

Working with SQL Server in Hybrid Cloud Scenarios

Working with SQL Server in Hybrid Cloud Scenarios
4 (80%) 2 votes

A hybrid cloud is a fairly attractive model when implementing cloud computing in enterprise information systems since this approach combines the advantages of public and private clouds. On the one hand, it is possible to flexibly attract external resources when needed and reduce infrastructure costs. On the other hand, full control over data and applications that the enterprise does not want to outsource remains. However, in such a scenario, we inevitably face the task of integrating data from various sources. Suppose there is a table with customers, which is vertically divided into two parts. The depersonalized part was allocated in a public cloud, and the information personalizing the customers remained in a local database. For holistic processing inside the application, you need to combine both parts by CustomerID. There are various ways to do this. Conventionally, they can be divided into two large categories: data aggregation at the on-premise database server level which, in this case, will be a single sign on for accessing local and remote data, and data aggregation within the business logic. This article will consider the first approach.

Read More