Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
127
questions with no upvoted or accepted answers
6
votes
0
answers
883
views
How do transactions affect PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY command?
PostgreSQL 9.4 supports REFRESH MATERIALIZED VIEW CONCURRENTLY.
I'm not sure, but I think the REFRESH command is viewed as DDL by PostgreSQL, and PostgreSQL wraps all DDL within transactions so that ...
- 305
5
votes
3
answers
978
views
advantages of single-leader (transactions) over multi-leader replication
I am reading the excellent book "Designing Data-Intensive Applications" which I wholeheartedly recommend, but I'm confused by a section comparing multi-leader (i.e. multi-writer) replication to single-...
- 213
4
votes
1
answer
535
views
Write-lock a whole table during transaction
I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
- 141
3
votes
0
answers
293
views
Is there a reliable way to determine the order in which Postgres transactions were committed without logical decoding?
Is there a reliable way to determine the serial order in which Postgres transactions were committed without logical decoding? From what I understand pg_xact_commit_timestamp can’t necessarily be ...
- 141
3
votes
1
answer
609
views
Commit() takes 50 seconds after inserting about 100000 rows
I have a sqlite database that is, (I think), fairly small.
I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb.
I then open a transaction and insert about ...
- 151
3
votes
0
answers
348
views
Why do two transactions making a read lock result in a DEADLOCK exception?
Let's assume a scenario that flows as follows:
Transaction #1 makes this query:
SELECT v.id, v.name
FROM versions_history as vh
INNER JOIN versions as v
ON vh.version_id = v.id
WHERE vh.project_id = ...
user162550
3
votes
0
answers
172
views
"FOR UPDATE" in an "EXISTS SELECT" statement
Is there any potential problem in using (in a transaction)
SELECT EXISTS( SELECT * FROM mytable WHERE id=36 FOR UPDATE )
i.e. having SELECT ... FOR UPDATE within an EXISTS?
MySQL does certainly ...
- 463
3
votes
1
answer
1k
views
MySQL: Skip INSERT / UPDATE statement that triggers error but show them in output
I have a million of rows of customers to be imported via SQL dump. Customer data will be inserted into main table customer and child table customer_address
The dump contains the following SQL ...
- 201
3
votes
0
answers
465
views
transaction chaining, isolation levels and atomicity
I'd like to ensure that I understand correctly how the concepts of transaction chaining, transaction isolation level and atomicity relate to each other.
Here's my understanding:
transaction chaining ...
- 3,279
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 ...
- 281
3
votes
1
answer
917
views
Help understanding InnoDB Deadlock Detection
I have a deadlock occurring when executing an Update and a Select in seperate threads but I'm not sure what can be done about it. Previously my understanding was that this could happen in parallel but ...
- 131
3
votes
1
answer
6k
views
Many transactions are getting stuck in the "cleaning up" state - what could cause that?
I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. ...
- 159
2
votes
0
answers
970
views
Postgres functions, transactions and rollback
I have a psql function that selects for update and then iterates through an array to update the table. If an error is found it correctly returns false, but does not rollback. So if the error is half ...
- 37
2
votes
0
answers
913
views
MySQL DB stuck with constantly rolling back transaction but no associated query
Ran into a problem on a website with a very slow site and tracked it down to the database responding very slowly, even for simple select queries. There seems to be spikes of activity every 9-12 ...
- 121
2
votes
0
answers
221
views
MSSQL - Session has Open Transaction without a Transaction?
I'm researching an open transaction issue in relation to database updates from processing transactional MSMQ messages (see code-side here). These are enlisted in a distributed transaction and ...
- 121
2
votes
0
answers
25
views
Transformations and source info for data ditionary
I am currently building our data dictionary for our data warehouse, but two things we would like to add is the source for the fields, and the transformations that were applied to them. Is there a way ...
- 21
2
votes
0
answers
99
views
Read READ COMMITED transaction advantage in MySQL, high performance object model
I am writing high performance object model with READ COMMITED locking.
My question is if there is any reason to starting READ ONLY transaction for read only operations vs. standard SELECT not ...
- 398
2
votes
0
answers
1k
views
PostgreSQL long transaction on a table performance
We have a long processing pipeline running on PostgreSQL 9.0.
Is it faster or slower to perform many operations on a single table within one transaction, versus doing auto-commit for each operation?
...
- 115
2
votes
0
answers
2k
views
Postgresql trigger - get all changed rows after transaction
I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction.
CREATE ...
- 121
2
votes
0
answers
1k
views
Is there a way to detect if there is an uncommitted transaction in H2?
I am using an H2 database with JDBC to create a connection with setAutoCommit(false).
I am currently programatically checking whether or not there is an uncommitted transaction, but I was wondering ...
- 195
2
votes
0
answers
137
views
Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?
Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
- 223
1
vote
1
answer
179
views
Lost connection to the database during COMMIT query: did the transaction succeed or not?
PROBLEM
I recently encountered the error 2013 in MySql (Lost connection to MySQL server during query) while executing the COMMIT statement for a transaction (the previous update statements succeeded). ...
- 11
1
vote
1
answer
283
views
Serializable isolation fails even for unrelated rows
I have this table
create table "tasks" (id SERIAL PRIMARY KEY, user_id int REFERNCES "user"(id), title TEXT);
I also created index on "tasks"(user_id)
Then I open two ...
- 111
1
vote
0
answers
289
views
Can two simultaneous DB transactions interfere with each other? ACID vs SELECT ... FOR UPDATE?
This question may be considered as a follow-up question to this one.
Imagine:
a platform
that provides a service X
to book a service X, customer Y has to pay in advance
every payment is authorized ...
- 163
1
vote
0
answers
26
views
Consistency in serializable concurrent transactions
My DBS textbook says that if each transaction individually maintains consistency in the system, in the case of concurrent transactions, serializability ensures consistency. I am not able to understand ...
- 11
1
vote
0
answers
83
views
Do the PostgreSQL pg_* catalog tables respect transactions?
Do the pg_* catalog tables and the has_table_privilege function respect transaction isolation levels? Say in a default READ COMMITTED transaction, does the below have a race condition?
SELECT
...
- 171
1
vote
0
answers
668
views
why are large sqlite transactions slow?
So in the process of writing a migration wrapped in a transaction for a local web app, I realized that the migration appears to perform inserts very fast when the transaction has a small number of ...
- 121
1
vote
0
answers
134
views
Postgresql stale result for insert into select
I've noticed when two processes (let's call them A and B) insert a record into the same table in Postgresql and don't commit those changes, the 2nd (later process, B) blocks until the 1st process (A) ...
- 153
1
vote
0
answers
2k
views
delete or rollback an insert if a trigger on insert fails
is it possible to delete or roll back insert statement if a trigger on that insert statement fails due to some reason.
for eg i have a trigger on insert into employe(id,name) values(1,'ali')
trigger
...
- 11
1
vote
0
answers
135
views
Difference between setting of a shared lock when using INSERT or UPDATE/DELETE statements
I would like to know why a shared lock is set when I use a foreign key on an INSERT statement, but not when I use a foreign key on an UPDATE or DELETE statement.
Specifications:
version: 10.1.47-...
- 123
1
vote
0
answers
85
views
The overhead of OCC validation phase
The validation phase of optimistic concurrency control has two derctions: one is backward validation, checking for conflicts with any previously validated transaction, the other is forward validation, ...
- 41
1
vote
0
answers
134
views
Efficient locking for a conditional insert inside a larger transaction
I have a script I'm using to merge incoming data into a table that includes a conditional insert to capture some related data.
For example, where [#Records] contains the incoming data:
BEGIN ...
- 183
1
vote
0
answers
28
views
Time-Order preserving concurrent reservation of non distinguishable items
Problem statement:
Its very similar to a traditional booking system, however it is a bit different.
There are non-distinguishable items (e.g. like money on bank account) of different types that can be ...
- 11
1
vote
1
answer
355
views
Reusing the same connection to read uncommited transaction
My question is similar to: how to spy on a transaction that is not yet committed in postgres for debugging
What I tried:
I start a pgbouncer instance, and set the max-db-connection to 1
From my node ...
- 11
1
vote
2
answers
178
views
Postgres Foreign Data Wrappers for Data Migration. Do transactions hold across databases?
I want to migrate some data from Postgres database A to database B. I found Foreign Data Wrappers that allow you to connect to a table in B while in database A. I have a table in A that I want to move ...
- 307
1
vote
1
answer
162
views
Do I always need boilerplate code to prevent nested transactions?
Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one?
It seems like a ...
- 3,931
1
vote
1
answer
103
views
Can I rely on the MSDTC to guarantee a distributed transaction was completed?
I've come across a bug using the TransactionScope from the .NET library, where we're getting a TransactionInDoubt exception. After doing a lot of research into something I don't know much about, I've ...
- 111
1
vote
1
answer
664
views
MySQL Partial Transactions
I am experiencing some strange behavior in MySQL using transactions. We have a PHP site that runs ~100 workstations inserting thousands of items (table 1) daily, with 4 to 7 logged events (table 2) ...
- 111
1
vote
0
answers
219
views
Sequence number for COMMITTED concurrent inserts
For example, I have a table
create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type
and many concurrent inserts into this table. Is there is a way to fill the OrderNum ...
- 119
1
vote
0
answers
191
views
Preserving some data on rollback in InnoDB?
I want to keep log of attempted, sucessful, and failed operations (both for troubleshooting, and also for detecting malicious users trying things). I want to keep that log in InnoDB MySQL table.
Now, ...
- 275
1
vote
1
answer
59
views
Wraparound Transaction ID
Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum ...
- 37
1
vote
0
answers
522
views
MySQL: why undo log is required in the processes of database recovery?
Let's imagine that an incident happens when you updates.
start transaction;
update users set gender = 1 where id = 3;
Undo log space is allocated.
Previous values are copied to undo log
Record of ...
- 493
1
vote
0
answers
151
views
Does two phase locking apply to individual transactions, or the schedule as a whole?
Problem
I have conflicting perceptions of when a schedule violates 2PL or not.
My first perception is that 2PL only applies to the level of a transaction at a time, since this is what I learned in a ...
- 217
1
vote
0
answers
373
views
Insert statement on one table blocking delete on another unrelated table on sql server
I performed a load testing on our environment,where for eg. stored procedure say "sp" was called multiple times to process on different set of ids.
During load testing this store procedure ...
- 11
1
vote
0
answers
367
views
Can A Database Transaction Have Different Session IDs?
I am just trying to troubleshoot an issue and was it seems that one of my spids is associated with an open transaction. Just wondering if that spid would be used for other queries in the transactions ...
1
vote
0
answers
304
views
Does PostgreSQL support subtransactions?
Does PostgreSQL support subtransactions?
From DOC
"Note that a transaction can hold conflicting locks on the same row,
even in different subtransactions;" (https://www.postgresql.org/docs/9.6/...
- 161
1
vote
0
answers
134
views
Question about 2PL locking
New to this stack exchange, I hope this is the correct one. So I am preparing for a final and one of our practice tests had us put locking on two transactions:
T1: R1(A), R1(B), W1(B)
T2: R2(B), R2(...
- 111
1
vote
0
answers
24
views
Do start and end points change conflict serialisability?
If we have two transactions, and the following schedule:
S1 R1(X) S2 R2(X) R2(Y) E1 W2(X) W2(Y) E2
where Si and Ei indicate the start and end of the ith transaction respectively. Does this change ...
- 177
1
vote
0
answers
102
views
Attach a psql session to a running transaction
Is it possible to open a psql session inside a currently existing transaction?
I'd like to be able to do this for debugging purposes. The idea would be to break into a debugger in my program, attach ...
- 111
1
vote
0
answers
47
views
MySQL/JDBC: Can a commit on server get missed on client?
I am wondering about when conducting a transaction of one or two updates/inserts from a JDBC client to MySQL Server over the network...
In the event of losing the network during the commit, can I get ...
- 111