Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
738
questions
3
votes
0
answers
710
views
After Upgrade/Migration to SQL Server 2019 Databases Periodically Gets Stuck In a Inaccessible State [closed]
I have a bit of a unique challenge, I have a series of test databases I use for CI type database testing, generally with each release I will restore (overwrite) these databases from a backup, update ...
- 205
0
votes
1
answer
52
views
Allow entering application while in transaction
We've got a process that needs to run a certain set of stored procedures in a transaction and fail if one of those fails...
Simple we have something similar to:
BEGIN TRY
BEGIN TRANSACTION;
...
- 289
1
vote
0
answers
28
views
Time-Order preserving concurrent reservation of non distinguishable items
Problem statement:
Its very similar to a traditional booking system, however it is a bit different.
There are non-distinguishable items (e.g. like money on bank account) of different types that can be ...
- 11
0
votes
1
answer
574
views
Can PostgreSQL UPDATEs in autocommit mode deadlock?
Is it possible for UPDATEs in PostgreSQL to deadlock when in autocommit mode? If so, how, and why? (Or, why not?)
- 171
1
vote
1
answer
355
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 ...
- 11
0
votes
1
answer
1k
views
How to ensure no duplicate records are added when using multiple Node JS processes
We are working on an appointment scheduler. We have a table Appointment that stores an appointment with starttime, endtime, assigneeId and eventId.
We are using Bookshelf.js as an ORM with Node JS. ...
- 249
0
votes
1
answer
39
views
How can I generate a consistent entity graph (say from 10 tables) from a database fetch
Suppose I have many tables in a database that need to be joined together to get a single consistent view of the entire entity graph. Am I forced to do this as a single query? I don't care about locks, ...
0
votes
1
answer
3k
views
"invalid transaction termination" in Postgres when trying to update a value using a function
I'm new to postgres. I defined a function like this:
CREATE OR REPLACE FUNCTION increment_count()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
UPDATE posts SET count=count+1 WHERE pid=...
- 103
1
vote
1
answer
515
views
MariaDB or MySQL @@SESSION.tx_read_only always returns 0
I'm trying to figure out possible "read only" transaction setup leak between pooled connections (on the same physical connection) in Java application which lead me to a question "How ...
- 113
4
votes
1
answer
566
views
In SQL Server, in layman's terms, how do you lock resources well enough to perform an INSERT-IF-NOT-EXISTS transaction?
If someone asks how to perform an INSERT-IF-NOT-EXISTS operation in SQL Server, they'll typically get an answer like this back:
IF NOT EXISTS(SELECT 1 FROM [TheTable] WHERE [ColumnX] = @valX)
...
- 157
0
votes
1
answer
234
views
Is my theory on INSERT deadlocks correct?
I originally had this (as three separate PG queries, from my applications):
INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
...
user214052
3
votes
1
answer
4k
views
If I actively BEGIN a transaction, but fail to ROLLBACK at error, will PG become confused?
Suppose I have this application pseudo code (it's actually PHP and communicating with PG with the pg_* functions):
function handle_update_of_foo_table()
{
BEGIN;
TRUNCATE TABLE foo;
...
user213936
1
vote
2
answers
448
views
Is this a correct understanding of transactions?
I have just noticed that one of my TRUNCATE queries takes 16 seconds to complete. I always assumed that TRUNCATE queries were basically-instant.
I currently do this:
TRUNCATE TABLE bigtable RESTART ...
user213904
7
votes
1
answer
716
views
Is there any side effect if select statements are wrapped in BEGIN TRAN ... COMMIT?
I am working on a rather old .NET project and have introduced some new features (on top) that have generated the following side-effect: all generated SELECTs (or groups of) are wrapped in BEGIN TRAN .....
- 1,261
0
votes
2
answers
7k
views
I need to do periodic commits in my purge function
I wrote a purge function that runs in Postgres 9.6. The function goes through all of the tables in our application starting from the bottom most child table and ending with the top most parent table ...
- 1,520
3
votes
3
answers
2k
views
(Deadlock) Select Query Causes Insert Query to be Blocking in Transaction
Database: MySQL
I have a running app with concurrency transactions in it. I noticed that there are so many errors occur related to deadlock.
The error says
SQLSTATE[40001]: Serialization failure: 1213 ...
- 135
1
vote
1
answer
303
views
Are snapshots generated for each sub query inside a Postgres READ_COMMITTED transaction?
From what I understand - in a READ_COMMITTED Postgres transaction “the transaction obtains a snapshot whenever an SQL command is executed” source. Does this mean a snapshot will be obtained for each ...
- 141
3
votes
0
answers
293
views
Is there a reliable way to determine the order in which Postgres transactions were committed without logical decoding?
Is there a reliable way to determine the serial order in which Postgres transactions were committed without logical decoding? From what I understand pg_xact_commit_timestamp can’t necessarily be ...
- 141
0
votes
1
answer
513
views
Is it possible to make a trigger that does not allow INSERT?
I'm currently learning how to implement sqlite commands through C code and I've been trying to figure out the best way to ensure that no data can be inserted to a table (which will only happen during ...
2
votes
1
answer
349
views
Why does MYSQL use blocking locks instead of optimistic attempts that fail immediately?
Why isn't optimistic concurrency control more commonly supported at the database layer? It is a valid alternative and can completely do away with any blocking locks. For certain usage patterns, this ...
- 121
1
vote
2
answers
618
views
PostgreSQL sometimes fails to detect serialization failures
I have an http-server in application language that looks roughly like this:
item = SELECT item FROM table WHERE field = 'value'
if (item) {
UPDATE another field on item
} else {
INSERT item VALUE ...
- 151
-1
votes
1
answer
138
views
MySQL Code implementation of locking and transactions
I am trying to understand the internals of how MySql (using InnoDB) implements locking and transactions in code. I understand the different types and concepts of locks and transactions, but I really ...
- 101
1
vote
2
answers
178
views
Postgres Foreign Data Wrappers for Data Migration. Do transactions hold across databases?
I want to migrate some data from Postgres database A to database B. I found Foreign Data Wrappers that allow you to connect to a table in B while in database A. I have a table in A that I want to move ...
- 307
0
votes
2
answers
289
views
PostgreSQL concurrency control and Serializable transactions
Im building an application that needs to do some concurrent read/write operations on specific tables. Even after reading the official documentation (and many, many articles), the trouble persists, no ...
0
votes
1
answer
137
views
Transaction isolation level/design for write-only ledger
This is a simplified example of a problem I've been working on. Say I have the following database schema:
Table: Deposits
+--------+------------+--------+---------+
| ID | Date | Amount | ...
- 101
1
vote
1
answer
294
views
Using Transactions with PostgresQL dependency issue [duplicate]
I am trying to use transactions when inserting into a PostgresQL DB, however due to dependencies, I ran into some issues:
During the transaction I am inserting into multiple tables. The first table ...
- 111
0
votes
2
answers
269
views
MSSQL: Auto-numbering rows in commit order
General setup
I have an event table with an ID field and some data. Multiple producer threads should be inserting events to this table at the same time. Each batch of inserts is done in a separate ...
- 103
1
vote
1
answer
600
views
How to guarantee data consistency across multiple reads?
Let's imagine my application needs to do a series of consecutive SELECTs (to different tables) to collect various bits of information from the db, and we don't want any of those tables to change while ...
- 57
0
votes
1
answer
1k
views
syntax error at or near TO (ROLLBACK TO SAVEPOINT)
The following sql is a part from a FUNCTION ,i want to rollback to rbk SAVEPOINT :
for ptrEnt in entite loop
-- positionnement du point de retour pour une entité
savepoint rbk;
-...
- 29
0
votes
1
answer
2k
views
How to pass a variable to a stored procedure?
Calling the following procedure in DO $$ bloc seems to not work correctly .
Procedure :
CREATE OR REPLACE PROCEDURE pLog(P_VAR_LOG text)
AS $$
BEGIN
IF D_SetLog THEN
RAISE NOTICE '[LOG %] ...
- 29
0
votes
1
answer
29
views
how to setup mongodb replica on cloud server?
I want to setup mongodb repicaset on my cloud server(AWS ec2) but I have only one IP address.
Currently I have make /etc/hosts two entries 127.0.0.1 dbnode1 and xx.xx.xx.xx dbnode2 but when I try to ...
2
votes
1
answer
4k
views
ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER
I give an example to show my problem. I create a table as the following:
CREATE TABLE a
(
id INT
)
I create a AFTER INSERT trigger to don't allow to insert id=1 into table "a"
CREATE TRIGGER [dbo]...
- 51
2
votes
2
answers
2k
views
Does PostgreSQL ever retry failed transactions internally?
I am writing an application in Python and PostgreSQL (using psycopg2) and in one section we are using the following pattern.
As separate python commands which I assume translates to separate calls to ...
- 123
0
votes
2
answers
227
views
Why does SQL Server start REDO phase from minLSN instead of last CHECKPOINT?
while reading an article from BOL, I stuck on the picture which explains the recovery process of database (without ADR):
Shouldn't Phase 2:Redo start from Last CHECKPOINT as till that point ...
- 1,253
0
votes
2
answers
711
views
Can somebody explain BEGIN; and COMMIT; for me once and for all?
Let's say I have this PHP code:
dbcall('BEGIN');
dbcall('CREATE SCHEMA "cool schema"');
dbcall('CREATE TABLE "cool schema"."cool table"');
dbcall('COMMIT');
Am I correct in thinking that this will ...
- 1
1
vote
1
answer
54
views
Select into query, insert fails, but the table is created
I am using SQL Server 2016 and I tried to the following query.
SELECT CONVERT(BIGINT, 'A') col1 INTO #tmp
This query is obviously in error. Because it does not convert. However, the temporary table (...
- 11
0
votes
2
answers
2k
views
ROLLBACK TO SAVEPOINT syntax error at or near "TO"
I am having trouble in executing the following FUNCTION in PostgreSQL
CREATE OR REPLACE FUNCTION pckg_fcvoi.supprimer_voies_plus_42c (CodeEntite text) RETURNS VOID AS $body$
DECLARE
...
- 29
2
votes
1
answer
1k
views
If I kill a looping INSERT query, will it rollback all inserts since the loop started or only the last iteration?
Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping?
DECLARE @LoopId INT = 1;
WHILE (@LoopId < 100)
BEGIN
INSERT INTO Table2
...
- 35.1k
0
votes
1
answer
93
views
Details of transactions in SQL SERVER 2008 R2
I need following details:
Username of the user who initiated the transaction
Origin of the query i.e. Whether Query executed in SQL server New
Query Interface or Transaction due to editing in Table ...
- 3,131
1
vote
1
answer
162
views
Do I always need boilerplate code to prevent nested transactions?
Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one?
It seems like a ...
- 3,931
0
votes
1
answer
5k
views
MySQL Difference between BEGIN and START TRANSACTION
I'm using RDS Aurora (MySQL 5.6)
By default Auto Commit flag is enabled.
I have a transaction as follows
BEGIN;
INSERT INTO .....;// STATEMENT 1
INSERT INTO ......;//STATEMENT 2
COMMIT;
I'm ...
- 11
2
votes
1
answer
709
views
Rollback is not working if the storedproce call another storedproc
Is it intended behavior that if I call a stored procedure inside a stored procedure will automatically commit?
example:
pseudo procedure proc1
create procedure proc1(in var1 int, out var2 int)
...
- 453
0
votes
1
answer
230
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 ...
- 101
2
votes
1
answer
3k
views
What standard SQL isolation levels do the SQLite isolation levels correspond to?
There are four standard SQL isolation levels: Read Uncommitted, Read Committed, Repeated Read, and Serializable. (c.f. Database System Concepts 6ed.)
In SQLite, there are three isolation levels: ...
- 129
0
votes
1
answer
67
views
Duplicate record inserted - why trigger is not preventing it?
I have two tables: DebitIUVs and RPTs. I have record inside DebitIUVs which is with state 118001. My business logic must insert new line inside RPTs table. Inside RPTs table there is additional ...
- 1,163
0
votes
2
answers
817
views
Avoiding deadlocks in PostgreSQL with multiple clients
I am developing software which launches multiple, concurrent clients to connect to a PostgreSQL (12) database. When each client starts, the first thing it does upon connecting to PostgreSQL is to run ...
- 175
12
votes
2
answers
16k
views
Why am I getting a deadlock for a single UPDATE query?
I have two processes which execute code like this in parallel:
begin;
update foos set unread=false where owner_id=123 and unread=true;
commit;
This results in deadlocks.
My understanding of what ...
- 783
1
vote
1
answer
967
views
Would refreshing a materialized view in a transaction allow me to have test-only specific data on it?
I have created a postgresql database used in integration tests for my code.
Its schema is consisted with some materialized views and the code I want to test uses them, therefore I need once I place ...
2
votes
2
answers
935
views
What type of locks does BEGIN TRANSACTION cause against a specific table that's being read from?
In the following example, what kinds of locks are generated from the explicit transaction block that wraps the SELECT INTO query?
BEGIN TRANSACTION T1
SELECT Field1, Field2, Field3
INTO TableB
FROM ...
- 35.1k
1
vote
1
answer
103
views
Can I rely on the MSDTC to guarantee a distributed transaction was completed?
I've come across a bug using the TransactionScope from the .NET library, where we're getting a TransactionInDoubt exception. After doing a lot of research into something I don't know much about, I've ...
- 111