Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
584
questions
-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 ...
