Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
764
questions
0
votes
0
answers
36
views
SQL via JDBC fails on MSSQL Server with BEGIN TRAN
Running this SQL via JDBC on MSSQL works as expected:
UPDATE costcenter
SET updatetimestamp = Getdate(),
[de] = 'CostCenterName',
[en] = 'CostCenterName_en'
WHERE [customerid] = '...
2
votes
2
answers
265
views
If a trigger runs an update will it ALWAYS have the same timestamp for a temporal table?
Background
Here is an example near to what I am working with:
CREATE TABLE sandboxTesting.TemporalTest (
GroupNumber VARCHAR(25) NOT NULL,
StartEffectiveWhen DATE NOT NULL,
...
- 88k
15
votes
4
answers
4k
views
Why do temporal tables log the begin time of the transaction?
When updating a row in a temporal table the old values for the row are stored in the history table with the transaction begin time as the SysEndTime. The new values in the current table will have the ...
- 88k
0
votes
0
answers
14
views
Understanding MySQL's Deadlock for un-related tables
I see the following Deadlock in my show engine innodb status's output:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-08-22 09:35:46 0x7f70f2d2d700
*** (1) TRANSACTION:...
- 21
0
votes
1
answer
2k
views
PostgreSQL Foreign Data Wrappers - Simultaneous queries won't finish
We're using foreign data wrappers in a database which points to another server (which is a read-only replica). We run scheduled jobs using python ( more on this here: https://github.com/sqlalchemy/...
CommunityBot
- 1
5
votes
3
answers
525
views
Lost Update Understanding
https://habr.com/en/company/postgrespro/blog/467437/ gives the following example of a Lost Update:
For example, two transactions are going to increase the amount on the same account by ₽100 (₽ is ...
- 101
0
votes
0
answers
36
views
PostgreSQL: using stale statistics instead of current ones because stats collector is not responding
We are running PostgreSQL 13 on Azure Centos VM and found this problem from the logs followed by some slow commit and SET statements. The slow statement logs are only lasted for a period of less than ...
- 341
1
vote
1
answer
480
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). ...
CommunityBot
- 1
1
vote
1
answer
752
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) ...
CommunityBot
- 1
1
vote
1
answer
42
views
Setting a value for max_locks_per_transaction
We find ourselves in a situation where we need to increase the value max_locks_per_transaction as the default isn't appropriate. I've spent some time looking and I can't find any information to ...
- 54.8k
0
votes
1
answer
336
views
Transaction - Timestamp Ordering. When is aborted transaction restarted
(I don't know if this is the correct place to ask, since this is more like a theoretical question).
I have a question about restarting aborted transactions. I don't know when / at which point should ...
CommunityBot
- 1
0
votes
1
answer
387
views
Try Confirm Cancel (TCC) Protocol
My Confusion
I have been reading up about the Try Confirm Cancel (TCC) Protocol, and I understand the main idea behind it (for the happy flow). My confusion comes in how do we actually implement or ...
CommunityBot
- 1
0
votes
2
answers
99
views
Why must I commit after the ALTER TABLE DDL to make changes visible?
If I execute this in DBeaver:
alter table classes alter column reference set not null;
on my local database which only I have open in DBeaver, it completes successfully.
But then I can't open the ...
- 23.6k
4
votes
1
answer
569
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 ...
CommunityBot
- 1
0
votes
0
answers
20
views
why the mysql 8 database auto generate a long running transaction
Today when I run an update command run spring boot application, the MySQL 8 generate a long running transaction and block the next update command. This is the output when I check the transaction:
...
- 805
0
votes
1
answer
389
views
What is the best solution to prevent picking the same value (by multiple requests)?
Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick ...
CommunityBot
- 1
3
votes
1
answer
940
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 ...
CommunityBot
- 1
1
vote
1
answer
340
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
1
vote
1
answer
470
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 ...
CommunityBot
- 1
0
votes
1
answer
46
views
Conditional update
I would like to create a conditional update on an audit table, such that if the update on the main table succeeds, then the update on the audit table is performed.
Right now I have this
CREATE DEFINER=...
- 904
0
votes
1
answer
615
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 ...
CommunityBot
- 1
0
votes
1
answer
299
views
Do I need to set "ConnectionReset" parameter in the connection string when using isolation levels occasionally?
I have a MySql 5.6.44, and an dotnet application using Mysql.Data 8.0.12.
The application code has not specified any isolation level till now because the default one was enough. Suddenly we need to ...
CommunityBot
- 1
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-...
CommunityBot
- 1
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. ...
CommunityBot
- 1
0
votes
1
answer
2k
views
MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure
I have this stored procedure:
START TRANSACTION;
code_gen_loop: WHILE @Codes > 0 DO
IF MOD( @Codes, 10000 ) = 0 THEN
EXECUTE qry_status_stmt2;
EXECUTE qry_status_stmt3;
...
CommunityBot
- 1
5
votes
2
answers
853
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.) ...
- 173
0
votes
0
answers
9
views
Serialisable Schedule but not View Serialisable
These are the definitions I have for Serialisable and View Serialisable.
I know that View Serialisability => Serialisability and I want to find an example of a schedule that is Serialisable but ...
- 1
0
votes
1
answer
75
views
Optimizing XID management in PostgreSQL: How to avoid INSERT locks in tables?
I would like to raise a question regarding the optimal practices for managing transaction age (XID) in a PostgreSQL database.
I am currently using the method of performing VACUUM FULL operations on ...
- 1
2
votes
2
answers
1k
views
Why does the SERIALIZABLE transaction isolation level not guarantee point-in-time views of data?
I have been told that if your transaction consists of multiple statements, then the SNAPSHOT transaction isolation level is the only way to guarantee consistent point-in-time views of data. Why is the ...
- 88k
1
vote
2
answers
63
views
MySQL exclusive lock not appearing to work for new child rows
I have some logic in my application which I think results in the following MySQL calls, however when I get two of these run within a couple of milliseconds, I get two incompatible child rows.
Start ...
- 3,662
1
vote
1
answer
93
views
Limiting the number of rows that can be inserted for a given WHERE clause
In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions?
I have a table event_attendance with ...
- 180k
0
votes
0
answers
107
views
Handling deadlocks in Laravel during concurrent DELETE and INSERT , ON DUPLICATE KEY UPDATE operations
in Laravel I have a service which is not wrapped in a transaction and involves both INSERT ... ON DUPLICATE KEY UPDATE and DELETE statements targeting the same table, customers.
INSERT INTO customers (...
- 103
1
vote
1
answer
162
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
#...
- 1,206
2
votes
0
answers
126
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 ...
- 21
0
votes
0
answers
21
views
I need help pulling info from a .txt file into a table in either access or excel
So.. I've tried both Excel and Access to do what I need but I don't know the programs well enough to get them to do what I want, if it's even possible.
I have a hourly updating .txt file with ...
- 3,662
0
votes
1
answer
290
views
Can't bring up slave from ec2-consistent-snapshot due to uncommitted prepared transaction
I'm struggling with bringing up a slave instance using a snapshot created by ec2-consistent-snapshot, in my log it's describing the fact that an unprocessed transaction exists, but isn't that what ec2-...
CommunityBot
- 1
1
vote
1
answer
110
views
What happens when RCSI is enabled in one database but not another, and you query both?
Suppose that I have two databases on the same server, RCSI_Yes and RSCI_No. In the first, RCSI is enabled. In the second, it is not. How does SQL Server decide what transaction isolation level should ...
- 85.8k
-4
votes
2
answers
264
views
What do developers need to know about Read Committed Snapshot Isolation? [closed]
My developers have only ever worked with Read Committed, but my new server has Read Committed Snapshot Isolation (RCSI) enabled. What do they need to do differently when writing application code?
- 11.1k
2
votes
1
answer
437
views
Catching exceptions and rolling back transactions
I am just trying to see if I understand this correctly:
By default, any error occurring in a PL/pgSQL function aborts
execution of the function and the surrounding transaction
I have a procedure ...
- 180k
1
vote
1
answer
75
views
Is MariaDB Acquiring Exclusive Lock during Delete?
There is a Spring batch job with one delete and many inserts within a transaction with isolation level READ_COMMITTED:
delete from TABLE_A;
insert into TABLE_A values (...);
insert into TABLE_A values ...
- 78.8k
1
vote
1
answer
83
views
how can I see how many records have been affected by a transaction before it is committed? or how costly would be the rollback if I have to kill it?
I am using sqlwatch to monitor some of my servers and it has a job that deletes in batch from a table called dbo.sqlwatch_logger_snapshot_header
I had lots of problems of locking and blocking as this ...
- 13.9k
0
votes
1
answer
72
views
Repeatable reads in case protection from duplicated inserts in POSTGRES
I would need to perform this kind of logic in a serializable way:
account table has columns: 'accountName', 'location', 'worth', ...
Assume no unique key exists.
Some accounts are logically (according ...
- 113
0
votes
1
answer
127
views
Understanding Lock Behavior in SQL Server Deadlock Scenario with INSERT
Hello SQL Server Experts,
I'm currently experimenting with SQL Server deadlocks and have intentionally created one to better understand locking behavior. After setting up a test table, I ran two ...
- 607
0
votes
1
answer
75
views
How to set up locks to prevent stale data being written
I'm mainly a C#/Javascript developer, with only a basic understanding of SQL and databases. I've been doing some reading on transactions and locks, but I'm still struggling to figure out how to set ...
- 286
1
vote
2
answers
97
views
Transaction management work under Instance or database
I remember from university that:
By Default, doing Insert, Delete and update on 2 or more data sources can't be participant under a transaction
Please consider this this code:
begin tran Insert2
...
- 38.6k
0
votes
0
answers
57
views
Why postgresql creates force subtransactions in large statement except sql commands (savepoint...)?
I performed following large transaction without any sub transactions:
begin
for i in 1..10000000 loop
INSERT INTO src.customers
(id, first_name, last_name, email)
values
(i,'First Name', 'Last Name', '...
4
votes
1
answer
4k
views
Complex constraint across all data in a table
We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status.
I want to ensure that the result of this is always either zero or ...
- 180k
3
votes
1
answer
374
views
SQL Server do not block table/row when I update row to the same value
create table test (
id int identity,
id_int int default 1
)
insert test default values
go 1000
begin transaction
update test
set id_int = id_int
where id = 1000
waitfor delay '00:00:10'
...
- 744
-1
votes
1
answer
247
views
Single-statement transactional consistency
I ran into this Q&A on StackOverflow the other day. To quote comments from the question author, the situation seems "somewhat horrifying". A similar situation is set up in this Q&A.
...
- 85.8k