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
3 votes
2 answers
35 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 ...
user avatar
1 vote
3 answers
41 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 ...
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 votes
1 answer
41 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. ...
user avatar
  • 289
7 votes
1 answer
632 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 ...
user avatar
  • 2,239
-1 votes
1 answer
181 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 (...
user avatar
0 votes
0 answers
43 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
43 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
1 vote
1 answer
129 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` ...
user avatar
  • 111
0 votes
0 answers
19 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('...
user avatar
  • 1
0 votes
1 answer
90 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 ...
user avatar
  • 216
1 vote
2 answers
320 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 ...
user avatar
0 votes
2 answers
77 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 ...
user avatar
  • 111
2 votes
1 answer
195 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 ...
user avatar
  • 143
0 votes
1 answer
39 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" ...
user avatar
  • 111
3 votes
2 answers
539 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-...
user avatar
  • 41
1 vote
1 answer
182 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 ...
user avatar
  • 83
1 vote
0 answers
94 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 ...
user avatar
  • 47
4 votes
2 answers
407 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 ...
user avatar
  • 77
0 votes
1 answer
38 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 ...
user avatar
0 votes
1 answer
100 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
1 answer
299 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 ...
user avatar
  • 3
0 votes
0 answers
17 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
7 votes
1 answer
286 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 ...
user avatar
2 votes
1 answer
260 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 ...
user avatar
1 vote
1 answer
46 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? ...
user avatar
1 vote
1 answer
481 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 ...
user avatar
0 votes
1 answer
72 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 ...
user avatar
0 votes
0 answers
439 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
1 vote
0 answers
29 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
3 answers
58 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 ...
user avatar
1 vote
3 answers
400 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 ...
user avatar
  • 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 ...
user avatar
  • 213
1 vote
1 answer
62 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 ...
user avatar
  • 1,450
1 vote
2 answers
122 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 ...
user avatar
1 vote
1 answer
48 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 ...
user avatar
0 votes
1 answer
44 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 | ....
user avatar
  • 3
2 votes
1 answer
710 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 ...
user avatar
  • 21
12 votes
1 answer
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 ...
user avatar
0 votes
0 answers
20 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
1 vote
0 answers
96 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
0 votes
1 answer
514 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 ...
user avatar
0 votes
1 answer
615 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 = '...
user avatar
  • 103
0 votes
1 answer
474 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. ...
user avatar
  • 209
1 vote
1 answer
948 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
9 votes
2 answers
825 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 ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
1 vote
2 answers
420 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 ...
user avatar
  • 151
0 votes
2 answers
127 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 ...
user avatar
2 votes
1 answer
750 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 ...
user avatar

1
2 3 4 5 6