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
1answer
140 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 ...
4
votes
1answer
258 views

Is it best practice to use surrogate keys when creating foreign key constraints in SQL Server?

I'm trying my best not to ask a leading question, but after a lot of searching I'm unable to find a similar answer or recommendation. First, take a look at this question I asked on Stack Overflow ...
1
vote
1answer
39 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 ...
4
votes
2answers
246 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 ...
1
vote
0answers
90 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 ...
1
vote
1answer
535 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 ...
-1
votes
2answers
257 views

Are lost updates completely handled in SQL Server or is manual intervention still required?

As lost updates occur when 2 different transaction try to update the same thing at the same time - different sessions update the same row; the last one overwrites the changes made by the first. We ...
0
votes
1answer
26 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
27 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. ...
9
votes
4answers
753 views

Resources for understanding SQL Server locking and concurrency?

As demonstrated by a recent question of mine locking and concurrency are HARD. Can you suggest any good resources for intermediate-to-advanced SQL professionals to do a thorough study on these that ...
7
votes
1answer
230 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
548 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 ...
107
votes
1answer
100k views

Postgres UPDATE … LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't ...
5
votes
2answers
6k views

Concurrent Transactions vs Simultaneous Transactions

Chapter 6 of David Kroenke's Database Concepts: Database Administration Is my understanding correct that in a database, simultaneous transactions don't really happen. Instead, it is a concurrent ...
0
votes
2answers
103 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 ...
5
votes
1answer
1k views

How to atomically replace a subset of table data

In PostgreSQL 9.6 I have a table T like this category | id | data ---------+----+------ A | 1 | foo A | 2 | bar A | 3 | baz B | 4 | eh B | 5 | ...
6
votes
1answer
4k views

How to improve performance on PostgreSQL when using multiple concurrent processes?

Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 ...
1
vote
1answer
306 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 ...
0
votes
1answer
88 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
11 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 ...
1
vote
1answer
384 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 ...
2
votes
1answer
85 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
33 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
136 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
30 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 ...
2
votes
2answers
378 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
0answers
70 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
14 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
303 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 ...
2
votes
1answer
553 views

Understanding repeatable read isolation level

Wikipedia defines following terms: With a lock-based concurrency control DBMS implementation, serializability isolation level requires read and write locks (acquired on selected data) to be ...
1
vote
3answers
45 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 ...
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
28 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 ...
2
votes
2answers
202 views

Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking

I have tables person and toy. There is a foreign key person.favorite_toy_id from person to toy. This foreign key is declared as ON DELETE RESTRICT. I now want to delete all toys which are no longer ...
11
votes
1answer
1k 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 ...
1
vote
2answers
96 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
34 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 ...
11
votes
6answers
5k views

Best situation to use READ UNCOMMITTED isolation level

As we all know, READ UNCOMMITTED is the lowest isolation level in which things like dirty reads and phantom reads may accrue. When is the best time to use this isolation level and for what reasons ...
0
votes
1answer
36 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
261 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 ...
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 ...
0
votes
0answers
21 views

Problem in model to save score

I am building an application in which users will have a score in which they can receive it (+10 XP) when they do certain activities, but they can also lose it when using that score to make "...
0
votes
1answer
196 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 ...
1
vote
0answers
63 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
265 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
243 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. ...
7
votes
2answers
262 views

Does timestamp protocol following thomas's write rule allow non-view-serializable schedules in some cases?

I have come across the following line in a text book (Database System Concepts Textbook by Avi Silberschatz, Henry F. Korth, and S. Sudarshan $6e$) page no. 686: Thomas’ write rule allows schedules ...
8
votes
2answers
584 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 ...
8
votes
3answers
16k 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....
1
vote
1answer
951 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
2 3 4 5 6