Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
738
questions
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
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
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
1
vote
1
answer
132
views
What's the correct database behavior when two transactions change the same row/value?
I'm writing a database LRU cache in C++ for an embedded NoSQL database to solve a performance problem, and I'm trying to understand the correct assumed behavior and philosophy behind it.
Say there's a ...
1
vote
1
answer
149
views
Is COW (copy-on-write) based atomic commit widely used in mainstream databases?
The database is not a flat structure like a file system, and generally has three to four levels of index levels. If you simply use COW, you will have obvious write amplification every time you modify ...
- 111
2
votes
2
answers
4k
views
How to lock table in SQL Server to prevent other transactions from INSERT and avoid deadlocking?
I'm writing a database trigger on Table A. In said trigger, I insert a new record into Table B by doing:
Look up the PK ID of Table B
Increment the PK ID and insert new row into Table B
(Note: Table ...
- 275
0
votes
1
answer
877
views
Does Oracle Database support nested transactions?
Does current version of Oracle Database (21c) support nested transactions?
I'm aware of Oracle DB supporting savepoints. However I'm looking for a way to make COMMINT not to write to disk for purpose ...
- 523
1
vote
1
answer
580
views
MySQL temporary can't see some newly inserted rows, but can see others inserted afterwards
I have a MySQL(version 5.7.14-8) table that essentially acts like a queue(it's a legacy system) that has the following schema:
CREATE TABLE `myqueue` (
`queue_id` bigint(20) unsigned NOT NULL ...
- 113
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
0
votes
1
answer
424
views
Recovery of uncommitted transactions after checkpoint
After reading from several resources including:
COMMIT (Oracle Database SQL Reference)
Checkpoints (Oracle Programmer Reference)
Difference between commit and checkpoint (on this site)
ORACLE ...
2
votes
1
answer
2k
views
When to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION in SQL Server?
For this question lets assume there are two servers, ServerA and ServerB, and there is a linked server setup on ServerA to ServerB. Let's also assume I have REMOTE_PROC_TRANSACTIONS set to ON for both ...
- 35.1k
0
votes
2
answers
948
views
How can I trigger an error on transaction commit?
I develop a library that implements transactions in Postgresql, and at my job we had a weird bug in production with it, when transaction failed not on one of the queries itself, but on COMMIT, because ...
- 157
1
vote
2
answers
888
views
Missing an inserted row
I am having a strange heisenbug when a set of queries in a transaction (in MariaDB 10.2.38) somehow results in missing rows afterward. I mean that there are rows inserted within the transaction, but ...
- 131
4
votes
2
answers
2k
views
Transaction strategy for UNIQUE constraint?
How to use a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day?
It was suggested to me that it will different for every isolation level. Could ...
- 77
2
votes
2
answers
861
views
MariaDB event to automatically move data older than a specific time to another table without temporary table?
I am trying to setup an event, that automatically moves data older than a specified amount of time from a heavily used table to some "archive" table to improve performance.
So far I've only ...
- 135
6
votes
3
answers
2k
views
Commit or rollback SQLServer-SQLTransaction when no data changed
we use the SqlTransaction-class in the backend to access data stored in the MS-SQLServer.
In some cases we definitely know that no data were changed by this transaction (and no errors occurred).
So ...
- 165
0
votes
1
answer
309
views
Unexpected behaviour with InnoDB Transaction retry
This is going to be difficult to reproduce but hopefully someone can shed some light on my problem based on the logic involved. After having some intermittent issues with deadlocks during a ...
1
vote
1
answer
3k
views
COMMIT works in one plpgsql code block, but not in another?
Every once in a while we need to backfill a bunch of data based on some complex calculations. Because we have a lot of data, we need to make sure that we are not locking a table for a long time and do ...
1
vote
1
answer
165
views
What could be the reason for running rollback after a commit?
I am trying to understand why there are so many rollback queries being issued by Jira and is equal to the number of commits. MySQL log reveals the following:
2021-04-29T14:55:58.364468Z 42059 ...
- 145
8
votes
2
answers
4k
views
Why is part of transaction committed when stored procedure faults?
I have a stored procedure that begins by declaring a few variables then contains begin tran; After this it performs some validations on the supplied arguments (and increments an error count each time ...
- 1,595
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
votes
1
answer
78
views
Explanation for "Heisenberg" effect on SELECT before INSERT inside transaction? (MariaDB)
I have a live database application of medium size (biggest table 40,000 rows) which has had a problem with transactions failing on a race condition.
There is a "Heisenberg" effect where if ...
- 99
0
votes
1
answer
475
views
Consistency of data between two databases on different server
I have to guarantee consistency of data stored in two databases installed on different servers.
The first one is a MySQL database and the second is a SQL Server database.
Data properties are not the ...
- 109
14
votes
1
answer
2k
views
Is this an error in SQL Server's documentation for ROLLBACK?
This is the SQL Server's document talking about the ROLLBACK statement. On that page it states that the syntax of it is as follows:
ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @...
- 2,602
2
votes
1
answer
2k
views
What is isolation level of autocommit?
I'm trying to make sense from "autocommit" concept.
Originally I've heart about it in the context of Java JDBC API Connection.setAutoCommit(boolean autoCommit)
Quick search revealed that the ...
- 499
1
vote
1
answer
1k
views
How to change the default isolation level of a transaction in a stored procedure in PostgreSQL PL/pgSQL?
I am interested in controlling the isolation level of a transaction inside a PostgreSQL procedure but haven't found any information on how to do it. The documentation on Transaction Management within ...
- 125
0
votes
1
answer
128
views
does SELECT with implicit transaction prevent VersionStore cleanup?
The JDBC driver usually sets the implicit transaction on in every query executed by the client.
I found many SELECT statements in suspended state with implicit transaction set to ON using Version ...
- 107
0
votes
0
answers
216
views
How to avoid duplicate in mysql INNODB transaction for non index key?
I have a INNODB table with txnno column without unique index but I have primary key on another column. Now I am inserting transaction from multiple places. And there are some concurrent insertion ...
- 41
0
votes
1
answer
59
views
Queueing MySQL record inserts to avoid over-subscription of a related resource ... table locking?
Given a simplified hypothetical of seats in a lifeboat, if I have the following setup with a lifeboats table and a seats table where each record is one occupied seat in the given lifeboat:
CREATE ...
- 3
2
votes
2
answers
3k
views
Transaction log: small transactions vs large transactions for speed [closed]
I am trying to learn how the transaction log works and have been studying it for a couple of day.
Some operations become faster when done as many small transactions, e.g. deleting many rows in small ...
- 785
6
votes
3
answers
3k
views
Postgres Repeatable Read vs Serializable
I'm trying to understand whether the repeatable read isolation level is good enough for my scenario in an application that uses Postgres, but the docs are making it difficult to understand which is ...
- 161
2
votes
2
answers
2k
views
Conditions that can make a MySQL COMMIT query fail?
I have an application where I run an explicit COMMIT query on a MySQL standalone server
COMMIT;
The query works as expected in the vast majority of cases, but sometimes the query seems successful but ...
- 151
0
votes
2
answers
168
views
Nested Stored Procedure Connections
I have a Stored procedure that simply looks for Imported files through a set of other processes. The main SP runs every 5 mins and looks for new files in a directory and imports them into a staging ...
- 53
2
votes
1
answer
103
views
When autovaccum will analyze?
Let's suppose that a table foo became eligible for autovacuum analyze, e.g. I have inserted a number of rows into foo that exceeds autovacuum_analyze_scale_factor * number of rows + ...
- 397
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
3
votes
2
answers
96
views
Isolation levels and rows not yet read
Under RR in transaction T1 I do a
SELECT * FROM mytable WHERE city='London'
In another T2 I do
UPDATE mytable SET price=100 WHERE city='London' and id=5000
Is it possible that T2 reads,updates and ...
- 163
0
votes
1
answer
52
views
the actual writes only after the transaction enters the committed state
The text below is from Database System Concepts by Silberschatz. It says that the actual writes take place only after the transaction enters the committed state.
My question: When the transaction has ...
- 101
2
votes
4
answers
582
views
Why can't a committed transaction be undone
Once a transaction has been committed, we cannot undo its effect by aborting it.
We have the log file that has all information to undo a committed transaction, so why is this not possible? We should ...
- 101
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
1
answer
622
views
MariaDB InnoDB does not rollback on timeout
I have the ff. sample queries in this sequence:
START TRANSACTION
UPDATE
INSERT
COMMIT
Query #2 is timing out but I noticed that query #3 is not rolled back.
I am also trying to manually rollback by ...
- 169
2
votes
2
answers
452
views
Trying to understand an RCSI example-The Potential Dangers of the Read Committed Snapshot Isolation Level
There's this article The Potential Dangers of the Read Committed Snapshot Isolation Level which demonstrates RC vs RCSI isolation levels.
I get the RC example but not the RCSI one.In particular how ...
- 163
2
votes
1
answer
470
views
How crash recovery process works in SQL Server?
I would like to ask a question regarding recovery process while restoring database operation. Let's say I restored a full backup with NORECOVERY followed by restoring two t-log backups with NORECOVERY....
- 1,253
1
vote
3
answers
120
views
How to improve the liveness of a system when having a long-running transaction blocking multiple reading transactions
Is there any recommended approach for improving the application liveness when having, over the same database object, multiple reading transactions, and one long-running transaction which is updating ...
- 13
1
vote
3
answers
632
views
What is the difference between concurrency control in operating systems and in trasactional databases?
Recently I'm learning concurrency control techniques in transactional databases. However, I am so confused about the differences between concurrency control in operating systems and in transactional ...
- 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
2
votes
1
answer
763
views
Transaction not rolling back in MySQL
Consider the following setup, which you can tinker with at dbfiddle.uk:
In the first batch we setup three simple tables, and add a row to each table:
CREATE TABLE t1 (x int NOT NULL);
CREATE TABLE t2 (...
- 69.7k
1
vote
2
answers
1k
views
Trap exception in PostgreSQL script
I want to run a script inside a transaction in PostgreSQL. So I surround the SQL code with begin and commit statements. But I want to rollback on error. I don't see how to do that.
BEGIN;
UPDATE ...
- 121
0
votes
1
answer
157
views
CREATE TABLE and transactions
Is there a way to use CREATE TABLE inside of a transaction in Sybase?
- 217
0
votes
1
answer
189
views
Enforcing maximum number of relations for a record
I am trying to figure out the way to enforce a maximum number of relations in a highly concurrent system. The following is an example use case.
// Job table
job_id | ... | num_positions
xx-xx-xx | ....
- 3
1
vote
1
answer
139
views
Is it safe to rely on transactions in Firebird 2.5 Embedded DB in case of power outage?
How it's safe to say that the following list of action will never be reflected in DB in case of power outage somewhere in the middle of #2 line, before transaction is committed?
#1 begin transaction
#...
- 477