Questions tagged [concurrency]
For questions about issues arising when multiple users or processes concurrently access or modify shared information in a database system.
309
questions
0
votes
0
answers
52
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 - ...
1
vote
1
answer
74
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 ...
- 167k
14
votes
3
answers
26k
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....
- 167k
1
vote
2
answers
177
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
1
answer
137
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
...
- 74.9k
0
votes
1
answer
354
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 ...
CommunityBot
- 1
13
votes
2
answers
6k
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 ...
- 167k
1
vote
1
answer
248
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 ...
CommunityBot
- 1
0
votes
1
answer
282
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 ...
CommunityBot
- 1
0
votes
2
answers
123
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 ...
- 42.9k
2
votes
0
answers
113
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
109
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
251
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. ...
CommunityBot
- 1
1
vote
1
answer
952
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 ...
CommunityBot
- 1
11
votes
2
answers
13k
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 ...
- 167k
7
votes
2
answers
393
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 ...
- 8,303
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 ...
CommunityBot
- 1
0
votes
1
answer
559
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 ...
- 32.2k
0
votes
0
answers
52
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
430
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 ...
- 80.3k
2
votes
1
answer
278
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
20
votes
2
answers
13k
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 ...
- 378
1
vote
0
answers
104
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
3
votes
0
answers
125
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
1
vote
2
answers
38
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 ...
- 24.4k
3
votes
2
answers
810
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 ...
- 868
0
votes
2
answers
515
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 ...
- 80.3k
0
votes
1
answer
101
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 ...
- 24.3k
2
votes
1
answer
777
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 ...
3
votes
1
answer
691
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 ...
- 24.4k
0
votes
0
answers
54
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 = ...
- 147
1
vote
0
answers
272
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
170
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 ...
- 152
3
votes
1
answer
8k
views
How to execute two transactions concurrently in PostgreSQL?
I want to execute two transactions concurrently in pgAdmin:
--The first transaction should modify the age of Paul
BEGIN;
DECLARE newage INTEGER;
--the original age of Paul is 32
newage := Select age ...
- 167k
1
vote
1
answer
234
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 ...
- 42.9k
3
votes
2
answers
460
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 ...
- 29.9k
2
votes
3
answers
1k
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
...
- 74.9k
0
votes
2
answers
159
views
Mysql locking - How to acquire a lock in the DB for each userId
Hi I have a J2EE Server(clustered) that executes requests from multiple users.
I would like that each request of a given user will be handled like so:
{
acquireLockForUser(userId); // i.e. insert ...
CommunityBot
- 1
1
vote
1
answer
396
views
How, when and why does implicit changing of transaction isolation level happen?
RCSI (Read Committed Snapshot Isolation) is the setting of a database changing the behavior of (the default) Read Committed isolation level in SQL Server.
This is, as I understand, the context of the ...
- 2,181
1
vote
0
answers
23
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
547
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.
...
- 29.9k
22
votes
3
answers
20k
views
Concurrent transactions result in race condition with unique constraint on insert
I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation ...
- 167k
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 ...
- 32.2k
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,241
10
votes
1
answer
3k
views
Isolation and atomiticy of SQL Server UPDATE
Is there any difference in atomicity and isolation between the two following SQL statements?
Batch A:
UPDATE Orders
SET InProgress = 1
WHERE Completed = 0
and
Batch B:
UPDATE Orders
SET ...
- 2,181
-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 (...
- 44.3k
21
votes
2
answers
34k
views
Locking in Postgres for UPDATE / INSERT combination
I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once.
The user needs to be able to redeem a coupon, which will insert a row into the log table ...
0
votes
1
answer
48
views
_Batch_ priorities in PostgreSQL
I would want to tag many queries and mostly materialised view refreshing tasks as low priority in my RDBMS, in the fashion I run the web browser on the local machine as low priority (because JS, CSS, ...
- 66
4
votes
3
answers
691
views
Why is our query suddenly returning rows it should not (using READPAST and UPDLOCK options)?
We have a job table that looks like this
CREATE TABLE [dbo].[Clearing](
[Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL,
[BsAcctId] [int] NULL,
[Status] [varchar](20) NULL,
CONSTRAINT ...
- 1
1
vote
1
answer
833
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`
...
- 5,070