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
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 ...
11
votes
2answers
21k views

Using multiple cores for single MySQL queries on Debian

I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four. I am doing expensive joins on large tables, ...
5
votes
2answers
1k views

SQL Server 2014 Concurrent input issue

In table Orders, I store the orders we receive from all shops. Since an order can have multiple lines, among the columns there are OrderID and OrderLineID where OrderID can be duplicated but ...
9
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....
5
votes
2answers
3k views

Concurrently update with limit mysql

I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db. After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned. I ...
4
votes
1answer
6k views

MySQL consistent nonlocking reads vs. INSERT … SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...
26
votes
3answers
14k views

Managing concurrency when using SELECT-UPDATE pattern

Let's say you have the following code (please ignore that it's awful): BEGIN TRAN; DECLARE @id int SELECT @id = id + 1 FROM TableA; UPDATE TableA SET id = @id; --TableA must have only one row, ...
27
votes
6answers
5k views

Can I rely on reading SQL Server Identity values in order?

TL;DR: The question below boils down to: When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the ...
9
votes
4answers
2k views

Strategy for concurrent group bookings?

Consider a seat booking database. There is a list of n seats, and each one has an attribute is_booked. 0 means it isn't, 1 means it is. Any higher number and there is an overbooking. What is the ...
3
votes
2answers
3k 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 ...
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 | ...
4
votes
2answers
2k views

Identity Column in Concurrency, Multithreading, Parallel Processing

Our company would like to understand the effects: If we have single table with Primary Key Identity column, int primary key IDENTITY(1,1), and the Application is conducting multi-threading parallel ...
38
votes
5answers
28k views

Locking issue with concurrent DELETE / INSERT in PostgreSQL

This is pretty simple, but I'm baffled by what PG does (v9.0). We start with a simple table: CREATE TABLE test (id INT PRIMARY KEY); and a few rows: INSERT INTO TEST VALUES (1); INSERT INTO TEST ...
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 ...
8
votes
1answer
4k views

How is SQL Server returning both a new value and old value during an UPDATE?

We've had issues, during high concurrency, of queries returning non-sensical results - results the violate the logic of the queries being issued. It took a while to reproduce the issue. I've managed ...
14
votes
3answers
10k views

Concurrent transactions result in race condition with unique constraint on insert

I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation ...
5
votes
2answers
10k views

SQL Server Concurrent Inserts and Deletes

From: SQL Server 2014 Concurrent input issue Follow up question: How do we Delete and Reinsert rows in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc? Do we ...
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 ...
4
votes
1answer
2k views

SQL Server Concurrent Updlock Serializable vs Try Catch

We import multiple records per second from different flat files. Sometimes we encounter a racing condition, and duplicate Unique error constraint. We are inserting and retrieving records, I hear ...
3
votes
2answers
1k views

Is `count(*)` ever guaranteed to return the same result in a transaction at any transaction level?

If I create a table. CREATE TABLE foo AS SELECT CASE WHEN random() > 0.5 THEN x END AS x FROM generate_series(1,10) AS x; And, then I run the following in a transaction BEGIN; SELECT count(*) ...
3
votes
1answer
3k views

List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index. I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY ...
2
votes
1answer
873 views

Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

I'm having a locking issue on PostgreSQL 9.4.5. I run two or more transactions simultaneously (generally there are 2 running at the same time, but 2+ may happen sometimes) reading from the same tables ...
2
votes
2answers
882 views

Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?

I have a large (3M rows) InnoDB table in MySQL 5.6.21 in production and I want to add some columns. From the MySQL Documentation on Overview of Online DDL I understand that, even if this is an ...
1
vote
1answer
263 views

UPDATE n:m relation in view as array (operations)

Environment I came up with a view and trigger based solution to modify rows in multiple underlying tables in a Postgres 9.5.3 database. This works reasonable using Qt's model view programming. The ...
1
vote
1answer
4k views

Show processlist / Max concurrent connections seem to max out at 131

When I put my database under a load test (basically just a basic write and basic read from our app), and run show processlist; in the middle of it, there is always 131 rows in the set and that's it. I'...
5
votes
2answers
2k views

SQL Server (2012) non-blocking DDL?

I'm not very experienced with SQL Server, so maybe I'm missing something My situation is as follows: Session 1 runs a CREATE TABLE (or other CREATE statements) with autocommit off and the CREATE is ...
4
votes
1answer
1k views

Concurrent UPDATE deadlock (updating 2 fields, selecting by PK)

I have a strange deadlock case, where simple concurrent updates are causing deadlocks on MySQL (5.6.27). The setup is with multiple workers (several different servers) processing queues and sometimes ...
2
votes
1answer
830 views

Does READ UNCOMMITTED have the same effect as AUTOCOMMIT=1?

AFAIK running transactions as READ UNCOMMITTED means that there is no lock, so every change caused by a transaction is immediately visible to other transactions. Is it correct to assume that this is ...
2
votes
2answers
5k views

Is SQL Server Thread Safe?

If I need to update same row with two concurrent thread then is it possible in SQL Server? Suppose I have one table that contains Id and Name columns and now I want to update the third number Id with ...
2
votes
1answer
4k views

performance issues when concurrent requests

We are using SQL Server 2008 R2 on a Windows Server 2008 server. We have some main functions which uses some Stored Procedures which has a lot of sub-queries inside of it. I'll take one SP as an ...
1
vote
1answer
880 views

How does the transactions interact if you set a different isolation level per connection?

I read that I can have different isolation levels per connection and in the server. The default isolation level is REPEATABLE-READ. So if I have a transaction that issues a SERIALIZED to the client ...
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 ...
-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 ...