DEV Community: Ankita

SELECT FOR UPDATE and its behavior with foreign keys in PostgreSQL.

Ankita — Sat, 22 Oct 2022 08:42:15 +0000

The general approach followed by developers is to pre-acquire the lock(FOR UPDATE)on the data sets that are being updated/deleted concurrently.

This approach may be considered to solve concurrency problems occurs due to concurrently running transactions. if you are confused that what is concurrency problems? then check out my blog concurrency in dbms.

To start with a simple scenario, let us consider the following example with two tables and let us assume that an application is running concurrent update operations on the parent table.


CREATE TABLE parent(id INT PRIMARY KEY, balance INT);

CREATE TABLE child(id INT REFERENCES parent(id) ON DELETE CASCADE, trx_timestamp TIMESTAMP);

A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete.The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.

ON DELETE CASCADE option is to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behaviour of the database server prevents you from deleting data in a table if other tables reference it.

Let's Insert some data in both table.

INSERT INTO parent VALUES (1,1000),(2,2000),(3,3000) ;

INSERT INTO child VALUES( 1 ,now()),( 2 ,now());

The following is an example transaction on the parent table which is using the FOR UPDATE clause to avoid any deadlock problems for this specific transaction.

Now, consider that I have an another concurrent transaction which tries to insert the data into the child table.

Following is the behaviour observed, when we run these two transactions concurrently.

As you see in the above output, the insert operation on the child table is in waiting state.
Even though the FOR UPDATE clause does not exist in the SESSION 2, the transaction would still remain in a waiting state. The reason for this waiting state is due to the PostgreSQL's FOREIGN KEY data validation process between the child and parent tables.

By default, when we use FOR UPDATE, it means that it is going to return the rows which are retrieved by the select statement by acquiring the lock on key columns.

In the above example we are actually updating the non key column(balance), by acquiring the lock on id column.

As we hold the lock on primary key column id, when we try to insert any value into the child table, then it will
try to acquire the SHARE lock on the parent table's id column. In this case, the SESSION 2 transaction will wait until the SESSION 1 transaction is complete.

In majority of the cases, we actually do not need to update any key columns,
rather we update only the non key columns like salary, phone number, address, etc. While updating the non key column values, we could force the behavior to not hold any lock on the key columns. To achieve this with SELECT FOR UPDATE in PostgreSQL, we could use the NO KEY option in the FOR UPDATE clause.

Now, let try the same example with the NO KEY option.

successfully inserted row in child table.

concurrency problems

Ankita — Sat, 22 Oct 2022 06:51:15 +0000

When multiple transactions execute concurrently in an uncontrolled or unrestricted manner, then it might lead to several problems.

Such problems are called as concurrency problems.

Dirty Read Problem
Unrepeatable Read Problem
Lost Update Problem
Phantom Read Problem

Dirty Read Problem

Reading the data written by an uncommitted transaction is called as dirty read.

This read is called as dirty read because there is always a chance that the uncommitted transaction might roll back later.Thus, uncommitted transaction might make other transactions read a value that does not even exist.This leads to inconsistency of the database.

Dirty read does not lead to inconsistency always.It becomes problematic only when the uncommitted transaction fails and roll backs later due to some reason.

Unrepeatable Read Problem

This problem occurs when a transaction read different values of the same variable in its different read operations even when it has not updated its value.

Lost Update Problem

This problem occurs when multiple transactions execute concurrently and updates from one or more transactions get lost.

This problem occurs whenever there is a write-write conflict.In write-write conflict, there are two writes one by each transaction on the same data item without any read in the middle.

Phantom Read Problem

This problem occurs when a transaction reads some variable from the buffer and when it reads the same variable later, it finds that the variable does not exist.

ERROR #1093 : You can't specify target table table_name for update in FROM clause

Ankita — Sat, 16 Jul 2022 17:28:09 +0000

In this blog, we will solve one MySQL question and understand errors while solving the problem.

Problem :
Delete Duplicate Emails

Table: Person



+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+

id is the primary key column for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id.

Input:
Person table:



+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

Output:



+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.

If We want No. of count which is having same email then we should use GROUP BY method.

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of id in each email".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Here we group each row that has the same email and find a count of the row with COUNT() function. email john@example.com occurs two times so we get a count as 2 and for bob@example.com we get a count as 1 because it occurs only one time.

Here email john@example.com has two ids 1 and 3. If We want to get minimum id from these two ids or minimum id from any no. of ids for particular email then we have to use MIN() function.

MySQL query should be like :

Here we get id 1 for email john@example.com because min(1,3) should be 1 and get 2 for bob@example.com because it has only one id that should be returned in MIN() function.

Now we have completed our major task in this problem. We want only id that is not in this min id because we want to keep the smallest id in our table and delete all ids that are greater than small id which has same email. so we first group row that has same email and then finds the smallest id from that to keep smallest id in table and delete all the row that has same email with id > small(id).

so the query will delete all id that is not equal to smallest id of particular email.

Oops...
We have got an Error.

ERROR #1093 : You can't specify the target table table_name for an update in FROM clause

What you can do is change the query to something like this below.

Okay, let me explain how the magic happens here.

You can not delete the rows from the same data source to which your sub-query refers. This above-mentioned query is a working, but it’s ugly for several reasons, including performance. Here nested subquery makes a temporary table. So it doesn’t count as the same table you’re trying to delete data from.

