Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [concurrency]

For questions about issues arising when multiple users or processes concurrently access or modify shared information in a database system.

Filter by
Sorted by
Tagged with
-2 votes
0 answers
17 views

PostgreSQL - simulating long-running queries via pg_sleep to test application

I am currently testing concurrency in my Python web application via threading as well as coroutines / asyncio. I have a synchronous SQL Alchemy ORM (v1.3) session set up. The session is set up per ...
0 votes
0 answers
6 views

Serialisable Schedule but not View Serialisable

These are the definitions I have for Serialisable and View Serialisable. I know that View Serialisability => Serialisability and I want to find an example of a schedule that is Serialisable but ...
142 votes
1 answer
177k views

Postgres UPDATE ... LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't ...
-2 votes
0 answers
32 views

Can concurrent SQL statements interleave?

Lately, I've read a lot about database transactions and isolation levels but there's one simple question I simply cannot find a definite answer to: Consider a table with some arbitrary rows: CREATE ...
0 votes
1 answer
295 views

Postgres Search Path between connections

I'm a noob to Postgres, and am setting up a multi-tenant database using one schema per tenant. To target each tenant I need to set search_path accordingly. I'm worried about accidentally executing ...
0 votes
1 answer
555 views

How to prevent double booking of a room in this hotel booking database design?

I have two tables for hotel booking. One is rooms table where I save room type, hotelId and total number of rooms that the hotel has for that type. HotelId RoomTypeId RoomType TotalRooms 100 2 ...
1 vote
1 answer
767 views

Postgres Repeatable Read vs Selecting rows that are changed by another ongoing transaction

Let's say I have a set of select statements that query important field values in a for-loop. The goal is to make sure that the rows are not updated by any other transaction so that this set of selects ...
0 votes
1 answer
331 views

Transaction - Timestamp Ordering. When is aborted transaction restarted

