As of May 31, 2023, we have updated our Code of Conduct.

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
135 votes
1 answer
151k 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 ...
vastlysuperiorman's user avatar
11 votes
2 answers
22k 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, ...
Thomas's user avatar
  • 307
5 votes
2 answers
2k 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 ...
Joe's user avatar
  • 359
11 votes
2 answers
12k 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 ...
John Bachir's user avatar
29 votes
6 answers
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 ...
Fabian Schmied's user avatar
14 votes
3 answers
26k 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....
Rob Johansen's user avatar
5 votes
2 answers
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 ...
llazzaro's user avatar
  • 153
4 votes
1 answer
8k 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 ...
Luis Casillas's user avatar
27 votes
3 answers
15k 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, ...
ErikE's user avatar
  • 4,295
12 votes
2 answers
6k views

Is the system column "ctid" legitimate for identifying rows to delete?

I have a table with hundreds of millions of rows that I need to delete data from. The existing indexes are the most efficient. I can however use the existing indexes to find the rows to delete by ...
ruelloehr's user avatar
  • 221
9 votes
2 answers
1k 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 ...
Vivek Kumar Batra's user avatar
9 votes
4 answers
3k 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 ...
Benjamin Scherer's user avatar
6 votes
2 answers
8k views

Transaction basics. What is the result of 2 transactions running concurrently?

I have a rather basic question on transactions. Assume a table Account with a column amount. Now we have the following code: BEGIN $amount = SELECT amount FROM ACCOUNT WHERE id = 123; $amount = ...
Jim's user avatar
  • 741
5 votes
1 answer
2k 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 | ...
vektor's user avatar
  • 371
5 votes
2 answers
3k 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 ...
user avatar
40 votes
5 answers
33k 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 ...
DaveyBob's user avatar
  • 541
22 votes
3 answers
20k 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 ...
Elliot Blackburn's user avatar
15 votes
6 answers
6k 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 ...
user avatar
8 votes
1 answer
5k 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 ...
Ian Boyd's user avatar
  • 946
7 votes
1 answer
460 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 ...
Alex Shelemin's user avatar
6 votes
2 answers
13k 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 ...
user avatar
5 votes
2 answers
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 ...
a_horse_with_no_name's user avatar
4 votes
1 answer
3k 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 ...
user avatar
4 votes
1 answer
2k 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 ...
arnaslu's user avatar
  • 141
3 votes
1 answer
1k 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 ...
Tim He's user avatar
  • 223
3 votes
2 answers
2k 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(*) ...
Evan Carroll's user avatar
  • 60.4k
3 votes
1 answer
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 ...
alfonx's user avatar
  • 787
3 votes
2 answers
739 views

Force everyone to use snapshot isolation

I have a scenario in which I don't want any readers to block writers. I don't want users using table hints or isolation levels to issue shared locks, which might block writers. Is there a way for ...
processadd's user avatar
3 votes
1 answer
1k 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 ...
pietrop's user avatar
  • 716
2 votes
1 answer
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 ...
Mithir's user avatar
  • 529
2 votes
2 answers
1k 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 ...
dukebody's user avatar
  • 175
2 votes
2 answers
6k 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 ...
hemangi mehta's user avatar
2 votes
1 answer
1k 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 ...
Tobia Tesan's user avatar
2 votes
2 answers
295 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 ...
pbillen's user avatar
  • 291
1 vote
1 answer
1k 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 ...
Jim's user avatar
  • 741
1 vote
1 answer
396 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 the ...
Gennady Vanin Геннадий Ванин's user avatar
1 vote
1 answer
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'...
michael's user avatar
  • 123
1 vote
1 answer
342 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 ...
maxik's user avatar
  • 255
1 vote
2 answers
2k views

SQL Difference Optimistic Reading and Optimistic Writing

What is the difference between optimistic read and optimistic write? Or probably more so, can someone define these 4? (a) Optimistic Reading (b) Pessimistic Reading (c) Optimistic Writing (d) ...
user avatar
-1 votes
2 answers
556 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 ...
igelr's user avatar
  • 2,098