Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [concurrency]

For questions about issues arising when multiple users or processes concurrently access or modify shared information in a database system.

0
votes
0answers
23 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
14 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
111 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
19 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
52 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 ...
2
votes
2answers
67 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
39 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
44 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
38 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
22 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
53 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 ...
0
votes
1answer
82 views

How to design a database-Excel connection for read-write by multiple users? [closed]

In my project users maintain data in Excel tables (with proper row and column headers). With time however, the Excel tables get pretty large. The amount of numerical data is not too large (~10mb per ...
4
votes
3answers
233 views

Does MERGE prevent deadlocks and server blocking?

I have started looking at MERGE as an option that I would use in my application to process UPSERT transactions. Seems that a lot of SQL Server experts are recommending this approach. However, while ...
0
votes
0answers
31 views

Setting up a Database for Multiple User Access

I am working on revamping the database at the company I work at. I've been making decent progress getting various parts set up, but something occurred to me today that I hadn't really thought to ...
3
votes
1answer
243 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 ...
2
votes
2answers
79 views

Why is column value going negative even with checks

I have a simple MySQL table with 3 columns id, balance and locked. I have a requirement where the locked column's value must be increased by x, sometimes by multiple processes running at the same time,...
5
votes
3answers
652 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 ...
1
vote
1answer
104 views

Sporadic Gap-lock related Deadlocks under high concurrency in MySQL 5.7

Under high concurrency when running a daily process overnight we see deadlocks occur on a particular table, which seems to reference that it was holding a gap lock at the time. However I'm unable to ...
1
vote
0answers
89 views

MongoDB write locks on insert

Why would an insert into a MongoDB 3.4 collection require global write locks? I'm creating a copy of a large collection in a new replica set (via mongodump/restore) and I'm finding that some inserts ...
1
vote
1answer
21 views

How to concurrency will be handled in this case?

Say I have employee table with 100 records . Out of 100 records, 60 records having middle_name as null Say in two separate sessions(say Session_A, Session_B), I execute below query at same time ...
3
votes
2answers
110 views

MySql concurrency with COUNT

I Have a table with user_id and product_id. For my purpose, a particular user is allowed up to 5 rows only. To enforce this, I created such a SQL statement: INSERT INTO the_table (product_id, ...
0
votes
2answers
54 views

Is it possible to make a SELECT wait until an ongoing UPDATE finishes?

Suppose that I am implementing a bank account logic using PostgreSQL. At some point, I will want to check if the user has sufficient balance to perform an operation. However, the user's balance might ...
3
votes
2answers
309 views

Contention on tempdb due to high concurrency temp table creation from multiple databases?

I have an environment with ~100 databases, all with the same schema. There is a stored procedure on each database that creates a #temp table and drops it, and is running very often (every 30 seconds ...
0
votes
0answers
144 views

Understanding multiversion timestamp ordering protocol in database systems

I was reading multiversion timestamp ordering protocol from the book Database Systems Concepts by Korth et. al. It can be explained in simpler words as follows: Each data item version Q has two ...
0
votes
2answers
415 views

the role of “ Intent lock ” in transaction and concurrency

According to the book Microsoft Sql server 2012, Chapter 13 (Transactions and concurrency): If an exclusive page lock is held in one page of a table, another process can not get even a shared ...
1
vote
2answers
145 views

SQL Read Committed Snapshot Isolation in Database with Selects and Inserts Only

We have database with only 1 table, which conducts Inserts and Selects. There are No Updates in the table. Table has lot of Concurrency; with many simultaneous Insert and Select queries occurring. ...
11
votes
6answers
2k 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 ...
7
votes
1answer
1k 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 ...
1
vote
2answers
313 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) ...
-1
votes
1answer
71 views

SQL Snapshot Isolations Phantom Rows

Does Read Committed Snapshot Isolation prevent phantom row issue? Does Snapshot Isolation prevent phantom row issue? I am seeing different results in researching online: Akira Yamamoto states: ...
0
votes
1answer
71 views

SQL Server Simplify Racing condition query

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 wanted to ...
1
vote
1answer
167 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 ...
0
votes
0answers
46 views

SQL Server Blocking issues

We are facing an issue in our top-up system which is SQL Server blocking resources. The scenario is every API user (third party system) has a single account in our database, when many requests came ...
1
vote
1answer
764 views

UPDATE with subquery LIMIT 1 returns multiple rows [duplicate]

I am struggling with a query which is randomly returning multiple rows instead of one. I am using PostgreSQL 9.4.12 The query is the following : UPDATE folder as f set locked = 'locked', ...
1
vote
1answer
322 views

How to create some GIN index concurrently in Postgresql

I have a large table: CREATE TABLE hh(h int8[] not null, file int8 not null), and GIN-index over h field (CREATE INDEX ON hh USING gin(h)). Index was created is about 8 minutes, so I decide to split ...
0
votes
1answer
84 views

Does Write-Ahead-Logging allow me to make concurrent changes to a table?

In Sqlite3, Write-Ahead-Logging mode allows one process to read a table, while another is writing to it. It doesn't say anything about one process writing and another deleting/updating. Is this ...
1
vote
0answers
95 views

conditional UPDATE race conditions

In a conditional UPDATE like this: UPDATE MY_TABLE SET my_coulmn = "my value" -- many other updates WHERE ... -- many conditions Do I have any guarantee that I will only update those records ...
0
votes
0answers
20 views

Can Lamport's Bakery algorithm be used for controlling concurrency in soft real time database system (case study of stock trading)?

Currently, I'm doing a research on concurrency control on mixed soft real time database(case study of Stock Trading) an idea I got after reviewing much literatures. Given the fact that stock trading ...
0
votes
0answers
14 views

Conflict serializable Database transactions?

Consider the following transactions! When this is made like this!: T1 | T2 (T2 starts later) r(x) | | r(z) r(z) | | r(x) r(y) | | r(y) w(z) | | w(x) ...
1
vote
1answer
26 views

Concurrent Users and Foreign Keys

For this question, please assume the following: I am using MySQL I have a Location table: Location(ID, Name, Address, TravelDetails, ...) I have an Event table: Event(ID, Name, LocationID, StartTime, ...
0
votes
1answer
37 views

Changes made by TransactionA not seen by TransactionB eventhough TransactionA commits them. How is that possible?

Is it possible for 2 non concurrent connections to see the same data for a record even as the 1st connection commits its changes? Here is an example of what I am seeing from the audit logs: ...
0
votes
0answers
160 views

SHOW ENGINE INNODB STATUS - SEMAPHORES

I am using MariaDB 10.2.11. In my "SHOW ENGINE INNODB STATUS" command output I find teh following SEMAPHORES statistics. I find a few OS waits. My OS is CentOS-7.4 64bit. Would like to understand if ...
3
votes
1answer
602 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 ...
1
vote
1answer
296 views

Transaction log records in sql server

Based on Silberschatz's Database system concepts book, log file for a transaction has 3 records, 1 record for transaction start: T_i start, 1 for transaction changes on write(x) operation: T_i,X,...
0
votes
1answer
60 views

Read after transaction from secondary session not visible

We've got a database (MySQL ) that has a client 'per channel'. When a player changes channel from, say, 1 to 2, we have to save the state of the player to the database. Saving is done in multiple ...
0
votes
0answers
277 views

Concurrency with triggers in mysql

I'm trying to implement a hierarchy using closure tables maintained by triggers. I'm using MySQL 5.6. I know we should move out of the 90s and use a database that actually supports CTEs, but this is ...
3
votes
0answers
478 views

Postgres performance for running 100 queries concurrently [closed]

I am performing time series analysis on a database which has 4 tables +------------------+-------------+---------+ | TABLE | ROWS | SIZE | +------------------+-------------+------...
3
votes
1answer
794 views

SQL Concurrent Inserts and Automatically Find Active Flag Record

What is the proper database design to Instantaneously find Active Flag for an OLTP table: with many rows being inserted into a parallel, multithreading environment while allowing optimization, and ...
3
votes
1answer
422 views

SQL Server Concurrent Delete/Insert Application

How do we delete and then insert rows in a parallel, multithreading environment while avoiding deadlocks? I am still receiving deadlocks after applying UPDLOCK and SERIALIZABLE. See deadlock error ...
0
votes
0answers
519 views

AUTO INCREMENT concurrency issue and ordering

Let's suppose that table t1 has an AUTO INCREMENT primary key id. And let's say that I am starting two transactions at different moments of time, and that transactions are started by the same ...