Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
668
questions
1
vote
1
answer
30
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
0
votes
0
answers
25
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 ...
- 137
0
votes
0
answers
15
views
when should we use the read uncommited
I am reading the database isolation level, and I found the read uncommited isolation level. my question is: what the purpose of the read uncommited isolation level? when should we use the read ...
- 477
1
vote
0
answers
18
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
3
votes
1
answer
91
views
Does the SQL Transaction Log clear at the end of a loop?
I'm running SQL Server 2019 Enterprise Edition. Every month, we run a stored procedure that loads millions of records with service dates going back five years. I have 400GB of space allocated across 4 ...
- 157
0
votes
2
answers
51
views
Is there any way to get current transaction id in SQL SERVER 2008?
I want to get current transaction id to log something in another process. The code is actually run in .net, but in SQL Server it will be similar to the following pseudo code:
BEGIN TRAN
<GET ...
- 1
1
vote
0
answers
25
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
...
1
vote
3
answers
75
views
SQL or/with Nosql
I am designing a mobile app with basic functionality such as sign-up, Auth, posts, real time messages etc. I think for this kind of work Nosql should work better. But over a long period of time I want ...
- 23
9
votes
3
answers
738
views
What is the point of TRY CATCH block when XACT_ABORT is turned ON?
Code sample with XACT_ABORT_ON:
SET XACT_ABORT_ON;
BEGIN TRY
BEGIN TRANSACTION
//do multiple lines of sql here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0) ROLLBACK;
//...
- 2,223
3
votes
1
answer
158
views
Making an index a unique index in very large MySQL table within one transaction - is the following approach safe?
For the purpose of optimizing SELECT statements I am trying to make an index UNIQUE with the following SQL statement in MySQL:
ALTER TABLE credentials DROP INDEX special_credential_id, ADD UNIQUE KEY ...
- 133
0
votes
0
answers
48
views
After a restore my transaction is slow, so I stop it, do a roll back, then re-run and it's fast...why?
On my development server I delete all data from two tables then pull fresh data from the production server via Delete From then Select Into. It's only about 100,000 rows total. On dev I run a routine ...
- 109
0
votes
0
answers
28
views
Transactional Inserts and updates in NoSQL
Let's say I'm using Database X which is NoSQL and doesn't support transactions.
1. Update A
[error] server goes down
2. Insert B
In a scenario, A is updated and suddenly our server goes down or the ...
- 33
0
votes
0
answers
21
views
Result Of A Schedule
I recently attempted a test on Transaction Management and one of the questions went like this:
Consider the three transactions T1, T2 and T3 with timestamp in order T1<T2<T3.
There are 2 ...
- 1
1
vote
2
answers
134
views
InnoDB's undo log start growing after simple SELECT with READ-UNCOMMITTED level
I have a percona mysql (5.7) server with 20K QPS (lots of inserts/updates/deletes).
My question is: why issuing a simple, but long, select query (to any table) with trx isolation=READ-UNCOMMITTED ...
- 23
0
votes
1
answer
27
views
LOCK TABLES is locking current transaction
While I do some changes to a table in innodb I want to lock the table. But I can't make it work. After locking the table I can't alter the table in the same transaction
SQLSTATE[HY000]: General error:...
- 123
0
votes
1
answer
5k
views
ERROR: current transaction is aborted, commands ignored until end of transaction block SQL state: 25P02
I am trying to create a deadlock in my postgreSQL-database but the error I'm getting is not really the one I would expect:
ERROR: current transaction is aborted, commands ignored until end of ...
- 113
0
votes
0
answers
43
views
If a schedule is allowed by 2 phase locking protocol, does that mean all possible interleaved schedules (same transactions) are serializable?
I came across following results on web:
1)2PL ensures conflict serializability(cs) and hence avoid non serializable schedules.
2) But not every cs schedule allowed by 2pl.
3)this is a theorem i found: ...
0
votes
2
answers
159
views
Do select statements start a transaction in auto commit mode?
I knew that update, insert, delete start a transaction in auto commit mode, I would like to ask if select statements make their own transaction also in auto commit mode or there is no need for that ?
...
0
votes
1
answer
37
views
Taking many row level locks on a rows with little contention - how does that affect PostgreSQL scalability (and RDMS in general)
I use transactions to lock on rows in my database. Generally, the number of 'things' accessing the same rows at a single time is small, however, for correctness I would still like to use transactions ...
- 101
1
vote
1
answer
72
views
How to return values from psql to bash with custom session settings
I need to set the datestyle to "dd/mm/yyyy" before calling a procedure and getting it's return value back to bash.
I tried the following:
read return <<< $(psql \
-...
- 11
0
votes
1
answer
39
views
Insufficient privilege for read only transaction
I am using Oracle and SQL Developer and here is a problem I ran into. I have created a table called T_TEST and I am trying to create a read only transaction to it from another user. This is my ...
-1
votes
1
answer
226
views
Perform a transaction across multiple databases at the same time
I have a system which performs many actions frequently and am looking at splitting databases up a bit to spread out the load and speed up the system hopefully.
The fear is that by splitting up these ...
- 127
1
vote
0
answers
231
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 ...
- 27
0
votes
0
answers
208
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 ...
- 111
1
vote
0
answers
169
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 ...
- 111
1
vote
1
answer
54
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 ...
0
votes
1
answer
56
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 ...
- 101
1
vote
2
answers
445
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 ...
- 243
0
votes
1
answer
309
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 ...
- 443
1
vote
1
answer
87
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
48
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) ...
- 123
0
votes
1
answer
115
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
470
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 ...
- 24.2k
0
votes
1
answer
68
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 ...
- 127
1
vote
0
answers
94
views
SQL statements to ensure that the same location cannot be reserved more than once on the same day?
I have two tables one for venues (3 locations hall1, hall2 and hall3) and one for orders (a row could be 441,2004,50895,'Requested','2021-01-02 10:53','2021-03-06 11:46',7,3) as follow:
CREATE TABLE ...
- 47
1
vote
2
answers
141
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 ...
- 31
4
votes
2
answers
407
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
1
vote
2
answers
202
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 ...
- 125
6
votes
3
answers
914
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
57
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 ...
- 525
1
vote
1
answer
422
views
COMMIT works in one plpgsql code block, but 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
64
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 ...
- 135
8
votes
2
answers
2k
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,425
1
vote
0
answers
342
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
46
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
125
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's properties are not the ...
- 101
13
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,492
1
vote
1
answer
509
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 ...
- 429
1
vote
1
answer
271
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 ...
- 115
0
votes
1
answer
49
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 ...
- 97