Questions tagged [transaction]

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

Filter by
Sorted by
Tagged with
-1
votes
0answers
21 views

Default Concurrency Control Implementation in MySQL

What is the default implementation of concurrency control in MySQL? Is it optimistic locking (multi version concurrency control), or pessimistic locking (2 phase locking)? More specifically, how does ...
1
vote
2answers
58 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 ...
-1
votes
1answer
30 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 ...
1
vote
2answers
20 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 ...
0
votes
2answers
33 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
1answer
19 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 | ...
1
vote
1answer
17 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 ...
0
votes
2answers
61 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 ...
1
vote
1answer
45 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 ...
0
votes
1answer
31 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; -...
0
votes
1answer
26 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 %] ...
0
votes
1answer
11 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
1answer
39 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]...
0
votes
2answers
60 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 ...
0
votes
2answers
44 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 ...
0
votes
2answers
31 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
vote
1answer
39 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 (...
0
votes
2answers
105 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 ...
1
vote
1answer
47 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 ...
0
votes
1answer
41 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 ...
1
vote
0answers
30 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 ...
0
votes
1answer
209 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 ...
1
vote
1answer
36 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) ...
0
votes
1answer
33 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 ...
0
votes
1answer
155 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: ...
0
votes
1answer
41 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 ...
0
votes
2answers
80 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 ...
2
votes
2answers
310 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 ...
0
votes
0answers
71 views

Long Running Transaction in MySQL

I have very Long Running Transactions in MySQL (RDS Aurora) select * from information_schema.innodb_trx where trx_id = 45638005; +----------+-----------+---------------------+-----------------------+-...
0
votes
0answers
26 views

Does the order of query operations inside transactions matter?

Does the order of query operations inside transactions influence the performance or not? Does it depend on the low-level DBMS concurrency control protocol, e.g., 2PL, MVTO, OCC?
2
votes
2answers
63 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 ...
1
vote
1answer
20 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 ...
2
votes
1answer
58 views

How the single SQL statement is isolated?

All the books I've seen so far, talking about transactions show scenarios with several SQL statements involved. But what's about single statements? What level of isolation do they have? Is it ...
7
votes
3answers
313 views

What events still occur within a transaction that gets rolled back?

Is it true that all data modifications are undone when the transaction they are a part of gets rolled back? For example if a cursor that just executed a stored procedure 100 times that updated a ...
0
votes
1answer
76 views

Do table locks get released after a query finishes when WAITFOR is used after that query within the same batch and transaction?

If I have a query that reads from a table, if that query is proceeded with the WAITFOR keyword (within the same batch and transaction) will the table lock of the preceding query be held until the ...
0
votes
0answers
68 views

Long Running Transactions with Active Status

I could see long running transactions in my Innodb Status, I'm using RDS Aurora Mysql 5.6 Version. ---TRANSACTION 977075315, ACTIVE 70113 sec 2 lock struct(s), heap size 376, 0 row lock(s), undo log ...
0
votes
1answer
128 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) ...
1
vote
0answers
45 views

Sequence number for COMMITTED concurrent inserts

For example, I have a table create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type and many concurrent inserts into this table. Is there is a way to fill the OrderNum ...
0
votes
1answer
538 views

“Lock wait timeout exceeded; try restarting transaction” for my delete query

I'm using RDS Aurora with MySQL 5.6. I have a delete query on a table which runs once in a day and it fails with lock wait time outexception, try restarting transaction. This issue is not happening ...
1
vote
2answers
321 views

How does autocommit=off affects bulk inserts performance in mysql using innodb?

I know that turning off autocommit can improve bulk insert performance a lot according to: Is it better to use AUTOCOMMIT = 0 And I have made experiments to confirm that conclusion. But what I want ...
9
votes
1answer
435 views

Key-range RangeI-N lock compatibility in SQL Server

The documentation states that the RangeI-N lock (Insert range, null resource lock; used to test ranges before inserting a new key into an index) is compatible with itself (see the compatibility matrix)...
0
votes
1answer
76 views

How to optimize stored procedure so it won't end with “Out of memory exception”

I have created a stored procedure that check against 2 and then delete from the 3. When I execute the stored procedure on Management Studio I get the exception as mentioned in this question: ...
0
votes
1answer
59 views

Is it safe to leave/exit the stored procedure without commit or rollback on a transaction?

Like the code below is it safe to do that? create definer=`root`@`%` procedure `test`() label: begin declare exit handler for sqlexception rollback; start transaction; select condition from table ...
0
votes
2answers
41 views

How can I force a minimum isolation level in a procedure?

How can I force a minimum isolation level in a procedure in mssql? I can use "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;" inside my procedure to set the transaction-level. But this command will ...
6
votes
1answer
217 views

Change transaction isolation level inside running transaction

I'm studying transaction isolation levels in SQL Server, and I'm trying to figure out how SQL Server behaves when the isolation level changes during the lifetime of the transaction. It seems that ...
5
votes
2answers
305 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 ...
0
votes
1answer
49 views

SQL transaction: Delete between two selects possible?

I have two SELECT statements within a transaction (repeatable read) Select @firstItem =id from myTable Where .... --Do some more magic, so I can't concat the the two queries!!!! and Select * from ...
43
votes
9answers
5k views

Asked to Not Use Transactions and to Use A Workaround to Simulate One

I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have ...
0
votes
2answers
82 views

Pending changes from a finished job

I'm getting the following error: Uncommitted changes pending for file MYTABLE in library MYLIB. CPF325E Cause . . . . . : Uncommitted changes are pending for file MYTABLE in library MYLIB for ...
0
votes
1answer
290 views

Debugging a hanging session/lock

We are having a recurring situation where we have 2 one query hanging, and other queries waiting for that one, and our DB grinds to a standstill. I am trying to debug this and am seeking help. The DB ...

1
2 3 4 5
12