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
0 votes
1 answer
53 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 ...
GionJh's user avatar
  • 113
1 vote
1 answer
54 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 ...
Marcus's user avatar
  • 59
1 vote
1 answer
64 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 ...
dragonborn's user avatar
0 votes
2 answers
70 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,`...
TheDataGuy's user avatar
  • 1,920
-2 votes
2 answers
148 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 ...
J. Mini's user avatar
  • 81
0 votes
1 answer
44 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 ...
wefad12292's user avatar
1 vote
1 answer
243 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 ...
DontBreakAlex's user avatar
0 votes
0 answers
45 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 ...
Steve Robinson's user avatar
0 votes
0 answers
10 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 ...
Kannan M's user avatar
0 votes
0 answers
25 views

SSTable loading concurrent exception

While loading sstables through SSTableLoader command, I am frequently facing this issue. java.util.concurrent.ExecutionException: org.apache.cassandra.streaming.StreamException: Stream failed ...
prasanna's user avatar
0 votes
1 answer
130 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 ...
Achraf Cheikh Mohamed's user avatar
0 votes
2 answers
35 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 ...
Inventor's user avatar
  • 111
0 votes
1 answer
126 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(...
pylopass's user avatar
0 votes
0 answers
96 views

Interaction between ReadConcern and ReadPreference in mongodb

Question 1. Is my understanding of the concepts correct? ReadConcerns: local - returns data from any single instance, and thus may return outdated state, or state that will be rolled back. majority - ...
Kit Isaev's user avatar
1 vote
1 answer
464 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 ...
Alvin's user avatar
  • 35
1 vote
2 answers
874 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 ...
Curtis Fenner's user avatar
0 votes
2 answers
543 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 ...
Christian Kruse's user avatar
0 votes
1 answer
473 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 ...
bornfree's user avatar
  • 101
2 votes
0 answers
150 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 ...
Anand Shrestha's user avatar
0 votes
1 answer
593 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 ...
Dhanuka Jayasinghe's user avatar
0 votes
1 answer
1k 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 ...
lifeisajourney's user avatar
0 votes
0 answers
98 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 ...
Tom Shelley's user avatar
1 vote
1 answer
726 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 ...
Abinash's user avatar
  • 11
2 votes
1 answer
627 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 ...
user15716642's user avatar
1 vote
0 answers
218 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 ...
Ashish Bisht's user avatar
0 votes
1 answer
636 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 ...
Don Draper's user avatar
3 votes
0 answers
175 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 ...
SerMal's user avatar
  • 31
0 votes
2 answers
877 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 ...
asgerhallas's user avatar
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 ...
مجتبی حکیمیان's user avatar
1 vote
2 answers
61 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 ...
GenGG's user avatar
  • 15
5 votes
1 answer
1k 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 ...
Martin Thoma's user avatar
0 votes
0 answers
76 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 = ...
padjee's user avatar
  • 177
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 ...
Kum's user avatar
  • 47
0 votes
1 answer
187 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 ...
user2323596's user avatar
1 vote
0 answers
381 views

Why would executing individual update statements on two unrelated tables each within their own transaction cause deadlock?

I've searched the stackoverflow network, but couldn't find anything similar to the scenario detailed below. It doesn't make sense how deadlock could occur, but we're seeing logs from the database ...
HeatZync's user avatar
0 votes
0 answers
207 views

Writing a ledger with high concurrency

I'm trying to achieve a high level of writes on a single account for a ledger, however, if there is a high level of concurrency, things start to slow down significantly. My current setup is basically ...
Luis's user avatar
  • 1
1 vote
1 answer
343 views

PostgreSQL triggered update function lock mechanism

I'm fairly new to database administration and PostgreSQL, and there's some things I'm not familiar with, even tho I've read a lot of the PostgreSQL documentation. Here is a simplified version of the ...
Damien's user avatar
  • 113
3 votes
2 answers
765 views

Inconsistent data when combining (LEFT) JOIN with SELECT FOR UPDATE

I have recently stumbled upon a seemingly odd behavior of SELECT ... FOR UPDATE when combined with (LEFT) JOIN. Here is the table structure as well as a scenario to reproduce the result: Table ...
auxdevelopment's user avatar
3 votes
3 answers
3k views

MySQL concurrent UPDATES

Say we have the following situation in an application using MySQL where a User can buy an Item and each Item has a single buyer but the Item's price may change. Pseudocode : BEGIN TRANSACTION ...
Iliana Kollia's user avatar
1 vote
0 answers
26 views

Consistency in serializable concurrent transactions

My DBS textbook says that if each transaction individually maintains consistency in the system, in the case of concurrent transactions, serializability ensures consistency. I am not able to understand ...
Kashish's user avatar
  • 11
0 votes
1 answer
811 views

How to integrate Postgres advisory lock functions into SELECT and UPDATE?

Using Postgres 13 in a Node.js application, I have a simple task to do: SELECT a row (identified by its primary key) Run some application code that cannot be done in the database* with that data. ...
cis's user avatar
  • 439
7 votes
1 answer
2k views

What are the problems with using NOLOCK given an approach to handle the short comings?

I am researching the harm of using NOLOCK SQL to load data from one database that is actively used into a reporting database. I understand that there are problems with using NOLOCK but I am thinking ...
variable's user avatar
  • 3,533
0 votes
1 answer
2k views

Get next invoice number and increment without concurrency problems

I have a table of locations that looks something like this: CREATE TABLE Locations ( Id INT NOT NULL AUTO_INCREMENT, Name VARCHAR(255) NOT NULL, NextInvoice INT NOT NULL, PRIMARY KEY (...
Jonathan Wood's user avatar
1 vote
1 answer
1k views

Select/Insert concurrently in Postgresql

I have a table like this `articles` id: int data: timestamp body: varchar for some reason, I want to split this table into 2 separate tables: `articles` id: int data: timestamp `article_body` ...
Mehdi's user avatar
  • 111
0 votes
0 answers
20 views

Avoid duplicated insert in PostgreSQL [duplicate]

I'm struggling to handle concurrent insert in my table on PostgreSQL. I have the table monitoring_services with the following structure: create table monitoring_services( id integer default nextval('...
Gutty's user avatar
  • 1
0 votes
1 answer
654 views

Why scaling up an app by 50x exceed the PostgreSQL max_connection value of 100? [closed]

I went into this issue on purpose by scaling up a dockerized Python application 50x using this command docker-compose up --scale app=50 app: This app connects to a single PostgreSQL database (also as ...
s.k's user avatar
  • 342
1 vote
2 answers
2k views

Avoid Deadlock on concurrent insert

I am trying to avoid a future problem. Table has 2 fields: id -> bigint Primary key hash -> varchar(65) unique key there will be concurrent inserts into the table for hash. I need the last ...
Andriy Lysak's user avatar
0 votes
2 answers
3k views

What is the proper way to handle multithreading insert

I'm a newbie on Database and for now I need to work with MySQL to build a web server. In a word, I have a web framework, developed with Python. It will read/write data from/into MySQL. For now I don't ...
Yves's user avatar
  • 111
4 votes
1 answer
2k views

Why is my Postgres 9.6 Create Index Concurrently INVALID?

Am trying to create an index concurrently on production. However when doing so the index when viewing via \d says INVALID. Why is this the case? Never seen it happen before. Have tried re-creating but ...
rdbmsNoob's user avatar
  • 439
0 votes
1 answer
228 views

Deleting records from two-child tables in parallel (concurrently)

I have a postgres database where I am using the pg_cron extension to automatically perform some database "maintenance" at night time - Specifically, my database has some "offers" ...
XxaemaethxX's user avatar

1
2 3 4 5
7