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
2 votes
1 answer
210 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 ...
user avatar
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
2 votes
1 answer
79 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 ...
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
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
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
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
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
1 answer
32 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 ...
user avatar
3 votes
2 answers
82 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
2 votes
3 answers
195 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
155 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
  • 299
7 votes
1 answer
737 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,407
-1 votes
1 answer
401 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
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
1 vote
1 answer
250 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
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('...
user avatar
  • 1
0 votes
1 answer
143 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
580 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
345 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
3 votes
1 answer
359 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
  • 185
0 votes
1 answer
66 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
561 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
339 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
  • 93
4 votes
2 answers
588 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
43 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
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
1 answer
381 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
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
7 votes
1 answer
315 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
291 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
55 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
639 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
108 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
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
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
3 answers
65 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
424 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
73 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,463
1 vote
2 answers
145 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
81 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
53 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
857 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
12 votes
1 answer
3k 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
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
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
0 votes
1 answer
624 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

1
2 3 4 5
7