Questions tagged [concurrency]
For questions about issues arising when multiple users or processes concurrently access or modify shared information in a database system.
323
questions
7
votes
4
answers
933
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 ...
- 37.5k
0
votes
1
answer
62
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 ...
- 113
1
vote
1
answer
55
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 ...
- 59
1
vote
1
answer
97
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 ...
- 13
0
votes
2
answers
101
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,`...
- 1,930
-2
votes
2
answers
183
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 ...
- 125
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 ...
1
vote
1
answer
346
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
60
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 ...
- 101
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 ...
- 1
0
votes
0
answers
33
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
...
- 1
0
votes
1
answer
158
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
43
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 ...
- 111
0
votes
1
answer
133
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(...
- 13
0
votes
0
answers
104
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 - ...
- 31
1
vote
1
answer
566
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 ...
- 35
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 ...
- 121
0
votes
2
answers
669
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 ...
0
votes
1
answer
521
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
...
- 101
2
votes
0
answers
152
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 ...
0
votes
1
answer
742
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 ...
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 ...
- 505
0
votes
0
answers
104
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
788
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 ...
- 11
2
votes
1
answer
690
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 ...
- 123
1
vote
0
answers
232
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 ...
- 111
0
votes
1
answer
697
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 ...
- 199
3
votes
0
answers
181
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 ...
- 31
0
votes
2
answers
937
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 ...
- 225
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 ...
1
vote
2
answers
62
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 ...
- 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 ...
- 165
0
votes
0
answers
77
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 = ...
- 187
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 ...
- 47
0
votes
1
answer
202
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 ...
- 101
1
vote
0
answers
401
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 ...
- 25
0
votes
0
answers
219
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 ...
- 1
1
vote
1
answer
364
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 ...
- 113
3
votes
2
answers
842
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 ...
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
...
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 ...
- 11
0
votes
1
answer
853
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.
...
- 459
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 ...
- 3,595
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 (...
- 327
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`
...
- 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('...
- 1
0
votes
1
answer
663
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 ...
- 362
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 ...
- 115
1
vote
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 ...
- 121
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 ...
- 459