Questions tagged [concurrency]
For questions about issues arising when multiple users or processes concurrently access or modify shared information in a database system.
306
questions
0
votes
2
answers
35
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
0
answers
40
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
89
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
68
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
237
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 ...
- 319
0
votes
0
answers
40
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
273
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
187
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 ...
- 23
1
vote
0
answers
58
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
241
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 ...
- 197
3
votes
0
answers
104
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
368
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
628
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
34
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
3
votes
1
answer
496
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 ...
- 145
0
votes
0
answers
47
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 = ...
- 137
2
votes
2
answers
582
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 ...
- 37
0
votes
1
answer
77
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
214
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
154
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
182
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
298
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 ...
2
votes
3
answers
921
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
22
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
471
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.
...
- 359
7
votes
1
answer
1k
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,153
-1
votes
1
answer
1k
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 (...
- 257
1
vote
1
answer
697
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
392
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 ...
- 300
1
vote
2
answers
1k
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
0
votes
2
answers
1k
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 ...
- 111
3
votes
1
answer
856
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 ...
- 389
0
votes
1
answer
170
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" ...
- 121
3
votes
2
answers
722
views
Read-only transaction anomaly
I've learned about this anomaly from these papers: https://www.cs.umb.edu/~poneil/ROAnom.pdf (original) https://johann.schleier-smith.com/blog/2016/01/06/analyzing-a-read-only-transaction-anomaly-...
- 41
1
vote
1
answer
966
views
Measuring how long PostgreSQL waits for locks
I want to figure out how long PostgreSQL spends waiting for locks during a period of time (during this period of time, many requests are served by PostgreSQL).
What I know
PostgreSQL system table ...
- 203
4
votes
2
answers
1k
views
Transaction strategy for UNIQUE constraint?
How to use a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day?
It was suggested to me that it will different for every isolation level. Could ...
- 77
0
votes
1
answer
92
views
Pessimistic locking and isolation levels
How is pessimistic locking with read and write locks related to isolation levels?
Readers block writers and writers block readers. This behaviour is always constant irrespective of isolation level ...
0
votes
1
answer
207
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. ...
- 193
1
vote
1
answer
623
views
Is MySQL more scalable than PostgreSQL due to the difference in how they handle connections?
I'm trying to decide if either MySQL or PostgreSQL would be more suitable for an application that will get hit by potentially thousands of simultaneous requests at a time.
During research, one fact ...
- 13
0
votes
0
answers
129
views
How to avoid duplicate in mysql INNODB transaction for non index key?
I have a INNODB table with txnno column without unique index but I have primary key on another column. Now I am inserting transaction from multiple places. And there are some concurrent insertion ...
- 41
7
votes
1
answer
431
views
Missing rows under read committed isolation
It's well known that Read Committed isolation is prone to different anomalies. I read The Great Paul White's series on isolation levels. The post relevant to the discussion is this one:
The Read ...
- 555
2
votes
1
answer
409
views
SQL Server 2019 running 100% on only 1-2 cores with MAXDOP 8 to 14
Here is the background on this issue.
I am inserting slightly over 1 million records into a table from within a C++ program using ADO. One row at a time is read in from flat file and converted info a ...
- 21
1
vote
1
answer
91
views
Can Postgres timestamp be used to implement client side caching?
If I have a pool of connections in a multi-threaded client sending out requests to the database server, can I rely on transaction or statement level timestamps to determine which data is most current?
...
- 63
1
vote
1
answer
1k
views
Can UPDATE lead to deadlocks in SQL SERVER?
Can we experience deadlock if run the same update query concurrently?
Let's assume there's a table with millions of records and we need to update several thousand records at one shot.
UPDATE TABLEX
...
- 113
1
vote
1
answer
235
views
Concurrent Update of Array (Postgres)
I have an array that I can update from multiple workers as follows:
UPDATE my_table SET arr = array_append(my_table.arr, element).
The problem pops up when two workers try to update the array at the ...
1
vote
0
answers
68
views
The overhead of OCC validation phase
The validation phase of optimistic concurrency control has two derctions: one is backward validation, checking for conflicts with any previously validated transaction, the other is forward validation, ...
- 41
1
vote
3
answers
94
views
How to improve the liveness of a system when having a long-running transaction blocking multiple reading transactions
Is there any recommended approach for improving the application liveness when having, over the same database object, multiple reading transactions, and one long-running transaction which is updating ...
- 13
1
vote
3
answers
542
views
What is the difference between concurrency control in operating systems and in trasactional databases?
Recently I'm learning concurrency control techniques in transactional databases. However, I am so confused about the differences between concurrency control in operating systems and in transactional ...
- 41
9
votes
2
answers
2k
views
Safe to use WITH (NOLOCK) in this case?
Scenario
I have a table that deals with concurrent SELECT's and DELETE's. I'm getting a few deadlocks on my SELECT statements. I assume that the DELETE from the other transaction is getting an ...
- 213