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
13 views

Postgres vs MySQL: Commands out of sync;

MySQL scenario: When I execute "SELECT" queries in MySQL using multiple threads I get the following message: "Commands out of sync; you can't run this command now", I found that this is due to the ...
2
votes
1answer
60 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
33 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
32 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
44 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
24 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
132 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
28 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
45 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
128 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
33 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
73 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 ...
2
votes
2answers
228 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
61 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
28 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
57 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 ...
6
votes
1answer
150 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
117 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
34 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
221 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
36 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
173 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
224 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
112 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
440 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
228 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
122 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
83 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
274 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
70 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
227 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
44 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
17 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
131 views

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

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
82 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
70 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 ...
0
votes
1answer
218 views

Concurrent update queries locking rows when `where` clause doesn't match

My application sends messages to Slack. On very rare occasions we may need to retract the message going out. These retractions are handled by queueing a job for each messageId that has been created ...
1
vote
1answer
29 views

Database transaction consistency on PostgreSQL

In a table with three columns (serial id, varchar name, varchar email) and having two different transactions executing concurrently as the ones bellow: Transaction 1: BEGIN; INSERT INTO public.users ...
2
votes
0answers
182 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 ...
3
votes
2answers
495 views

Performance of large transactions and concurrency?

If I have a multi-million row table and I run a transaction that updates 50k rows, what are the performance implications of this? Assuming it's indexed correctly, it shouldn't take long, but what ...
2
votes
1answer
98 views

Concurrent requests not running in parallel

I am trying to run update statistics in parallel. Say I start with 2 sessions: S1, S2 In S1 I run Update Statistics A with fullscan and time it to take 2 minutes. In S2 I run Update Statistics B ...
0
votes
1answer
98 views

Concurrent backups in MS SQLServer 2008 R2

Can anyone please point me to a piece of documentation that suggest how many concurrent backups initiated by different BACKUP statements are possible in Microsoft SQL server 2008? The documentation ...
3
votes
0answers
202 views

Why do two transactions making a read lock result in a DEADLOCK exception?

Let's assume a scenario that flows as follows: Transaction #1 makes this query: SELECT v.id, v.name FROM versions_history as vh INNER JOIN versions as v ON vh.version_id = v.id WHERE vh.project_id = ...
0
votes
1answer
95 views

Multiple concurrent sql dumps in mysql

My question is whether it is safe to perform multiple dumps at the same time in mysql, i.e. running: mysql -u root -p database1 < database1.sql mysql -u root -p database2 < database2.sql on ...
1
vote
1answer
54 views

Isolation level issue

I need some help in choosing the correct isolation level on my transactions. My situation is as follows. I have a table with a unique constraint over two columns. For the sake of the argument, lets ...

1
2 3 4 5