Questions tagged [concurrency]

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

57 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6 votes
1 answer
294 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 ...
user avatar
4 votes
0 answers
287 views

Performance impact of having Polymorphic collections in MongoDB?

Say one document(Level 1) is supposed to have N(k) different kinds of items as sub-document arrays(Level 2), and these are stored in a separate collection(s?) due to lack of query optimisation for ...
user avatar
  • 141
4 votes
0 answers
357 views

Can a slow SELECT statement in READ COMMITTED isolation level read content from concurrent committed UPDATE?

Let's say we have a big table with hundreds of million of rows (or more). On one connection, we perform a query requiring full table scan under READ COMMITTED isolation level: SELECT * FROM bigtable;...
user avatar
  • 147
3 votes
0 answers
953 views

Grant Privileges on Production Database Throws "Error: tuple concurrently updated"

I would like to grant read access to all tables in my schema GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXX; While it works on our alpha environment, it fails with our production database: [...
user avatar
  • 183
3 votes
0 answers
301 views

Why do two transactions making a read lock result in a DEADLOCK exception?

Let's assume a scenario that flows as follows: Transaction #1 makes this query: SELECT v.id, v.name FROM versions_history as vh INNER JOIN versions as v ON vh.version_id = v.id WHERE vh.project_id = ...
user avatar
3 votes
0 answers
503 views

Failed to run query: error: 1023 on Redshift

I have a lambda : 'use strict'; const config = require('./redshift_config'); const pg = require('pg'); const redshift_conn = `pg://${config.user}:${config.password}@${config.host}/${config.database}`...
user avatar
  • 567
3 votes
0 answers
802 views

Is a postgresql subquery in autocommit mode part of the same transaction or a different one?

I can't find anything explicit about this and I'm not sure whether "statement" in the documentation refers to an individual select or input text: By default (without BEGIN), PostgreSQL executes ...
user avatar
3 votes
0 answers
338 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 ...
user avatar
2 votes
0 answers
55 views

Repro for missed rows during Allocation Order Scan + Page Splits

In Read Uncommitted, an index scan can execute as an allocation order scan. If a page split occurs during the scan, it is possible that a row will be read twice if it moves to a later position in the ...
user avatar
2 votes
0 answers
111 views

Do concurrent in-memory databases exist?

I spent days searching the internet but all the databases I found are at most in-memory but not concurrent. By concurrency I mean that they run on multiple threads and can do multiple reads ...
user avatar
  • 121
2 votes
0 answers
147 views

Concurrent Merge Issues

A (simplified) model of my application has several processes dumping data into the table below: CREATE TABLE [dbo].[InputData] ( [InputId] [int] NOT NULL, [Dts] [datetime] NOT NULL, [...
user avatar
  • 53
2 votes
0 answers
569 views

Is it a good idea to have too many named locks?

I am using MySQL as a data store and a Java App on the top. There are many state changes that happen, and I am seeing some concurrency bugs in there. I wanted to know if I can use named locks (...
user avatar
  • 141
2 votes
0 answers
516 views

SSIS: FTP task - concurrent connections

I have created an SSIS package for downloading file from FTP server. When I debug the package, everything goes fine. But when I shedule the package as Job step, it sometimes fails to connect. The job ...
user avatar
  • 121
2 votes
1 answer
690 views

How to make a PostgreSQL trigger thread-safe

I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't ...
user avatar
  • 237
1 vote
2 answers
21 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 ...
user avatar
  • 13
1 vote
1 answer
16 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 ...
user avatar
  • 27
1 vote
0 answers
64 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 ...
user avatar
1 vote
0 answers
18 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 ...
user avatar
  • 11
1 vote
0 answers
35 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, ...
user avatar
  • 41
1 vote
0 answers
107 views

Order of foreign keys/cascades processing in SQL Server

I have a parent table and 2 (or more) children tables with foreign key defined with some cascade option. So when an entry from a parent table is being deleted, SQL Server will check 2 (or more) ...
user avatar
1 vote
0 answers
87 views

Sequence number for COMMITTED concurrent inserts

For example, I have a table create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type and many concurrent inserts into this table. Is there is a way to fill the OrderNum ...
user avatar
  • 119
1 vote
1 answer
779 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 ...
user avatar
1 vote
0 answers
102 views

Understanding connection between different database concurrency control protocols

I have came across following diagram related to timestamp based concurrency control protocols in database systems (as summarized in these sldes or detailed in the book by Korth et al.): I don't know ...
user avatar
  • 219
1 vote
0 answers
25 views

Understanding what does it mean when we say certain rule ensures conflict serializabiliy

Database book by Korth explains validation protocol as follows : To perform the validation test, we need to know when the various phases of transactions took place. We shall, therefore, associate ...
user avatar
  • 219
1 vote
0 answers
360 views

MongoDB write locks on insert

Why would an insert into a MongoDB 3.4 collection require global write locks? I'm creating a copy of a large collection in a new replica set (via mongodump/restore) and I'm finding that some inserts ...
user avatar
1 vote
1 answer
25 views

How to concurrency will be handled in this case?

Say I have employee table with 100 records . Out of 100 records, 60 records having middle_name as null Say in two separate sessions(say Session_A, Session_B), I execute below query at same time ...
user avatar
1 vote
0 answers
113 views

conditional UPDATE race conditions

In a conditional UPDATE like this: UPDATE MY_TABLE SET my_coulmn = "my value" -- many other updates WHERE ... -- many conditions Do I have any guarantee that I will only update those records ...
user avatar
  • 111
1 vote
0 answers
115 views

Row locks on selects

select t.*, row_number() over ( partition by t.Entity_Identifier order by t.Message_Sequence ) as seqnum from Process_Activity t (ROWLOCK) where t.Process_Id = Is it possible for this ...
user avatar
1 vote
0 answers
7k views

How to identify concurrency issues in Oracle 11g?

For a short period in time we had a concurrency issue that lead to subsequent faults in our application (Java, Spring, Hibernate). Since we are using dictionary managed tablespaces, I first thought ...
user avatar
  • 127
1 vote
0 answers
207 views

How can I obtain concurrent deletes for this table?

We have a program that is currently doing something to the effect of: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION UPDATE [Source].[dbo].[SourceTable] SET [ProcessedOn] = ...
user avatar
1 vote
1 answer
1k views

How to determine right value for innodb_sync_array_size

How do I determine the right value for innodb_sync_array_size in MySQL? We have version 5.7 deployed on AWS RDS with 32 vCPUs, and it sees about 26 concurrent threads on average. When I measure waits ...
user avatar
0 votes
0 answers
32 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 = ...
user avatar
  • 7
0 votes
0 answers
29 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 ...
user avatar
0 votes
0 answers
51 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 ...
user avatar
  • 1
0 votes
0 answers
54 views

If a schedule is allowed by 2 phase locking protocol, does that mean all possible interleaved schedules (same transactions) are serializable?

I came across following results on web: 1)2PL ensures conflict serializability(cs) and hence avoid non serializable schedules. 2) But not every cs schedule allowed by 2pl. 3)this is a theorem i found: ...
user avatar
0 votes
0 answers
58 views

how to manage concurrency in Microsoft azure dedicated SQL pool?

system configuration- user count- 1200 Data warehouse Units- 1000dWU current maximum concurrency- 128 resource class used most- staticrc20(90%) , staticrc30(8%), smallrc(2%) DB size- 500 TB Problem ...
user avatar
0 votes
1 answer
123 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. ...
user avatar
0 votes
0 answers
45 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 ...
user avatar
0 votes
0 answers
748 views

Can't refresh Materialized View Concurrently

I'm trying to run REFRESH MATERIALIZED view CONCURRENTLY my_view in Postgresql, and I'm getting the following error: SQL Error [XX000]: ERROR: unexpected attlen: -2 Where: SQL statement "SELECT ...
user avatar
  • 101
0 votes
0 answers
22 views

Query doesn't return data that's written recently

I have two micro services where one write to DB and another read from DB. The first micro service is written in C++ while the other is written in NodeJS. When querying for data say for a IP the ...
user avatar
  • 1
0 votes
1 answer
54 views

Prevent failure in conditional insert in mysql database

The infrastructure of our system looks like this. An AWS lambda function receives requests such as (accountId, .....). It creates an entry in the MySQL database using a newly generated UUID as caseId....
user avatar
  • 101
0 votes
0 answers
58 views

What is correct way to ensure that row is not deleted in concurrent transaction

I have 2 tables: Parent (PK id) Child (PK id, parent_id) Note that parent_id is not defined a foreign key (actually they are not parent/child, I use those names just for example). I need to handle ...
user avatar
  • 121
0 votes
0 answers
97 views

How does MySql database handles multiple update operation

I am new to database and the way they work so my question might be a bit dumb, please be nice. I have always been thinking about how database update work in general and I think I understand the ...
user avatar
  • 101
0 votes
1 answer
102 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 ...
user avatar
0 votes
0 answers
43 views

Effect on concurrency of breaking queries into multiple statements

Let's say I have a paginated search query which takes a long time (when the offset is large) due to a large number of rows in the joined tables. The purpose of the query is to search through accounts, ...
user avatar
  • 113
0 votes
0 answers
2k views

Perform SELECT without Locking Table but don't allow Dirty Reads

Is there a way I can perform a SELECT query which wont lock the table it is reading but also won't perform dirty reads? for example, assume Table1 has 100m records: SELECT Col1, Col2 FROM ...
user avatar
  • 1,463
0 votes
0 answers
50 views

Setting up a Database for Multiple User Access

I am working on revamping the database at the company I work at. I've been making decent progress getting various parts set up, but something occurred to me today that I hadn't really thought to ...
user avatar
0 votes
0 answers
676 views

Concurrency with triggers in mysql

I'm trying to implement a hierarchy using closure tables maintained by triggers. I'm using MySQL 5.6. I know we should move out of the 90s and use a database that actually supports CTEs, but this is ...
user avatar
0 votes
0 answers
2k views

AUTO INCREMENT concurrency issue and ordering

Let's suppose that table t1 has an AUTO INCREMENT primary key id. And let's say that I am starting two transactions at different moments of time, and that transactions are started by the same ...
user avatar
  • 193
0 votes
1 answer
53 views

Help troubleshooting mysql deadlock

We have a query, which is trying to update a row, placing a user at the end of a queue. To do this, it needs to grab the MAX(initialPositon) and set this row's initialPosition to MAX(initialPosition)+...
user avatar
  • 1