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
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 ...
1
vote
1answer
2k 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
591 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
161 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
109 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 ...
1
vote
1answer
32 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
43 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: ...
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
613 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,...
1
vote
1answer
87 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
579 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
1answer
1k 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
743 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
1k 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 ...
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 ...
0
votes
1answer
106 views

SQL Server Concurrent Insert/Update and Storing Data

From: SQL Server 2014 Concurrent input issue Follow up question: Table Orderline stores information we receive from shops. OrderLineID is the unique, primary key in the table, since orders have ...
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 ...
1
vote
1answer
758 views

SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning

We are conducting parallel, multithreading inserts in SQL server tables, and want to reduce latching. What are the disadvantages of utilizing hash partitioning to reduce latching? Is it reduced ...
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 ...
0
votes
1answer
28 views

_Batch_ priorities in PostgreSQL

I would want to tag many queries and mostly materialised view refreshing tasks as low priority in my RDBMS, in the fashion I run the web browser on the local machine as low priority (because JS, CSS, ...
1
vote
2answers
125 views

Postgres serializable snapshot isolation for jsonb

I'm trying to acquire a serializable lock on a JSONB column, but it looks like by default JSONB supports only row level lock on transactions. Basically I want to store JSON like: {a:"v1", b:"v2"} ...
8
votes
1answer
13k 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 ...
0
votes
2answers
373 views

Real-time deduplication strategy for relational DB

I have a messages table with some fields like id, version, timestamp I'm trying to insert and retrieve messages with real-time deduplication logic, implemented in stored procedures. On insert, if a ...
1
vote
2answers
2k views

SQL Insert with soft row limit in stored procedure

I have a table ([Label]) which i would like to enforce a soft limit on the number of rows a company can insert (stored procedure code below) If this stored procedure is being called frequently (e.g. a ...
2
votes
1answer
2k views

Concurrency in NoSQL

I am planning on writing a webapp where a user can buy an object from a seller. Also I would like to outsource the payment so that I don't have to deal with the payment process myself. I was wondering ...
3
votes
2answers
2k views

Adding a nullable column concurrently with an update or insert

In SQL Server, when adding a column that allows nulls to a live SQL Server database with millions of rows, what happens if an insert or update is occurring at the same time as the DDL update? For ...
1
vote
2answers
2k views

Update Query DeadLock in SQL Server when ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled

Scenario: We had learnt that the read operations are locked by MSSQL, causing other operations to slow down. Later, we decided to turn on two factors. ALLOW_SNAPSHOT_ISOLATION and ...
0
votes
1answer
48 views

Help troubleshooting mysql deadlock

We have a query, which is trying to update a row, placing a user at the end of a queue. To do this, it needs to grab the MAX(initialPositon) and set this row's initialPosition to MAX(initialPosition)+...
-1
votes
2answers
415 views

Postgres 40001 Exception Conditional Insert

I have 3 tables, A,B and C. A function actOnABC(i) which is a read only operation on the tables: IF actOnABC(i) = 0 THEN RAISE EXCEPTION 'Message'; ELSE INSERT INTO TABLE B VALUES(...); END ...
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....
1
vote
1answer
802 views

Database table as a queue, order by another table's column

I have two tables. One table serves as the queue from which multiple users simultaneously dequeue items one at a time, by flagging the item's row. The other table is joined and used for ordering. It ...
1
vote
1answer
110 views

How to verify the index on a table was created “Concurrently”

Normally after I create an index, I check pgAdmin to verify that the index was created correctly. However, I'm not sure how to verify if the "concurrently" argument I passed in went through since I ...
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 | ...
0
votes
1answer
54 views

Concurrent usage of rdbms, write 4K rows without lockdown [closed]

I am working on a django project using Postgresql. The use case is as follows: A user needs to upload an excel (containing 2.5-4K rows) 2-3 times daily. There will be 100s of users (somewhere between ...
0
votes
1answer
140 views

Transaction - Timestamp Ordering. When is aborted transaction restarted

(I don't know if this is the correct place to ask, since this is more like a theoretical question). I have a question about restarting aborted transactions. I don't know when / at which point should ...
2
votes
1answer
128 views

SQL Server Object Concurrency

Background: SQL Server Version 2014 and the design is an invoice order system. I have a few questions to confirm my thoughts on SQL Server concurrency. As a brief background, the developer who ...
3
votes
1answer
220 views

SERIALIZABLE transaction information leak via serial

I'm trying to implement a deterministic configuration for database sessions, and one problem seems to be with sequences. The following example demonstrates: CREATE TABLE foo (id serial, n integer); -...
5
votes
3answers
673 views

Why is NOLOCK implemented like that?

Recently I have searched for NOLOCK option in SQL Server. What I've discovered is that when a transaction is active on a table, SQL Server does not allow even reading from a specific table until the ...
1
vote
2answers
295 views

How can I insert into a Destination Table from multiple tables concurrently..?

I have a monthly single threaded process that restores about 2,000 databases and pumps data into a table then drops the DB. It takes about 18 hours for the insert to complete (Its via an SSIS Package)....
1
vote
1answer
3k views

How to handle concurrent write access to two related tables

Suppose there are two tables t1 and t2. t1 has a boolean column ct1. There are two scenarios: If ct1 is false, create new entries in t2 and make ct1 true If ct1 is true, just return How to handle ...
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
0answers
450 views

Failed to run query: error: 1023 on Redshift

I have a lambda : 'use strict'; const config = require('./redshift_config'); const pg = require('pg'); const redshift_conn = `pg://${config.user}:${config.password}@${config.host}/${config.database}`...
2
votes
1answer
2k views

postgresql update with a subquery limit 1 that has joins sometimes doesn't respect the limit?

As per Postgres UPDATE ... LIMIT 1 I'm trying to update a single record but based on a join. However, ~30% of the time on the pre-installed postgresql 9.5.5 from travis-ci the following SQL actually ...
1
vote
0answers
611 views

Is a postgresql subquery in autocommit mode part of the same transaction or a different one?

I can't find anything explicit about this and I'm not sure whether "statement" in the documentation refers to an individual select or input text: By default (without BEGIN), PostgreSQL executes ...
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
1answer
186 views

TSQL and Table Lock on one row

So we have a table that is being used for concurrent access across a number of threads and processes. For certain actions, we want to get an exclusive table lock on the entire table, blocking all ...
6
votes
3answers
470 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 ...
4
votes
2answers
2k views

MySQL: Deadlock in a single table UPDATE…WHERE

The next issue raises with this configuration: MySQL 5.7.10 Spring 4.0.5 Spring Batch 3.0.1 Spring ThreadPoolTaskExecutor between 10 and 20 threads The issue is a deadlock when some threads try to ...
0
votes
1answer
60 views

SQL Server access table at a time [closed]

Multiple users access to write on the same table on same time on same record. How to avoid this problem in SQL Server? Already we implemented record locking. But the record locking will be be enable ...
2
votes
2answers
162 views

Handle concurrency on Sql Server to modify specific column

I have table that suffers from racing condition and I don't know how to fix it. I can't solve it 100% in code, so I need the server to help me. The scenario Admins of a system may accept or reject ...