What's the deal with Deno? We talk with a major contributor to find out. Listen now.

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
19 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 "...
1
vote
0answers
39 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
21 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
34 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
53 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. ...
0
votes
0answers
33 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
358 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
68 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
77 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
34 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
64 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
47 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
1answer
45 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
47 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
25 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....
5
votes
0answers
200 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
30 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
47 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 ...
0
votes
1answer
197 views

What standard SQL isolation levels do the SQLite isolation levels correspond to?

There are four standard SQL isolation levels: Read Uncommitted, Read Committed, Repeated Read, and Serializable. (c.f. Database System Concepts 6ed.) In SQLite, there are three isolation levels: ...
1
vote
0answers
34 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 ...
1
vote
1answer
79 views

Is SELECT FOR … UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock?

Consider the following when the transaction isolation level is Read Committed (the default): UPDATE example SET foo = 'bar' WHERE ... and DELETE FROM example WHERE ... Neither DELETE nor UPDATE ...
1
vote
2answers
549 views

Why am I getting a deadlock for a single UPDATE query?

I have two processes which execute code like this in parallel: begin; update foos set unread=false where owner_id=123 and unread=true; commit; This results in deadlocks. My understanding of what ...
0
votes
1answer
120 views

Update query race condition

Consider this simple query UPDATE [SomeTable] SET Value = Value + 1 WHERE Id = 1 For almost any programming language this has a potential risk of race condition if it is executed by more than one ...
6
votes
2answers
2k views

Will my SQL query use stale data? How can I prevent it?

I have two tables (SJob & SJobDependent) that I need to join for some logic in a stored procedure. They both have a column (job) that connects them in a one-to-many relationship - one SJob record ...
0
votes
0answers
26 views

Does the order of query operations inside transactions matter?

Does the order of query operations inside transactions influence the performance or not? Does it depend on the low-level DBMS concurrency control protocol, e.g., 2PL, MVTO, OCC?
0
votes
0answers
34 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 ...
0
votes
1answer
29 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 ...
2
votes
1answer
64 views

How the single SQL statement is isolated?

All the books I've seen so far, talking about transactions show scenarios with several SQL statements involved. But what's about single statements? What level of isolation do they have? Is it ...
1
vote
0answers
45 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 ...
7
votes
2answers
187 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 ...
1
vote
0answers
130 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 ...
0
votes
0answers
36 views

How do I handle deadlocking on a Stored Procedure when running an SSIS package?

I am concurrently running (for example 25) the same SSIS package that retrieves data from a 3rd party platform. This data is then inserted into our Production database. In order to increase ...
0
votes
1answer
270 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 ...
0
votes
0answers
37 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, ...
-4
votes
2answers
184 views

In the following inventory system, is it possible to force row locks to avoid deadlock and ensure it works as intended? [closed]

I want to atomically reserve inventory for N objects for M users. I have two inventory tracking tables, one for global inventory and one for personal inventory. Global Inventory Table Columns: ...
2
votes
0answers
298 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: [...
1
vote
2answers
158 views

How to handle concurrency during an INSERT-SELECT cycle?

The application should limit each user's access to a certain resource to a maximum number of accesses every 24 hours. To keep track of those, we have the following table: CREATE TABLE accesses ( ...
0
votes
2answers
816 views

SQL statement sequential execution for multiple statments [duplicate]

If a single SQL file contains 2 SQL statements below each other (as below) for millions of records Query 1 : select Id into MyTempTable from TableB b join abc a on a.Id = t.Id Query 2 : Update ...
4
votes
2answers
260 views

Why is our query suddenly returning rows it should not (using READPAST and UPDLOCK options)?

We have a job table that looks like this CREATE TABLE [dbo].[Clearing]( [Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL, [BsAcctId] [int] NULL, [Status] [varchar](20) NULL, CONSTRAINT ...
1
vote
1answer
145 views

How to prevent concurrency issues in a ssis package?

Have a SQL Server 2017 Server. Have several SQL Server jobs that call a single SSIS package using the SYNCHRONIZED flag. In the package there is a data flow that runs a stored proc (please see below)...
1
vote
1answer
94 views

Deadlock on the same resource calling the same procedure

I would like to kindly ask community for help with this deadlock scenario: <deadlock-list> <deadlock victim="process17392264e8"> <process-list> <process id="...
0
votes
1answer
51 views

Assign foreign keys only after populating database

I am porting a MongoDB database over to a PostgreSQL (relational) database. I would like to perform this task concurrently, and I have developed code with parallelism in mind, but dependencies between ...
0
votes
0answers
440 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 ...
0
votes
1answer
82 views

Update Statement on one table takes locks on other tables

Given the following statement on the AdventureWorks database: UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate >= '2014-01-01' If I open another query window and look at the ...
2
votes
1answer
276 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
0answers
49 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 ...
1
vote
0answers
18 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 ...
-2
votes
2answers
160 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
89 views

Ensuring unique value for group of rows in table with trigger and concurrent transactions

I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...
0
votes
1answer
79 views

Will Postgresql statements execute synchronously if autocommit=true?

If a single application thread sends a series of INSERT statements and autocommit=true, will the statements be executed synchronously on the server? I read that on the postgres server side, "there ...

1
2 3 4 5 6