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

learn more… | top users | synonyms

1
vote
1answer
169 views

Improve performance on concurrent UPDATEs for a timestamp column in Postgres

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here. The queries all have the same format UPDATE "managers" SET "updated_at" = ...
1
vote
1answer
108 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 ...
1
vote
1answer
264 views

How to insert and or update a variable number of records and prevent concurrency issues

I currently have the following stored procedure. However I am seeing some race conditions where I end up with incorrect data which I think is caused because the procedure isn't attomic and it can be ...
1
vote
1answer
125 views

Can a Database request automatically remove itself from the queue after a delay?

I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again. If many instances are run and requests are made ...
1
vote
1answer
69 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 ...
1
vote
1answer
42 views

Is it possible that records are changed while a read query is still executing?

Suppose a read (select) query R is reading some 100 records from a table, 50 records are read so far, could in the meanwhile a write query W change some of the unread records? We didn't take any ...
1
vote
1answer
199 views

timestamp based concurrency

I am wondering if someone could help me out with a question I have about time stamp based concurrency. I found the following practice problem on a website It basically asks which of the following ...
1
vote
1answer
695 views

Which Optimistic Concurrency Control method will work out best in the long run?

These are the options I've thought of: DotNet Dave: UPDATE mytable set f1=newval where id=666 and f1 := f1ValueFromSelect Charlie Checksum: UPDATE mytable set f1=newval where id=666 and ...
1
vote
2answers
48 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 ...
1
vote
1answer
630 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 ...
1
vote
1answer
113 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 ...
1
vote
1answer
2k 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. ...
1
vote
1answer
2k views

Differences between SQL Server 2005 and SQL Server 2008 deadlock handling

We recently upgraded a server to SQL Server 2008 R2. The server had been running SQL Server 2005. We are using Transactional Replication Management Data Warehouse and Data Collector on the ...
1
vote
0answers
221 views

How to identify concurrency issues in Oracle 11g?

For a short period in time we had a concurrency issue that lead to subsequent faults in our application (Java, Spring, Hibernate). Since we are using dictionary managed tablespaces, I first thought ...
1
vote
0answers
256 views

High concurrency is even slower than low concurrency in mysql? [closed]

My application is supposed to handle high concurrency insert/update, now I have only one mysql instance setup for test. My application is full asynchronous, it should be able to handle 10,000 ...
1
vote
1answer
71 views

Patterns/Designs to help with concurrency/performance [closed]

I'm working on a SQL Server 2008 R2 DB and I'm experiencing some performance/concurrency issues with probably the main table in the DB. Problems like slow reads, deadlocks, poor performance in ...
1
vote
0answers
205 views

UPLOCK to avoid multiple reads

Given the following table structure... Orders ====== OrderId int PK OrderEvent =========== OrderEventId int PK OrderId int FK EventTypeId int FK I've got multiple concurrent processes SELECT-ing ...
0
votes
1answer
2k views

Why are timestamps not always increasing with concurrent inserts?

I'm seeing some unexpected behavior with timestamp (rowversion) columns . I created a test table: create table Test ( Test_Key int identity(1,1) primary key clustered, Test_Value int, ...
0
votes
1answer
22 views

PostgreSQL visibility of records changes from other transaction inside the current one

I have a table "tst" which has a "status" column using which we process records in that table. Once the records are processed we set the status to 'Y'; #\d tst Table "core.tst" ...
0
votes
1answer
34 views

Is reading a single record always an atomic operation?

Is reading a single record always an atomic operation? I mean, when e.g. a read (select) and write (update) query are simultaneously carried out on the same record, is it possible for the read ...
0
votes
2answers
2k views

How to maintain MySQL performance at high concurrency

The following has been observed using "mysqlslap". mysqlslap -u root -p --create-schema=matrix --query=monthly_kpi.sql --iterations=1 --concurrency=1 --delimiter=";" Benchmark Average number ...
0
votes
1answer
377 views

How to create fault tolerant databases? [closed]

I have a local application running on a LAN. Sometimes a database gets shutdown. In such a scenario how can I keep the application working? Can I have two concurrent databases so that one picks up the ...
0
votes
2answers
714 views

MySQL concurrent INSERTs

I have a MySQL database with InnoDB tables. There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) ...
0
votes
1answer
430 views

How could I handle heavy load and consistency for product inventory in online retailers? [closed]

I have posted that question on Stackoverflow and they suggest me to repost my question here, as it would more likely to get help from a DB community (regarding this issue). Hope you guys don't mind. ...
0
votes
1answer
81 views

How to execute two transactions concurrently in PostgreSQL?

I want to execute two transactions concurrently in pgAdmin: --The first transaction should modify the age of Paul BEGIN; DECLARE newage INTEGER; --the original age of Paul is 32 newage := Select age ...
0
votes
1answer
33 views

How to serialize on SQL server metadata?

I tried to find an answer in Kalen Delaney's SQL Server Concurrency but there is no mention of metadata. The problem is how to ensure that only one of these reads and index creates executes from one ...
0
votes
1answer
62 views

How to avoid duplications using a function

I am using this function in a bulk insert to avoid duplication of url paths. CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4) RETURNS "int4" AS $BODY$ DECLARE ...
0
votes
1answer
639 views

