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
2
votes
1
answer
833
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 ...
user129291
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 ...
user129291
0
votes
1
answer
48
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, ...
- 66
1
vote
2
answers
213
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"}
...
- 113
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
0
votes
2
answers
439
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 ...
- 101
1
vote
2
answers
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 ...
- 11
2
votes
1
answer
4k
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 ...
- 23
3
votes
2
answers
5k
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 ...
- 31
1
vote
2
answers
3k
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 ...
- 111
0
votes
1
answer
57
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
-1
votes
2
answers
997
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 ...
- 99
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
1
vote
1
answer
1k
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 ...
- 121
1
vote
1
answer
193
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 ...
- 351
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 | ...
- 371
0
votes
1
answer
62
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 ...
- 143
0
votes
1
answer
282
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 ...
- 1
2
votes
1
answer
154
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 ...
- 121
3
votes
1
answer
292
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);
-...
- 31
5
votes
3
answers
698
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
2
answers
448
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)....
- 61
1
vote
1
answer
4k
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 ...
- 195
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(*)
...
- 60.5k
3
votes
0
answers
559
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}`...
- 621
3
votes
1
answer
3k
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 ...
- 261
3
votes
0
answers
1k
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 ...
- 261
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
3
votes
1
answer
292
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 ...
- 155
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
4
votes
2
answers
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 ...
- 141
0
votes
1
answer
68
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 ...
- 19
2
votes
2
answers
221
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 ...
- 225
3
votes
1
answer
4k
views
Handle concurrency on sql server for a booking system
I've been banging my head against the wall trying to solve this problem and I couldn't come up with a solution that absolutely guarantees two users can't book an appointment for the same time.
The ...
- 225
1
vote
1
answer
344
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 ...
- 255
4
votes
0
answers
325
views
Performance impact of having Polymorphic collections in MongoDB?
Say one document(Level 1) is supposed to have N(k) different kinds of items as sub-document arrays(Level 2), and these are stored in a separate collection(s?) due to lack of query optimisation for ...
- 141
3
votes
1
answer
2k
views
Are PostgreSQL cursor bound to connection?
I would like to confirm a point with PostgreSQL cursor.
I have to implement a select factory for huge result set query (more than 1Go of small-weight rows). Thus I have learn about cursor and I ...
- 337
2
votes
0
answers
158
views
Concurrent Merge Issues
A (simplified) model of my application has several processes dumping data into the table below:
CREATE TABLE [dbo].[InputData]
(
[InputId] [int] NOT NULL,
[Dts] [datetime] NOT NULL,
[...
- 53
1
vote
1
answer
910
views
How do I select the next row in SELECT FOR UPDATE?
I am modifying my web crawler program to use SELECT FOR UPDATE to avoid multiple crawlers (with multiple children) pulling the same row to process.
How do I do this?
select * from visited where ...
- 11
3
votes
1
answer
392
views
Concurrent reads and updates
Let's say I have a table with 50,000 transactions that need to be processed in the background in the same order they were received. I have a Windows service that polls for transactions, picks them up, ...
- 31
2
votes
1
answer
2k
views
How can CREATE OR REPLACE fail with already exists?
DETAIL: Key (proname, proargtypes, pronamespace)=(rand, , 2200) already exists.: CREATE OR REPLACE FUNCTION "rand"() RETURNS float AS
How on earth is that even possible? How can CREATE OR REPLACE ...
- 417
2
votes
1
answer
1k
views
Rowversion database lock performance
As the documentation says, rowversion data type is auto-updated every time a row is updated using a global auto-incremented number within the entire database scope.
My question is the following: Say ...
1
vote
1
answer
248
views
What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?
I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the ...
- 621
5
votes
1
answer
3k
views
Update with CTE & OUTPUT blocks on concurrent calls
I was having deadlock issues, so I started using this CTE trick that I read somewhere. No more deadlocks.
But now each client that I add slows down (blocks?) the stored procedure. E.g. a 1-2 second ...
- 205
3
votes
2
answers
740
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 ...
- 31
1
vote
1
answer
817
views
Updlock hint in stored procedure
If someone runs stored procedure below very fast (few thousand execution a second), can variable @amount change while executing and execute update more than one time?
CREATE PROCEDURE [dbo].[...
- 48
0
votes
2
answers
159
views
Mysql locking - How to acquire a lock in the DB for each userId
Hi I have a J2EE Server(clustered) that executes requests from multiple users.
I would like that each request of a given user will be handled like so:
{
acquireLockForUser(userId); // i.e. insert ...
- 355
1
vote
1
answer
2k
views
Using @@ROWCOUNT in multi-operation batch to check either a failure or a success of the whole transaction
I have the following script defined with hopefully explanations in comments to explain rationale in the right context. The questions are basically:
Is the following usage of @@ROWCOUNT in conjunction ...
- 259
2
votes
1
answer
156
views
Paged query against timestamp populated by trigger missing items
We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out.
First some background. We have a "tombstone" table that is populated using a ...
- 141
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