Skip to main content
The 2024 Developer Survey results are live! See the results

Questions tagged [transaction]

A mechanism for committing a consistent set of changes into a database atomically.

128 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6 votes
0 answers
915 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 ...
Jeff Widman's user avatar
5 votes
3 answers
1k 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-...
Stephen's user avatar
  • 213
4 votes
1 answer
565 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 ...
davide's user avatar
  • 141
3 votes
0 answers
340 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 ...
Jordan's user avatar
  • 141
3 votes
0 answers
356 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 = ...
user avatar
3 votes
0 answers
182 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 ...
Déjà vu's user avatar
  • 483
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 ...
MagePsycho's user avatar
3 votes
0 answers
490 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 ...
Marcus Junius Brutus's user avatar
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 ...
Eric Jensen's user avatar
3 votes
1 answer
938 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 ...
James's user avatar
  • 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. ...
mltsy's user avatar
  • 159
2 votes
0 answers
93 views

'Deadlock found when trying to get lock; try restarting transaction' in AWS RDS MySQL 8.0.36 after upgrading from MySQL 5.7.42. Django v4.1.6

Following an upgrade of our AWS RDS instance from MySQL version 5.7.42 to version 8.0.36, we are encountering persistent deadlock issues across concurrent transactions, affecting even basic read ...
Karan Raj's user avatar
2 votes
0 answers
1k 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 ...
user973347's user avatar
2 votes
0 answers
1k 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 ...
Josh Coady's user avatar
2 votes
0 answers
242 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 ...
Brian T.'s user avatar
  • 121
2 votes
0 answers
26 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 ...
Chad's user avatar
  • 21
2 votes
0 answers
103 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 ...
mvorisek's user avatar
  • 418
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? ...
thomjah's user avatar
  • 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 ...
Lamp town guy's user avatar
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 ...
Bri's user avatar
  • 195
2 votes
0 answers
139 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, ...
djechlin's user avatar
  • 223
1 vote
1 answer
434 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). ...
bruno-kakele's user avatar
1 vote
1 answer
330 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 ...
Le Hoang Long's user avatar
1 vote
0 answers
371 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 ...
DevelJoe's user avatar
  • 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 ...
Kashish's user avatar
  • 11
1 vote
0 answers
90 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 ...
Michal Charemza's user avatar
1 vote
0 answers
796 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 ...
andykais's user avatar
  • 121
1 vote
0 answers
144 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) ...
orange's user avatar
  • 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 ...
Jazz's user avatar
  • 11
1 vote
0 answers
142 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-...
Daan's user avatar
  • 123
1 vote
0 answers
93 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, ...
Hovin's user avatar
  • 41
1 vote
0 answers
148 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 ...
Aleks's user avatar
  • 183
1 vote
0 answers
29 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 ...
Thomas's user avatar
  • 11
1 vote
1 answer
445 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 ...
kmlkz's user avatar
  • 11
1 vote
2 answers
220 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 ...
mj_'s user avatar
  • 307
1 vote
1 answer
175 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 ...
Zikato's user avatar
  • 5,102
1 vote
1 answer
120 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 ...
DubDub's user avatar
  • 111
1 vote
1 answer
736 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) ...
MaKR's user avatar
  • 111
1 vote
0 answers
242 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 ...
Ruslan K.'s user avatar
  • 119
1 vote
0 answers
205 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, ...
Matija Nalis's user avatar
1 vote
1 answer
63 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 ...
user172940's user avatar
1 vote
0 answers
588 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 ...
Ryan Lyu's user avatar
  • 523
1 vote
0 answers
167 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 ...
isakbob's user avatar
  • 217
1 vote
0 answers
410 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 ...
ROHIT IHARE's user avatar
1 vote
0 answers
416 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 ...
Daniel Paduck's user avatar
1 vote
0 answers
309 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/...
ALZ's user avatar
  • 161
1 vote
0 answers
141 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(...
SomeStudent's user avatar
1 vote
0 answers
25 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 ...
WeakLearner's user avatar
1 vote
0 answers
110 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 ...
dshepherd's user avatar
  • 111
1 vote
0 answers
48 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 ...
Mark Meyers's user avatar