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
0answers
32 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 ...
1
vote
1answer
51 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` ...
0
votes
0answers
18 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('...
0
votes
1answer
47 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 ...
1
vote
2answers
86 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 ...
0
votes
2answers
35 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 ...
2
votes
1answer
50 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 ...
0
votes
1answer
19 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" ...
3
votes
2answers
518 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-...
1
vote
1answer
93 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 ...
1
vote
0answers
93 views

SQL statements to ensure that the same location cannot be reserved more than once on the same day?

I have two tables one for venues (3 locations hall1, hall2 and hall3) and one for orders (a row could be 441,2004,50895,'Requested','2021-01-02 10:53','2021-03-06 11:46',7,3) as follow: CREATE TABLE ...
4
votes
2answers
295 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 ...
0
votes
1answer
29 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
1answer
47 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. ...
0
votes
1answer
191 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 ...
0
votes
0answers
12 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 ...
7
votes
1answer
248 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 ...
2
votes
1answer
161 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 ...
1
vote
1answer
36 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? ...
1
vote
1answer
252 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 ...
0
votes
1answer
49 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 ...
0
votes
0answers
159 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 ...
1
vote
0answers
20 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, ...
1
vote
3answers
51 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 ...
1
vote
3answers
349 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 ...
8
votes
2answers
1k 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 ...
1
vote
1answer
38 views

Why use an intent lock at a higher level in the hierarchy, why not just a regular lock?

This article suggests intent locks are used at a higher level of the lock hierarchy to indicate that a lock has been taken on a lower level. The idea of this mechanism is that SQL Server doesn't need ...
1
vote
2answers
100 views

Dirty read or phantom row

We are having dirty reads or phantom rows in our data warehouse. My question is not how to solve it (it is a long story and I am currently working on it), but I want to know what exactly happens to ...
1
vote
1answer
38 views

What is the proper way to validate that a many-to-many relationship will not surpass a certain limit?

In a Postgres DB, I have a relationship as follows: A Group table has a column userEntranceLimits. A UserEntrance table has a column userId and a column groupId. The count of a UserEntrance's ...
0
votes
1answer
38 views

Enforcing maximum number of relations for a record

I am trying to figure out the way to enforce a maximum number of relations in a highly concurrent system. The following is an example use case. // Job table job_id | ... | num_positions xx-xx-xx | ....
2
votes
1answer
482 views

Does SQL Server Support Parallel Bulk Inserts From Multiple Connections?

Does SQL Server support parallel bulk inserts into the same database table? Some clarifications for my particular use case: The table has a clustered index composed of two distinct index keys, and ...
11
votes
1answer
2k views

What is "special" about PostgreSQL update vs delete+insert

My understanding is that an update locks a tuple, marks it as deleted, and then adds a new tuple. In other words, update = delete + insert. Or so I had believe. But it appears there something ...
0
votes
0answers
19 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 ...
1
vote
0answers
79 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) ...
0
votes
1answer
350 views

Postgres CREATE OR REPLACE VIEW... can it block?

I'm looking for a way for a table to be swapped while it's being used. Say we have a view CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v1; that is being used, e.g. lots of SELECT * FROM ...
0
votes
1answer
444 views

Prevent unique constraint violations caused by concurrent inserts

I have a situation where I need to insert a unique record into the database: SELECT user FROM users WHERE id = 'some_randomly_generated_id' If user does not exist, INSERT INTO users WHERE id = '...
0
votes
1answer
320 views

How to ensure no duplicate records are added when using multiple Node JS processes

We are working on an appointment scheduler. We have a table Appointment that stores an appointment with starttime, endtime, assigneeId and eventId. We are using Bookshelf.js as an ORM with Node JS. ...
1
vote
1answer
656 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 ...
8
votes
2answers
664 views

UPDATE statement behavior

I have a question regarding inner workings of UPDATE statements with regards to SQL Server. I am trying to understand what will happen if the following 2 update statements are received or serviced at ...
1
vote
1answer
1k views

How to prevent implicit casting from bytea to text during the current connection in postgres?

In Postgres bytea values are automatically converted to text when inserted to text/varchar columns, based on the bytea_output setting. I am working with some program code which automatically converts ...
1
vote
2answers
352 views

PostgreSQL sometimes fails to detect serialization failures

I have an http-server in application language that looks roughly like this: item = SELECT item FROM table WHERE field = 'value' if (item) { UPDATE another field on item } else { INSERT item VALUE ...
0
votes
2answers
92 views

PostgreSQL concurrency control and Serializable transactions

Im building an application that needs to do some concurrent read/write operations on specific tables. Even after reading the official documentation (and many, many articles), the trouble persists, no ...
2
votes
1answer
503 views

Are SqlServer Sequences, guaranteed to return different values for concurrent transactions?

In documentation it is told that: Duplicate validation only occurs once a record is fully populated. This can result in some cases where the same number is used for more than one record during ...
1
vote
1answer
115 views

How to improve performance for read only innodb MySQL Database

I have a particular task which is to maximize the concurrent performance. There is only one particular type of query, which is select * from table where col1 between ? and ? and col2 between ? and ? ...
2
votes
2answers
524 views

Is there a memory overhead using partitions in postgres vs. a single table?

Background I am ingesting time series data totalling to about 10M rows per day, where each row has a timestamp, a player id and some other columns. That data is then queried via an API for a ...
0
votes
1answer
61 views

Why aren't read-only queries executing concurrently? [closed]

I have a Django app using either sqlite or Postgres. (I've tried both and encountered the same problem.) Requesting a particular endpoint results in a read-only query like this that takes 1.5-3 ...
0
votes
1answer
47 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....
6
votes
0answers
242 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 ...
0
votes
0answers
35 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 ...
1
vote
0answers
50 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 ...

1
2 3 4 5 6