This is because your update could be cyclical. what if updating that record causes something to happen which made the WHERE condition FALSE? You know that isn’t the case, but the engine doesn’t. There also could be opposing locks on the table in the operation.

Trust Issues with setTimeout()

Ankita — Sun, 03 Jul 2022 13:22:02 +0000

what is setTimeout()?
Special asynchronous functions like setTimeout, setInterval are provided to JS by the environment it is currently running on. For example in case of Chrome JavaScript runs on the V8 engine, and Fire Fox uses SpiderMonkey as its JavaScript engine etc.

The setTimeout() method calls a function after a number of milliseconds. It sets a timer which executes a function or specified piece of code once the timer expires.

setTimeout with delay of 5000 milliseconds does not always wait for exactly 5000 milliseconds. There are trust issues.

Let's take one example and make it clear:

JavaScript is single threaded. Every line of code goes into call stack and gets executed.

date.getTime() method return current time in milliseconds. so I note the time when the execution of the program starts. our JS makes a timer for callback function of setTimeout and execution goes to the next line.

Here we use for loop to make more time in execution of our synchronous code. so when the call stack is busy in the execution of for loop our sweet timer gets expires but our call stack is busy in for loop so does execute callback function. our callback function is waiting in queue.

When the call stack finishes its loop execution it immediately runs our callback function. In the callback function, we get that time in milliseconds and log the total time of execution which may be greater than our delay time.

How much time setTimout takes, it's all dependence on our execution stack.

How to use API with Fetch API and Promises.

Ankita — Sun, 03 Jul 2022 06:04:33 +0000

A big part of working with JavaScript is learning how to connect to the API. As a new developer, at times you may just be asked to "play with some APIs"! To learn what they are and how to work with them. We're going to make a very simple advice generator web app with plain JavaScript that will retrieve information from an API and display it on the page.

PREREQUISITES

Basic knowledge of HTML and CSS.
Basic knowledge of JavaScript syntax and datatypes.
Basic knowledge of working with JSON and JavaScript objects.

GOALS

We are going to write from scratch random advice generator app that connects to Advice Slip API, retrieves the data with JavaScript, and displays it on the front end of a website.

It will look like this:

Quick overview:

What is an API?
APIs are mechanisms that enable two software components to communicate with each other using a set of definitions and protocols. An API is simply a medium to talk to program. For example, the weather bureau’s software system contains daily weather data. The weather app on your phone “talks” to this system via APIs and shows you daily weather updates on your phone.

Let's make it more clear with our vanilla JS DOM Element Example:
If You want HTML Elements in the JavaScript world you tell the browser to give that elements but how the browser will understand your language? it is done by using the querySelector API. Using document.querySelector API you can talk with the browser to give that particular element to me so I can make changes in it.

What is HTTP Request?
HTTP Requests are messages which are sent by the client or user to initiate an action on the server. HTTP works as a request-response protocol between a client and server.

Example: A client (browser) sends an HTTP request to the server; then the server returns a response to the client. The response contains status information about the request and may also contain the requested content.

You may be familiar with the concept of a CRUD app, which stands for Create, Read, Update, Delete. Any programming language can be used to make a CRUD app with various methods. A web API uses HTTP requests that correspond to the CRUD verbs.

HTTP Method : POST => Creates a new resource
HTTP Method : GET => Retrieves a resource
HTTP Method : PUT/PATCH => Updates an existing resource
HTTP Method : DELETE => Deletes a resource

Let's get started.
We want to read data from our Advice Slip API so we are going to use HTTP GET method.

Let’s begin by creating a index.html file that contains some basic information, in order to render a page.

If I open this index.html page, I should see black page.

Let’s now create our index.js file, which is referenced inside our html file.

Inside our index.js file fetch data from the advice slip API and log the response

What is Fetch API?
The Fetch API is a simple interface for fetching resources. Fetch allows us to make network request and handle responses easier than our old friend XMLHttpRequest(XHR). One of the main differences is that Fetch API uses Promises.

The fetch function takes one mandatory argument, which is the path to the resource you want to fetch and returns a Promise that resolves to the Response of that request.

What is Promise?
A JavaScript Promise object can be:

Pending
Fulfilled
Rejected

The Promise object supports two properties: state and result.

While a Promise object is "pending" (working), the result is undefined.
When a Promise object is "fulfilled", the result is a value.
When a Promise object is "rejected", the result is an error object.

When making an HTTP request as an asynchronous operation, fetch will not return any data. However it will return a response promise. When we log the response, it will show this Promise is in pending state. This means that the HTTP response we are expecting will get back eventually, but at the time of logging, this response was not ready to be logged.

Our Promise is in pending state, it can now transition into a fulfilled state if everything goes well or a rejected state if there’s an error while fetching. Once the Promise is settled, it can’t no longer change state.

Let’s now move back to our example and learn on how we can extract some data from this response Promise object. We will use the Promise.prototype.then method in order to attach a callback once our Promise has been fulfilled.

Now that we know our API response is working, we want to move on and actually get the body of the response. We want to call the json() method on the response in order to get the response body in json format. This operation is also asynchronous. The json() method actually returns a Promise, so we will need to create a Promise chain.

We will pass the value we receive from the first Promise into our chain in order to do some operations.

Once you refresh your browser and check your logs, you will see a object with key that contain attributes.

Now we can add it to our html element and get advice in front end.
we have used window.onload it is used to launch certain functions whenever a web page is loaded. Here we are loading advice when web page is loaded.