Questions tagged [transaction]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
6 views

Handling data inconsistency of high load time data

I have a highly normalized database PG14 with many foreign keys, and my application performs calculations on this data. First my application loads numerous entities of respective customer (let’s say ...
Cowabunga's user avatar
  • 135
0 votes
1 answer
25 views

Create named PK when create table

I'm creating a table in Postgresql-11, and I need create a primary key for it at same time, to prevent from duplicated data being inserted before the PK created, so I want to these two operations be ...
Leon's user avatar
  • 285
1 vote
1 answer
44 views

How to handle deadlocks while multiple batch inserts?

I need suggestions/ideas of handling a issue with deadlocks. I'm having multiple batch inserts commands to same table: insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, ...
KaliTheGreat's user avatar
0 votes
1 answer
28 views

Can I do transaction for dynamic sql which contains DISABLE change tracking / TRUNCATE / PARTITION SWITCH

I would like to create transaction which will : DISABLE change tracking IF exists. TRUNCATE partition for table. SWITCH PARTITION from different table to main table. ENABLE change tracking IF was ...
adam.g's user avatar
  • 283
0 votes
0 answers
17 views

UPDATE AND SELECT INSIDE THE SAME TRANSACTION

I have a transaction that updates a table and then does a SELECT check on that table that was changed, but the data does not follow the update made by the previous UPDATE, there is something I should ...
Kaio Marx's user avatar
0 votes
1 answer
28 views

Locking order in a nested query

I'm trying to prevent deadlocks in my code because i may use both in different parts of my project. Do they lock rows in the same order? Nested select * from `carts` where `carts`.`user_id` = ? and ...
Jacqueline's user avatar
0 votes
1 answer
58 views

How do two transactions with different levels of isolation interact with each other?

this is specifically for SQLserver: I want to know if I have a transaction with isolation level SERIALIZABLE and another one with READ UNCOMMITTED how will they interact concurrently? will the second ...
Achraf Cheikh Mohamed's user avatar
0 votes
0 answers
53 views

Is it possible deleted partially?

I executed delete query delete from [table_name] After execution, I found that 'where clause' is wrong so I cancelled the query using cancel buttion in SSMS. and then I counted the table record. I ...
gnc's user avatar
  • 1
0 votes
2 answers
63 views

Variable Value Set in TRY CATCH is Discarded When Trigger is Coded a Certain Way

I know this goes against all things logical, however, I am attempting to push this through anyway :( There is a scenario in which records are written to a table with the trigger below attached. ...
Ross Bush's user avatar
  • 463
0 votes
0 answers
43 views

Trigger with EXEC and Only Debug Statements After First Exception Saved [duplicate]

I apologize for the verbosity of this post. To catch DDL changes after a snapshot or full load (using aws/dms here) has been performed, there is sourceDB.tblAWSObjects which replicates to targetDB....
Ross Bush's user avatar
  • 463
0 votes
1 answer
79 views

Transaction/For update lock for an eCommerce project

When users want to purchase their orders, The quantity of the products should be decreased. Now I'm planning to use a Transaction and a FOR UPDATE lock on the products and decrease the quantity of ...
Jacqueline's user avatar
0 votes
1 answer
64 views

Running 2 inserts in one transaction is executing unacceptably long time. Running same DML Separately works fine

Decided to see what DBAs have to say about this because developers have no idea. I need to execute 2 DMLs in one transaction. First DML takes records from Table1, changes some values and inserts back ...
T.S.'s user avatar
  • 196
0 votes
0 answers
25 views

InnoDB deadlocks with IX locks

Table schema `id` varchar(255) NOT NULL, `created_time` bigint DEFAULT NULL, `updated_time` bigint DEFAULT NULL, `tdi` varchar(255) NOT NULL, `gid` varchar(255) NOT NULL, `auth_id` varchar(...
uzumas's user avatar
  • 101
0 votes
0 answers
69 views

How are blind writes recoverable in a transaction schedule?

Consider the following schedule - T1 T2 R(A) W(A) R(A) W(A) Commit Commit I understand that this schedule is non-recoverable, because if a failure occurs between the two ...
Kushagr Jaiswal's user avatar
1 vote
1 answer
127 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
0 votes
0 answers
132 views

multiple mongodb transactions over a single session

I'm using mongodb in an enterprise application and for some use cases, I have to use transactions. It seems like at first I wasn't using mongodb's ClientSessions correctly. Each time I was trying to ...
massivefermion's user avatar
0 votes
0 answers
92 views

How can I simulate transaction ID wraparound in Postgres?

I'm trying to get a better understanding of how XID wraparound affects the results of the query to find the snapshot xmin (that is txid_snapshot_xmin(txid_current_snapshot()) or pg_snapshot_xmin(...
Dev K's user avatar
  • 1
0 votes
1 answer
142 views

Visibility into progress of Postgres transaction with multiple queries

I have a regular case where I run many Postgres queries within a do/begin/end block DO $$ BEGIN <queries> END $$; In some cases, the databases and queries run a little bit slow and we need ...
Hendy's user avatar
  • 3
0 votes
1 answer
2k views

Getting ERROR: invalid transaction termination CONTEXT: PL/pgSQL function run(text,text,text,double precision) line 147 at COMMIT

I am trying to execute an INSERT statement and, if there's an error then I rollback and retry the insert; otherwise I commit it to close the transaction. However, I am getting the error: "...
RAJIV's user avatar
  • 1
3 votes
2 answers
406 views

In SQL Server Temporal Tables start/end date timestamps are identical for rows that are created inside a EF Core transaction

I'm using a EF Core DbContext transaction to create/update entities on a SQL Server temporal table. The data I'm processing (array of items) has a key (Year-Sequential, like: 2023-001, 2023-002, etc.) ...
Cheshire Cat's user avatar
-1 votes
1 answer
36 views

How to tell Postgresql what tables will be in a transaction?

RDS Postgresql 12.12 CREATE INDEX CONCURRENTLY idx_foo ON foo (f1, f2, f3); is blocked by a stored procedure which only touches tables bar and baz. BEGIN and START TRANSACTION don't seem to have a ...
RonJohn's user avatar
  • 606
-1 votes
1 answer
257 views

How to avoid deadlock inside transaction? [closed]

I have a transaction that wraps two code paths that update/insert related tables in the opposite order. Outside of a transaction I understand why that would cause a deadlock, but why does it happen ...
chell's user avatar
  • 175
0 votes
0 answers
129 views

Are autonomous transactions implemented in PostgreSQL v15?

I searched the official docs with no luck, but I found a few answers here on DBA referring to them in PG after introducing procedures. Are autonomous transactions implemented in PostgreSQL v15? Thank ...
geeko's user avatar
  • 21
3 votes
1 answer
547 views

Will INSERT ON CONFLICT DO NOTHING lock the row in case of conflict?

In read-commited isolation level: If I understood correctly, in case of no pre-existing rows that would result in conflict, two concurrent transactions with INSERT ... ON CONFLICT DO NOTHING - which ...
Adrian B.'s user avatar
  • 135
0 votes
2 answers
648 views

Try Catch w/Transaction and executing stored procedures

I need to create an error-handling method for a couple of Stored Procedures. Proc1 CREATE PROCEDURE dbo.Proc1 AS BEGIN TRY BEGIN TRANSACTION EXEC Proc2 INSERT INTO tableA (col1, col2) ...
Semperfi89's user avatar
0 votes
1 answer
656 views

MariaDB writing transaction and rollback - novice

Currently I am learning how to write transactions for MariaDB (I got a version 10.6 running). I attempt to execute several commands and based on their success to commit or rollback. So far I have ...
JRA's user avatar
  • 3
0 votes
0 answers
48 views

Is there a way of identifying transaction boundaries from MongoDB ChangeStream?

The MongoDB documentation (https://www.mongodb.com/docs/manual/reference/change-events/) specifies that for a change event, the transaction associated with that event can be uniquely identified by the ...
Kiruphasankaran Nataraj's user avatar
0 votes
1 answer
375 views

Logging in error states in Postgres transactions

I'm currently working on rebuilding some SQL Server stored procedures in Postgres, but I have the following hangup CREATE OR REPLACE PROCEDURE public.math_proc () LANGUAGE plpgsql AS $$ BEGIN ...
Rushabh Mehta's user avatar
1 vote
1 answer
40 views

MySQL does not open a transaction, and initiates a request containing multiple SQLs.This time, is it a transaction, or is each SQL a transaction?

MySQL does not open a transaction, and initiates a request containing multiple SQLs. This time, is it a transaction, or is each SQL a transaction? Does not open a transaction means that a transaction ...
feilong gao's user avatar
2 votes
2 answers
1k views

How to interpret "transactions per second"?

Short version: When I execute one transaction in IntelliJ, the "transactions per second" graph increases by 10 tx/sec - I only expect it to increase by 1. So, it seems that my understanding ...
TmTron's user avatar
  • 233
1 vote
1 answer
109 views

MySQL does not open a transaction, is there a deadlock between different SQLs?

2 concurrent do not start transactions. Concurrent A locks 1-3-5 sequentially, of course, the actual locking process must be one by one.Concurrent B locks 5-8-1 sequentially, of course, the actual ...
feilong gao's user avatar
0 votes
0 answers
30 views

Postgres, how to select and update in transaction via jdbc?

I'm trying to implement simple logic, there is a table with jobs my_jobs jobId | jobStatus | jobMeta | executorId 1 ACCEPTED "a stuff to do" | null 2 ACCEPTED &...
Capacytron's user avatar
0 votes
3 answers
591 views

How do I handle an INSERT to 2 tables within a transaction?

I have 3 tables in a Postgres DB: UserType -> ID, creation, delete, update, name User -> ID, creation, deleted, update, user_type_id Employee -> ID, creation, deleted, update, user_id (fk) ...
Thomas's user avatar
  • 1
0 votes
1 answer
37 views

Problem associated with transaction schedule

Here, Read_item(B); and B;=B+temp; have written parallelly. what happen here? What is the problem associated with the above transaction schedule?
Malinsha Vithanage's user avatar
0 votes
1 answer
315 views

Phantom read during single statement execution in PostgreSQL

I wonder could there ever be a concurrent situation when phantom read occurs during single SQL statement execution? Given: tx_test is a table with 3 rows and id column is primary key, value is some ...
peremeykin's user avatar
0 votes
1 answer
508 views

Using two transactions at the same time without getting Lock wait timeout exceeded

I use MySQL 5.6 I'm trying to make a test framework on PHPunit, I use two different db connections (from two different systems it has to be 2 different db connections not a choice). By default many ...
Steve Moretz's user avatar
1 vote
1 answer
588 views

Does MS SQL Server hold transactions in queue?

Is it possible for SQL Server to 'hold' transactions and then execute them in one go? Just some background: I have a client with a decently large SQL Server with their ERP databases and our scanning ...
Abinash's user avatar
  • 11
0 votes
2 answers
945 views

Guaranteed rollback in SSIS

I'm trying to create a job in SSIS to truncate a table, read from a data source and rewrite the table, so only 2 steps (1 Truncate and 2 Select + Insert). I need to guarantee a Truncate's Rollback in ...
Giorgia Grossi's user avatar
1 vote
2 answers
2k views

What happens to an open transaction when the session window is closed?

I executed the following query in SQL Server 2012: create table testTransaction ( id int name varchar(100) ) insert into testTransaction values (1,'ABC'),(2,'XYZ') /* Query1 */ begin try begin ...
lifeisajourney's user avatar
0 votes
1 answer
403 views

Aggregate each row and create new monthly column for the weeks to a month

I appreciate all your help. Below is my query and request. Here is my staging table: CREATE TABLE MyTable ( [WC] nvarchar(255), [Requestor] nvarchar(255), [MTR#] nvarchar (255), [Date Added] ...
Kiran's user avatar
  • 3
0 votes
2 answers
594 views

how long should an sql-server transaction run at most [closed]

our Teamcenter (Siemens' software) oftens runs transactions for hours, even days. As if they were forgotten. There easily are even 20+ transactions running longer than an hour, even 20 hours. They ...
myro's user avatar
  • 127
0 votes
2 answers
98 views

Holding transactions as long as a user edits

As an application developer I'm used to using databases transactions only as a way to play in modifications after a user has clicked "save". That's the way most database servers I'm familiar ...
John's user avatar
  • 755
3 votes
1 answer
279 views

The finer points of writers locking each other out under the snapshot isolation levels

The read committed snapshot and snapshot isolation levels in SQL Server do away with most locking except one: A writer still locks out other writers. The documentation tiptoes around saying as much ...
John's user avatar
  • 755
0 votes
1 answer
2k views

Run both of "DROP DATABASE" and "CREAT DATABASE" in CLI with "-c" option of psql of Postgresql

When debugging programs, I need to frequently drop a database of Postgresql, and then re-create it again. To convenient, I want to execute this in a single command line like following: psql -c "...
Leon's user avatar
  • 285
0 votes
1 answer
650 views

pg_restore error when using the --single-transaction: options c/--clean and -a/--data-only cannot be used together (even so the -a flag was not used)

I met a strange PostgreSQL (v.13.8) on a Windows server 2019 machine today when trying to restore a custom dump file. The following command worked: pg_restore -d postgres://postgres@localhost:5432/...
s.k's user avatar
  • 342
0 votes
1 answer
202 views

PostgreSQL: does the WAL group commit affect synchronous replication performance?

I'm curious if PostgreSQL's Group Commit mechanism (controlled by commit_delay and commit_siblings parameters) only affects disk fsync calls. Or does it also allow multiple on-the-fly transactions to ...
ASBai's user avatar
  • 103
0 votes
0 answers
116 views

Behavior Transactions PDO::beginTransaction() and "BEGIN"

I have a strange behavior in a php script (PHP 8.1) using MariaDB 10.7.5 database. This one with native SQL works: $pdo->query("BEGIN"); [ ... some database stuff ... ] $pdo->query(&...
Marco's user avatar
  • 97
0 votes
1 answer
135 views

Debug application performing transaction

I have an application to debug which performs a few dozen operations against a MySQL database. These operations are performed in a single transaction. I need to know the state of the database while ...
dotancohen's user avatar
  • 1,075
0 votes
0 answers
61 views

Locking for secondary B-tree

I implemented a heap with MVCC; that is, each tuple in the heap has a history list so that updating the tuple does not block reading. I also implemented a secondary B+ tree; that is, it stores (...
GenGG's user avatar
  • 15
0 votes
2 answers
698 views

Concurrently update counter with least amount of locking

Using Sql Server I need to have concurrent transactions update a counter, when they commit - but they must not block each other from updating the counter. Let's say the first started transaction takes ...
asgerhallas's user avatar

1
2 3 4 5
15