(I don't know if this is the correct place to ask, since this is more like a theoretical question). I have a question about restarting aborted transactions. I don't know when / at which point should ...
7 votes
4 answers
945 views

Is my queue table implementation race condition safe?

Hello people smarter than me! I've created a sort-of-a-queue table system, but it seems too simple to be safe from race conditions. Am I missing something or is the following race condition safe? The ...
1 vote
1 answer
437 views

What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?

I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the ...
0 votes
1 answer
456 views

Is this atomic set and get in MariaDB / MySQL: `UPDATE t SET col = @my_var := col + 1 WHERE id = 123`?

I want to atomically update a single row and get its value. I discovered today that MariaDB / MySQL has this additional way of setting user variables using the assignment operator :=, described e.g. ...
0 votes
1 answer
64 views

Repeatable reads in case protection from duplicated inserts in POSTGRES

I would need to perform this kind of logic in a serializable way: account table has columns: 'accountName', 'location', 'worth', ... Assume no unique key exists. Some accounts are logically (according ...
1 vote
1 answer
1k views

mysql/aurora not immediately seeing results of committed transactions

In a system running against an AWS aurora-mysql database, the following (obviously extremely simplified) code runs. It's a highly concurrent system, multiple threads, multiple connections to db. The ...
1 vote
1 answer
60 views

Job history cleanup query being blocked

I'm running the below query every hour to cleanup job history in the sysjobhistory table for all jobs that execute every 10 seconds. The query aims to delete 100k rows of job history that is older ...
35 votes
5 answers
9k views

How do you test for race conditions in a database?

I try to write database code to make sure that it's not subject to race conditions, to make sure that I've locked the correct rows or tables. But I often wonder: Is my code correct? Is it possible to ...
1 vote
1 answer
124 views

What type of lock does MongoDB apply at the document level when reading or writing the document?

I’ve been reading the MongoDB documentation for hours, but I couldn’t find a clear and straightforward answer. When reading or writing a document in MongoDB, what type of lock (S, X, IS, IX) does it ...
0 votes
2 answers
116 views

Improve MySQL concurrent updates

Are there any suggestions to run the concurrent updates on a table (100+ concurrent updates on a specific row)? Same row, like updating the clicks count for an user. UPDATE `tbl` SET `cli`=`cli`+ 1,`...
-2 votes
2 answers
238 views

I've TABLOCKed a table. How can I read it?

I've TABLOCKed a table so I can run bulk inserts. How can I read it while it is still locked? Under normal circumstances, I would call this obviously impossible. Luckily, there is one key factor that ...
0 votes
0 answers
67 views

Redshift Serializable isolation violation error

Our setup: We have a Redshift cluster as our data warehouse. We have a Postgres OLTP DB from which a number of write-heavy tables are getting replicated into Redshift using DMS. We use dbt to then ...
0 votes
1 answer
46 views

Two queries to retrieve user profile and tweets, or join and get user data from first column?

Let's say I have two tables: users and tweets. I want to display a full user profile, and latest tweets by id. Is it better to fetch them in two separate, possibly concurrent/parallel calls? in JS it ...
1 vote
1 answer
451 views

Is there a way to have postgresql transactions be commited in the order they were started?

For some business reason I am running the query SELECT * FROM table WHERE updated_at > $1 AND user_id = $2;. Consider two transactions, A and B, that are updating rows in table: UPDATE table SET ...
0 votes
0 answers
11 views

How Innodb Concurrency Tickets working in sorting by mysql?

Innodb concurrency tickets how reduced when the sorting performing query running? is tickets applied for sorting or not? If applied how it was calculated by mysql and is there any performance issue ...
4 votes
1 answer
412 views

Read/Write Deadlock in SQL Server

We are facing deadlocks when a stored procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of the stored ...
0 votes
1 answer
174 views

How do two transactions with different levels of isolation interact with each other?

this is specifically for SQLserver: I want to know if I have a transaction with isolation level SERIALIZABLE and another one with READ UNCOMMITTED how will they interact concurrently? will the second ...
0 votes
2 answers
52 views

Concurrent delete old rows

I have table with user sessions: CREATE TABLE user_sessions ( id SERIAL PRIMARY KEY, user_id INT NOT NULL ); Each user can have only 5 sessions. if a user has 5 sessions and starts a new ...
0 votes
1 answer
141 views

Does read committed have atomic statements?

Let's say I have the following situation: T1: Update Student Set GPA=(1.1)*GPA Where sizeHS > 2500 T2: Set Transaction isolation Level Read Committed; Select Avg(GPA) From Student; Select Max(...
1 vote
1 answer
626 views

Deadlock in AFTER INSERT trigger

I have an AFTER INSERT trigger for table A that updates multiple rows in table B (incrementing a column in table B). Normally, the web server would insert multiple rows into table A using a single ...
14 votes
3 answers
29k views

How to perform conditional insert based on row count?

I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table: Table "public....
1 vote
2 answers
1k views

How can I insert into a Postgres table with a unique constraint without waiting on concurrent transactions?

I have a table like this in a Postgres database: CREATE TABLE tabwithunique ( id SERIAL PRIMARY KEY, key BIGINT NOT NULL UNIQUE, value BIGINT NOT NULL ); I want to design a statement that ...
16 votes
2 answers
7k views

Is the system column "ctid" legitimate for identifying rows to delete?

I have a table with hundreds of millions of rows that I need to delete data from. The existing indexes are the most efficient. I can however use the existing indexes to find the rows to delete by ...
0 votes
2 answers
781 views

Postgres long transaction holding advisory lock

Is it a bad idea to try to hold a transaction open indefinitely in order to hold an advisory lock? It appears to function well enough for our use case, however in performance testing the long ...
2 votes
0 answers
153 views

Purging large database tables

I know this has been asked few times in here, but my case is bit different. I would like to purge the data in from my SQL Server database of 700 GB and almost 1000 tables. I spent fair amount of time ...
2 votes
1 answer
866 views

Non-Repeatable Read vs Phantom Read - Real World Scenario

About the mentioned 2 Data concurrency problems below is what I understand. Non-Repeatable Read - is where within same transaction running the same query twice returns 2 different values for the same ...
12 votes
2 answers
19k views

Why am I getting a deadlock for a single UPDATE query?

I have two processes which execute code like this in parallel: begin; update foos set unread=false where owner_id=123 and unread=true; commit; This results in deadlocks. My understanding of what ...
7 votes
2 answers
479 views

Using a Table as a Queue without sp_getapplock/sp_releaseapplock

I have a list of commands I need to execute, all of which are contained within a table I've named myQueue. This table is a little unique in that some commands should be grouped together such that ...
1 vote
3 answers
1k views

DB Locks - How would one handle long transactions and maintain high concurrency?

We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle ...
0 votes
1 answer
2k views

Updates to the same table in concurrent sessions in SQL Server

Consider the following scenario: 10,000 users try to create a profile on our application concurrently at the same time and the data has to be inserted into the table, userProfile. The SP that handles ...
0 votes
0 answers
105 views

Check Existence Before Creating - Select, Insert, on conflict return

I've the following code that creates mapbox vector tiles and inserts them for storage. In the final code each xyz tile will be unique. Before the below code I would like to check the storage table to ...
1 vote
1 answer
843 views

Does MS SQL Server hold transactions in queue?

Is it possible for SQL Server to 'hold' transactions and then execute them in one go? Just some background: I have a client with a decently large SQL Server with their ERP databases and our scanning ...
2 votes
1 answer
730 views

If concurrent queries are made with ON CONFLICT DO UPDATE, will every update take place sequentially?

I have a need to insert into a table and ensure that one column is being incremented each time. My insert looks like: INSERT INTO my_tbl (id) VALUES ($1) ON CONFLICT (id) DO UPDATE SET total = my_tbl ...
25 votes
2 answers
17k views

Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished? I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until ...
1 vote
0 answers
237 views

Stored procedures are getting locked

I have some procedures which use SQL statements like these: OPEN SYMMETRIC KEY SymKey_Xyz DECRYPTION BY CERTIFICATE Certificate_Xyz SELECT DECRYPTBYKEY(column1),DECRYPTBYKEY(column1) from ABC CLOSE ...
user avatar
3 votes
0 answers
184 views

Missed row when running SELECT with READCOMMITTEDLOCK

I have a T-SQL code that delta-copies data from the source table (SrcTable) to the destination table (DestTable). The data is inserted into the source table by multiple sessions and copied to the ...
1 vote
2 answers
63 views

Concurrency Control B-Tree between Hash

I am new to transaction. When I read the book "Transactional Information Systems", it is mentioned that B-tree uses key-range locking to guarantee serializability. Is that true all accesses ...
3 votes
2 answers
1k views

1st process writes, 2nd one reads -- " SQLITE_BUSY: database is locked "

I have 2 processes that use the same Sqlite3 database. 1st one -- in NodeJs, is a program which constractly writes data into a database, with the frequency of around 1 write / 1 second. It only ...
0 votes
2 answers
969 views

Concurrently update counter with least amount of locking

Using Sql Server I need to have concurrent transactions update a counter, when they commit - but they must not block each other from updating the counter. Let's say the first started transaction takes ...
0 votes
1 answer
213 views

DB2: Working with concurrent DDL operations

We are working on a data warehouse using IBM DB2 and we wanted to load data by partition exchange. That means we prepare a temporary table with the data we want to load into the target table and then ...
2 votes
1 answer
1k views

Why SERIALIZABLE level does not lock the table for other session in SQL Server

I want to Prevent concurrency in my table for Delete, Insert, Update, and Select so I tried to use SERIALIZABLE for the table before transation, but that didn't lock the table for other processes! how ...
5 votes
1 answer
2k views

Is a serialization anomaly only possible with SUM/COUNT?

I have read Deeply understand Isolation levels and Read phenomena in MySQL & PostgreSQL and especially the part "Serialization anomaly in Postgres". I think I've understood the problem ...
0 votes
0 answers
78 views

concurrency in running query

I am using Postgres12 and DBeaver. I insert thousands of data using a procedure like this : do $$ declare ctr_max_ID integer; ctr_loop Integer := 1; begin loop exit when ctr_loop = ...

1
2 3 4 5
7