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)

0
votes
0answers
15 views

Confusion of Time Stamping Protocol and Two Phase Locking Protocol in schedule

I'm wondering if someone helps me. Actually, I have a little confusion in Concurrency Control Time stamping protocol and Two-Phase locking protocol in a transaction schedule. I have found this ...
3
votes
0answers
35 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, [...
1
vote
1answer
30 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 ...
1
vote
1answer
284 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 ...
2
votes
1answer
53 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, ...
1
vote
1answer
51 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 ...
1
vote
0answers
29 views

Which database supports multi-user write or Concurrent writes [closed]

Can Anybody help me I was using SQLite database but it does not support multi-user write or concurrent writes or simultaneous multiple writes. I want to Ask which reliable Sql or non-Sql database ...
2
votes
1answer
59 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 ...
0
votes
1answer
13 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 ...
2
votes
1answer
77 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 sproc. E.g. a 1-2 second update with ...
3
votes
1answer
57 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 ...
18
votes
6answers
480 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 ...
28
votes
1answer
12k views

Postgres UPDATE … LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't ...
1
vote
1answer
66 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].[...
1
vote
1answer
26 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 ...
6
votes
1answer
301 views

Locking in Postgres for UPDATE / INSERT combination

I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once. The user needs to be able to redeem a coupon, which will insert a row into the log table ...
0
votes
1answer
22 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 ...
1
vote
1answer
33 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 ...
3
votes
0answers
113 views

Read/Write Deadlock on Ms-Sql Server

We are facing deadlocks when store procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of sp. I have ...
0
votes
0answers
64 views

Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished? I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until ...
3
votes
1answer
52 views

Use large set of data in multiple databases

I have been thinking about this a lot but somehow can't figure out how to deal with it in an efficient matter. Say you have a large set "X" of data that consists of multiple tables. For the sake of ...
0
votes
0answers
13 views

Isolation Level Impact on Selecting a Row Moving Between Two Tables

I have two tables (identical structure, one for current data and one for archive) being used as a source for a datamart staging table. I construct a SSIS data flow that runs SELECTs on both tables ...
0
votes
0answers
27 views

MySQL lock on READ and concurrent execution

I'm building a small application (in Java) and I have arrived to a point where, depending on a field in one my database table the action to be executed has to be completely unique. I have a ...
1
vote
1answer
2k 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
2answers
59 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
107 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 ...
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
40 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 ...
1
vote
2answers
532 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 ...
6
votes
1answer
375 views

What happens when we add an index to an existing table with a large amount of data?

I've a table which will contain around ~15 Million records. Now I need to add an index to the table. Adding an index will take some time to update every entry in the table. I'm quite confused ...
6
votes
1answer
195 views

Does UPDLOCK guarantee concurrency

I have a table called tblOrderNumber which has 1 row and 1 column. This table stores what the next order number will be for my ecommerce website. It is ABSOLUTELY VITAL that the same order number is ...
1
vote
0answers
63 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 ...
3
votes
1answer
106 views

Why am I getting old data?

Today I discovered something weird on PostgreSQL 9.5. (I have no idea whether this is because of beta or not.) When I want to fetch data, I get old and deleted data from query. I then do VACUUM FULL ...
0
votes
1answer
49 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
0answers
88 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
30 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 ...
1
vote
1answer
73 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 ...
4
votes
2answers
162 views

Best practice regarding concurrency for INSERT into a table with composite primary key?

Say I've got the tables users, teams and a teams_users junction table (team_ID, user_ID / composite PK). If I wanted to add a user to a team, what's the best option when it comes to performance / ...
3
votes
1answer
240 views

Work queue with complex select and long processing: Ensuring concurrency

I have a queue table from which worker processes claim records, one at a time. It is crucial that no two workers claim same item to process. Normally this kind of thing is done with select top (1) ......
0
votes
2answers
158 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 ...
3
votes
2answers
187 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
0
votes
1answer
28 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
46 views

MySQL: write in one connection then read in another

Say I have a library function, exec_sql(some_sql), every time it's been called, it obtains a connection from a connection pool, execute the sql using that connection, and then release the connection. ...
4
votes
2answers
3k views

How to avoid a “duplicate key” error?

This statement: INSERT INTO deleteme SELECT #t.id, 'test' FROM #t LEFT JOIN deleteme ON deleteme.id = #t.id WHERE deleteme.id IS NULL; ...will fail with a primary key violation in the concurrent ...
0
votes
0answers
71 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 ...
1
vote
1answer
53 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
0answers
121 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 (...
2
votes
1answer
440 views

Does PostgreSQL support online schema modification (DDL)?

Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table. Does PostgreSQL support this to any level? That is, can I add/drop columns from a table ...
3
votes
1answer
346 views

Any reason not to add 'rowversion' column?

The TechNet article, "Optimizing Microsoft Office Access Applications Linked to SQL Server", recommends adding a rowversion column to SQL Server tables linked from MS Access. From the Supporting ...
1
vote
2answers
2k views

Global Temporary Table used in Stored Procedure executed concurrently across sessions

I use dynamic SQL in a stored procedure and must use a global temp table so the temp table and its data is available outside the instance when sp_executesql happens. I need the stored procedure to be ...