Questions tagged [concurrency]
For questions about issues arising when multiple users or processes concurrently access or modify shared information in a database system.
309
questions
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 ...
- 1,505
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 ...
- 541
33
votes
4
answers
8k
views
How do you test for race conditions in a database?
I try to write database code to make sure that it's not subject to race conditions, to make sure that I've locked the correct rows or tables. But I often wonder: Is my code correct? Is it possible to ...
- 2,821
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 ...
- 555
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, ...
- 4,295
24
votes
2
answers
15k
views
Can I create index on a table in a MySQL database while in use
And if I can, any reason (performance/stability) why I shouldn't?
- 1,442
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 ...
- 325
21
votes
2
answers
33k
views
Locking in Postgres for UPDATE / INSERT combination
I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once.
The user needs to be able to redeem a coupon, which will insert a row into the log table ...
- 313
20
votes
2
answers
12k
views
Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL
If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?
I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until ...
- 827
18
votes
1
answer
15k
views
PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?
I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who ...
- 283
16
votes
1
answer
437
views
Read a partially updated row?
Let say I have two queries, running in two separate sessions in SSMS:
First session:
UPDATE Person
SET Name='Jonny', Surname='Cage'
WHERE Id=42
Second session:
SELECT Name, Surname
FROM Person ...
- 163
16
votes
1
answer
5k
views
What happens when we add an index to an existing table with a large amount of data?
I've a table which will contain around ~15 Million records. Now I need to add an index to the table.
Adding an index will take some time to update every entry in the table.
I'm quite confused ...
- 263
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 ...
user123215
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....
- 333
13
votes
3
answers
7k
views
Insert if not exists, concurrently
I am having concurrency issues with my inserts in a stored procedure. The relevant part of the procedure is this:
select @_id = Id from table1 where othervalue = @_othervalue
IF( @_id IS NULL)
BEGIN
...
- 313
13
votes
1
answer
5k
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 ...
- 680
13
votes
3
answers
1k
views
Inconsistency in repeatable read
http://www.postgresql.org/docs/9.2/static/transaction-iso.html
The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, ...
- 457
13
votes
1
answer
28k
views
Putting a Select statement in a transaction
What is the difference between these 2 queries:
START TRANSACTION;
SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John' WHERE id=1;
COMMIT;
And without transaction:
SELECT * FROM ...
- 283
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 ...
- 221
12
votes
1
answer
480
views
Highly Concurrent Storage System
Imagine your requirement is that you have 3 huge tables (structured data) with say 30 billion rows in each (total size of 4TB) and your many concurrent users (which are parallel os threads on remote ...
- 223
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 ...
- 665
11
votes
1
answer
6k
views
Why is CTE open to lost updates?
I don't understand what Craig Ringer meant when he commented:
This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected ...
- 999
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, ...
- 307
11
votes
2
answers
4k
views
LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR
We have a process that generates an inventory report. On the client side, the process splits of a configurable number of worker threads to build a chunk of data for the report that corresponds to one ...
- 1,364
11
votes
1
answer
12k
views
PostgreSQL concurrent increment of counter
I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item ...
- 111
10
votes
1
answer
3k
views
Isolation and atomiticy of SQL Server UPDATE
Is there any difference in atomicity and isolation between the two following SQL statements?
Batch A:
UPDATE Orders
SET InProgress = 1
WHERE Completed = 0
and
Batch B:
UPDATE Orders
SET ...
- 233
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 ...
9
votes
4
answers
758
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 ...
- 4,295
9
votes
2
answers
2k
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 ...
- 213
9
votes
1
answer
4k
views
Finding the default isolation level in SQL Server 2017 and Azure SQL DB
I'm reading a book related to transaction and concurrency. In one paragraph it is mentioned that:
In on-premises SQL Server instance the default isolation level is Read-Committed based on locking
...
user123215
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 ...
- 193
9
votes
1
answer
16k
views
SQL server temp table using # - is it only accessible by the containing query for multiple connections with the same login?
Say we have 2 completely different queries, that reference a temp table with the same name:
query one
....operates on:
#tempTableName
query two
....operates on:
#tempTableName
I've ...
- 255
9
votes
1
answer
18k
views
How to view full blocking postgres queries?
I'm using a statement from this page to view blocking queries in Postgres
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
ka.query ...
- 559
9
votes
2
answers
15k
views
Drop an index in postgresql
I had created an index wrongly and now I am trying to drop that index. Since the table is large, dropping the index is taking lot of time. Is there any other way to drop the index quickly?
user2659199
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 ...
- 946
8
votes
1
answer
2k
views
Does UPDLOCK guarantee concurrency
I have a table called tblOrderNumber which has 1 row and 1 column. This table stores what the next order number will be for my ecommerce website. It is ABSOLUTELY VITAL that the same order number is ...
- 225
7
votes
1
answer
1k
views
What are the problems with using NOLOCK given an approach to handle the short comings?
I am researching the harm of using NOLOCK SQL to load data from one database that is actively used into a reporting database. I understand that there are problems with using NOLOCK but I am thinking ...
- 3,225
7
votes
3
answers
12k
views
Concurrent MySQL updates hang with InnoDB (on Amazon RDS)
I'm running into a problem where multiple MySQL updates being performed at the same time will lock up and takes several minutes to finish. I'm using InnoDB, so I'm confused as to why this could be ...
- 205
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 ...
- 555
7
votes
2
answers
3k
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 ...
7
votes
2
answers
430
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 ...
- 173
7
votes
2
answers
18k
views
Way to do MERGE with update source when target not match?
I am trying to write a stored procedure that will take new values from a temp table, merge them into an actual table, giving me the ids of the inserted rows, and the rows that already exist matching ...
- 173
7
votes
2
answers
391
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 ...
- 9,242
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 ...
user129291
6
votes
2
answers
3k
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 ...
- 163
6
votes
2
answers
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 ...
- 61
6
votes
2
answers
610
views
Tuning Postgres for Single connection use? Or is postgres wrong tool?
Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions?
I'm a social ...
- 213
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 = ...
- 741
6
votes
1
answer
5k
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,538
6
votes
3
answers
1k
views
Handle concurrency when insertion depends on reading
[Short]
I have the following situation: user A attempts to insert data DA into the database. To check whether user A is allowed to insert DA, I need to run a query and do some computation. The ...
- 225