Oracle Enterprise Manager - top activity

I'm looking at the Top Activity view in OEM. The left axis is labeled "Active Sessions" (up to 24 in the graph I'm looking at) and there is a line labeled "Maximum CPU". The measured activities ...
0
votes
1answer
489 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 ...
0
votes
1answer
225 views

What causes lost updates being observed in my SQL Azure database?

I have a SQL Azure database with the following tables: CREATE TABLE [dbo].[UserBalances] ( [UserId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(), [AvailableMoney] INT NOT NULL ...
0
votes
1answer
237 views

When and how would MyISAM concurrent insert = always be a problem?

I wish to set concurrent_insert to always on a MyISAM log table, so that inserts wouldn't have to wait for selects to perform when there are some holes in the table. We're talking about 10-20 inserts ...
0
votes
1answer
363 views

Moving a collection to a new database (v2.2)

To take advantage of the improved concurrency in Mongodb 2.2--namely database level locking--we would like to move a sharded collection to a new database. I have been unable to find any documentation ...
0
votes
1answer
208 views

Why we use low numbers for concurrent in MySQL benchmarks or HTTP_LOAD? [closed]

I've seen so many times that we use numbers like 5,6 or 10 at most. What is this concurrency? Doesn't it refer to number of simultaneous users in bench? EDIT I'm not talking about any specific ...
0
votes
1answer
15 views

concurrent insertion in mutually exclusive tables in oracle

There are three tables: create table C(id_row integer primary key) create TABLE A(col_a1,col_a2,col_a3,..., id_row integer primary key, constraint fk_a_c foreign key (id_row) references C(id_row)) ...
0
votes
0answers
38 views

MYSQL- concurrency issue

I use JDBC to connect to MySQL. order_items table order_item_id order_id quantity unit_price shipping_price business_id workflow_id delivery_id item_id Orders table billing_address_id ...
0
votes
0answers
37 views

Is it a good idea to have too many named locks?

I am using MySQL as a data store and a Java App on the top. There are many state changes that happen, and I am seeing some concurrency bugs in there. I wanted to know if I can use named locks ...
0
votes
0answers
20 views

Can a slow SELECT statement in READ COMMITTED isolation level read content from concurrent committed UPDATE?

Let's say we have a big table with hundreds of million of rows (or more). On one connection, we perform a query requiring full table scan under READ COMMITTED isolation level: SELECT * FROM ...
0
votes
1answer
39 views

InnoDB index concurrent inserts

I have an InnoDB table with an index on it. I have a few million rows to insert, so I thought I might take advantage of the row-level locking and do it in parallel. I'm inserting in batches of 10000, ...
0
votes
0answers
81 views

Rowversion column in every table - Bad practice?

I'm currently providing my application with concurrency checks. For that, I'm using Entity Framework combined with SQL Server's RowVersion / Timestamp column. Now I've thought about adding a ...
0
votes
1answer
224 views

Concurrent SELECT and UPDATE in multiprocessing python

I have to randomly match about 20 million rows in two tables without using the same row twice using PostgreSQL 9.3.5. Both tables have the following structure CREATE TABLE de_sim_points_end ( -- ...
0
votes
1answer
43 views

Running identical queries in sysbench

When benchmarking using sysbench and utilizing concurrency (multiple threads) for example on a table with 1,000,000 records, does sysbench run "identical" queries in a loop on the table?
0
votes
0answers
66 views

How can I obtain concurrent deletes for this table?

We have a program that is currently doing something to the effect of: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION UPDATE [Source].[dbo].[SourceTable] SET [ProcessedOn] = ...
0
votes
0answers
72 views

Why does the delete/update block each other in this case?

The following drives me crazy. I have: session1>begin; session2>begin; session2>update dummy set salary=1000 where id=11; session1>delete from dummy where id=1; id is the PK ...
0
votes
0answers
165 views

Handling concurrent updates, inserting if and only if no record already exists

I'm writing a Java application to update a relational database (currently H2, but we'll likely switch to PostgreSQL at some point). I have a process which does the following: Poll an external system ...
0
votes
0answers
77 views

MySQL Multiple threads inserting *SAME* record a Bad Thing?

Okay, as I've noted in other questions, I'm not a sophisticated database user even though I took my first course in relational algebra from ted Codd. I've now inherited an application that ...
0
votes
0answers
46 views

Oracle User Profile for REST API

What sort of profile would you recommend for an Oracle user that is manifested through a RESTful API; particularly where there will be a lot of asynchronous requests? So we're talking about a ...
-1
votes
4answers
319 views

Importing data with a lot of SELECTS during transaction

I'm using SQL server 2008 R2 and I have a products table with an auto increment integer id column as primary key and a product_no column (unique) and 6 tables like articles and product_assets which ...
-1
votes
0answers
41 views

SQL Server - Parallel Updates? (or insert/ updates)

I know MySQL allows parallel transactions/ concurrency when it comes to "insert ... on duplicate key update". However I'm not sure how to achieve this in SQL Server. Whenever an update is being ...
-2
votes
1answer
47 views

Oracle Concurrent Insertion

We have simple case like Create table t1 (c1 number primary); session1: insert into t1 values (1); //session1 do not commit transaction session2: insert into t1 values (2); Will Session2 ...