Questions tagged [transaction]

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

Filter by
Sorted by
Tagged with
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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; //...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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:...
user avatar
  • 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 ...
user avatar
  • 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: ...
user avatar
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 ? ...
user avatar
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 ...
user avatar
  • 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 \ -...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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) ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 | @...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 97

1
2 3 4 5
14