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
2answers
2k views

Return the id after insert or select

I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this? Also how can I return the id if the email was ...
1
vote
2answers
291 views

ACID violation in Postgres 9.1?

I am using a postgres DB to implement a scheduling of jobs for a large number of computers/processes. To make the story short, every job has its id, all scheduling is implemented with three tabes: all ...
1
vote
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, ...
1
vote
1answer
63 views

PostgreSQL optimistic reads (equivalent to SQL Server's Read Committed Snapshot)

Does PostgreSQL provide a built-in optimistic concurrency control mechanism such as SQL Server's Read Committed Snapshot Isolation? Readers can read all the rows without blocking, but writes will ...
1
vote
1answer
213 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
2answers
460 views

DB Locks - How would one handle long transactions and maintain high concurrency?

We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle ...
1
vote
1answer
123 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
31 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" ...
1
vote
1answer
312 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
130 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
2answers
57 views

SQL Server Concurrency Issue [closed]

We have a SQL server concurrency issue we are facing and would like some ideas on how to solve it. There are several procedures we have written the contain INSERT/UPDATE statements on tables in a Set ...
1
vote
1answer
49 views

Can sequential queries result in concurrent transactions?

I'm using PostgreSQL 9.4.4 and the default configuration. I use a JDBC connection to run multiple queries in succession. Only one Java thread is using the connection and before a new query is executed ...
1
vote
1answer
228 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
2answers
1k 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 ...
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
2answers
839 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) ...
1
vote
1answer
221 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
781 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
71 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
1k 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
134 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
27 views

How to make a PostgreSQL trigger thread-safe

I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't ...
1
vote
0answers
34 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 ...
1
vote
0answers
538 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
314 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
0answers
200 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 ...
1
vote
1answer
72 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
234 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
915 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
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
29 views

What does this deadlock report from MySQL mean?

Here is the output: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2015-12-23 05:57:47 7f23d73c6700 *** (1) TRANSACTION: TRANSACTION 32990178, ACTIVE 0 sec inserting mysql ...
0
votes
2answers
100 views

How to manage concurrencies without locking tables in MySQL?

In our php app, we have a table that records orders recieved at the reception. Since we can have up to 7 agents/active accounts, how is it possible to manage the concurrencies in the orders table ...
0
votes
1answer
37 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
1answer
488 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
1answer
471 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
188 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
43 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
68 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
1k 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
244 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
335 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
4answers
352 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 ...
0
votes
1answer
409 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
213 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
0answers
15 views

How to lock tables in a stored program if I can’t say LOCK TABLES? (in an ancient version of MySql)

MySQL does not allow LOCK TABLES in stored programs. But this is my problem: I have a large stored function to control “phases”. The function advances to next phase, if needed (rarely so), and ...
0
votes
0answers
14 views

PostgreSQL - Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

I'm having a locking issue on PostgreSQL 9.4.5. I run two or more transactions simultaneously (generally there are 2 running at the same time, but 2+ may happen sometimes) reading from the same tables ...
0
votes
0answers
51 views

How to determine if a schedule is non serializable?

How to determine if a schedule is non serializable? I am aware that a schedule is serializable if there is a concurrent schedule which gets an output same as a serial schedule. But how do you ...
0
votes
0answers
28 views

Understanding Serializeable vs conflict-serializable

I am having hard time wrapping my head around this concept of serializable. I know that if a schedule is conflict-serializeable then it is a conflict-equivalent to some serial schedule. But then does ...