Questions tagged [transaction]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
23 views

Difference in location of transaction isolation level declaration - inside/ouside a transaction?

I have been looking though some stored procedured on our system today when I saw the following structure: (pseudocode indicative of actual SP, server is using the default isolation level): CREATE ...
1
vote
1answer
21 views

What are the performance differences of inserting a bunch of data with one INSERT versus a bunch of INSERTs in a transaction?

I'm trying to understand the performance differences between something like INSERT INTO Person (id, name) VALUES (1, "Kevin"), (2, "John"), (3, "Jane"), ... and BEGIN TRANSACTION; INSERT INTO ...
2
votes
2answers
43 views

Transactions in SQL Server Emergency Mode

I know that while the database is marked as suspected users cannot start new transactions, and the only access is via emergency mode which gives only read only access. I want to know how are ...
0
votes
0answers
10 views

Perfect DB design for Transaction feature [closed]

In my current project i am working a feature where people will buy/sell each others products. So let's say User1 has two products and User2 has 1 product. Now if User1 buys one product from User2. And ...
0
votes
0answers
32 views

PostgreSQL Race condition and Serializable

Hello I try to debug a tricky race condition in my app (golang, pq, sqlx, postgresql Debian 10.6-1.pgdg90+1). I have a table CREATE TABLE j ( id BIGSERIAL PRIMARY KEY NOT NULL, ...
2
votes
3answers
79 views

when do lock actually happen in a transaction?

Let's say we have a transaction A: BEGIN TRAN A; //--------------------< time a SELECT productid, unitprice FROM Production.Products WHERE productid = 2; //--------------------< time b ...
1
vote
1answer
19 views

Isolation level serializable not working as expected?

I have this table create table testing( c1 text, c2 text); I open two transactions simultaneously. t0 t1.. denote series of time snapshots in increasing order Transaction 1 BEGIN; -- t0 SET ...
-3
votes
1answer
40 views

System failure causes inconsistency between two transactions

Let's say the picture above the transaction 4 for user A. A updated a record from Account table, change the balance from 100 to 0, then a checkpoint occurred, so all dirty pages got written to disk. ...
0
votes
0answers
22 views

MySQL - Investigate deadlock on atomic update

On a high traffic queue managed by Redis there is the following code: update `ads_publishers` set `impressions` = `impressions` + 1 where `ad_id` = ? and `pub_id` = ? Here's is the table schema: ...
0
votes
1answer
59 views

How SQL Server transaction works with log records

My textbook says: Starting when you begin a transaction, your changes are isolated from other users. What you’re doing is visible to you only,and isn’t really done until you COMMIT — although it ...
2
votes
0answers
55 views

When updated records get written to the real physical pages in mdf file?

I'm a beginner in SQL Server, just some questions on transaction. My textbook says: A checkpoint is a periodic operation that forces all dirty pages for the database currently in use to be written to ...
1
vote
1answer
23 views

How to update two records with two different values with two 'where' conditions at once?

In T-SQL, how to have this: DECLARE @amount decimal(18,2) = 1000 UPDATE account SET balance = balance + @amount WHERE accountID= 1; UPDATE account SET balance = balance - @amount WHERE ...
15
votes
1answer
1k views

What exactly is the 'online' in OLAP and OLTP?

I'm a bit confused because I'm questioning the definition of 'online' in OLTP and OLAP. I used to think that 'online' here means that we want our answer in bounded time and based on the data available ...
1
vote
1answer
17 views

Do the action blocks of an ALTER TABLE statement run atomically?

In Postgres, ALTER TABLE can accept multiple action forms, separated by a comma. For example: ALTER TABLE mytable DROP CONSTRAINT pkey, ADD CONSTRAINT pkey PRIMARY KEY USING INDEX myindex; ...
1
vote
1answer
47 views

Deadlock on the same resource calling the same procedure

I would like to kindly ask community for help with this deadlock scenario: <deadlock-list> <deadlock victim="process17392264e8"> <process-list> <process id="...
0
votes
0answers
27 views

Memory usage of large transactions

I have an application which persists itself to a PostgreSQL 11 server. It does this by truncating the tables (removing the old state) and then uploading its current state (many statements) as part of ...
-1
votes
2answers
60 views

When is an Insert committed?

If I highlight/select both the statements below, without a GO stmt between them, will the Insert be committed once it shows 'Rows Affected 1234' in results pane? If so, can I then cancel the Rebuild ...
2
votes
2answers
44 views

SQL Transaction - use values before commit?

So I am creating a stored procedure in SQL2014. Towards the start it has: Begin Transaction Then it does an insert into a table, which has an auto-incrementing primary key: Insert into tNotice. ...
1
vote
1answer
20 views

Which part of a DB bulk insert takes the most time?

One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit ...
3
votes
1answer
71 views

An uncommitted transaction updates a column; how can a different transaction see the old column value?

Let's assume the following case. There is a students table: id | name ----------------- 1 | Mateus A "User A" executes the following statement without committing the transaction: update students ...
8
votes
3answers
866 views

transaction log in RAM or physical file?

I'm a beginner in transaction, just a question on transaction log. We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical ...
9
votes
3answers
511 views

Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?

In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT, CREATE TABLE foo ( a int );...
3
votes
2answers
139 views

Gap Locking in Read Committed isolation level in Mysql

MYSQL VERSION : 5.7.X STORAGE ENGINE : Innodb I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where ...
1
vote
0answers
24 views

Preserving some data on rollback in InnoDB?

I want to keep log of attempted, sucessful, and failed operations (both for troubleshooting, and also for detecting malicious users trying things). I want to keep that log in InnoDB MySQL table. Now, ...
4
votes
1answer
63 views

postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client

We are using postgres 11's stored procedures to do looped update of rows in batches. After a batch is updated, a 'progress' table is updated with progress percent and status, THEN a notification (...
4
votes
1answer
69 views

How to avoid deadlock for this query?

I am running a MySql 5.7 database and I have a query which results in a deadlock as it is running in multiple threads. I am trying to figure out how to avoid this deadlock, but so far I haven't been ...
1
vote
2answers
171 views

Very slow simple INSERT in transaction

I have I problematic query on my SQL Server where I could not find the problem. I have two simple tables, one for the "Headers" and one for the "Items" (each with an PK column, total of five fields ...
3
votes
2answers
218 views

Best practices for committing a transaction in SQL Server where TRY CATCH is used

In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?. For example, is option A or option B the correct approach or are they ...
1
vote
1answer
45 views

PostgreSQL ensure no concurrent reads

I'm sorry if it's a very basic question, but would really appreciate educated advice on this: Say I have an operation, which first has to read from a table and then insert a row if the read value ...
0
votes
1answer
83 views

Safe to use the same temporary table name in parallel transactons?

Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
0
votes
0answers
40 views

Transaction on defriend and multi databases

I have a big issue when i'm working with multi database and diffrent databases as same time. The problem is that I'm working with mssql, mysql and mongodb at sametime, and what I need is a way to be ...
1
vote
1answer
14 views

Wraparound Transaction ID

Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum ...
2
votes
1answer
92 views

Is it possible to step through TSQL that uses variables, in SSMS?

If I enter TSQL into SSMS Query editor, I know I can "step through" the statements (ie. execute them one at a time) by highlighting a statement and hitting F5 on the keyboard. It seems by default ...
-1
votes
2answers
47 views

Is there any possible solution to insert correct records and prevent incorrect records from being inserted by insert statement in SQL server? [closed]

I know that each statement (i.e: Insert , Update , Delete ) in SQL-Server is treated as one single transaction and I know that one of the main characteristics of a transaction is it's 'Atomicity' so ...
5
votes
3answers
601 views

How to inspect global temp table from outside the transaction it is created in

To help debugging a batch of SQL (that is run inside a transaction), inside the transaction I dump some data into a global temporary table. The global temporary table is created inside the transaction ...
0
votes
0answers
15 views

InnoDB: does `redo log` contain initial value and final value? or just final value?

start transaction; update users set age = 34 where user_id = 3 // update age from 30 to 34 years old. .... .... when I am updating the age of this user, InnDB will write redo log. What's the ...
0
votes
0answers
47 views

MySQL: why undo log is required in the processes of database recovery?

Let's imagine that an incident happens when you updates. start transaction; update users set gender = 1 where id = 3; Undo log space is allocated. Previous values are copied to undo log Record of ...
1
vote
2answers
59 views

How a Transaction is atomic?

I am really confused as to how to digest the fact that a transaction is atomic. If a transaction is set of "queries" then how will it be atomic. I am relating the word "queries" to a general SQL query....
0
votes
0answers
24 views

Utilizing DBLINK[_EXEC] to control transactions within PL/PgSQL functions?

I am exploring options to control transactions within functions, mainly to reduce the memory footprint for large operations over multiple individual units. I.e. large data-sets are inserted regularly, ...
0
votes
0answers
36 views

Why Update join sql locks a table in Mysql 5.6

I'm encountering transaction error like this. ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2018-12-25 08:45:10 2b128be88700 *** (1) TRANSACTION: TRANSACTION 112497037, ...
0
votes
1answer
73 views

Using `transactions` without `select … for update`?

I have a table of ranges that should never intersect: id | from | to -------------- 1 | 3 | 5 2 | 12 | 14 3 | 20 | 24 4 | 27 | 30 I have an algorithm that does the following: Find gaps ...
0
votes
2answers
26 views

Keep a history of transactions for easy rollback

Is there a database (either SQL or NoSQL) which allows you to keep a history or log of all past transactions so that you can easily rollback to any given point in time (similar to how git manages a ...
1
vote
1answer
21 views

Database transaction consistency on PostgreSQL

In a table with three columns (serial id, varchar name, varchar email) and having two different transactions executing concurrently as the ones bellow: Transaction 1: BEGIN; INSERT INTO public.users ...
1
vote
2answers
148 views

Elevate transaction isolation in Maria DB

I am migrating code from Microsoft SQL to Maria DB. In the MSSQL stored procedure a transaction will be started with repeatable read and then later conditionally elevated to serializable if needed. In ...
0
votes
1answer
234 views

SSIS Error :Cannot roll back .No transaction or savepoint of that name was found

Hope you are Doing well. I have 1 SSIS Package as you can see below : In the "Insert" part of the Package which is an Execute sql task, I'm executing a Stored procedure . this is the code inside the ...
1
vote
1answer
56 views

Commit() takes 50 seconds after inserting about 100000 rows

I have a sqlite database that is, (I think), fairly small. I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb. I then open a transaction and insert about ...
2
votes
1answer
58 views

Which database transaction log will increase?

I found a query miss written in one of our databases like this Use database2 go delete database1.dbo.table1 from table1 as t1 join table2 on t1.column1 = t2.column2 and t2.column3 = 1 I am having a ...
1
vote
1answer
879 views

How to use variables in procedure (not function)? [closed]

As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to: declare a variable select a value into a variable insert the variable into ...
3
votes
2answers
76 views

Can independent applications cause roll back to each other's transactions?

I have a database built on SQL Server Application-1 [Legacy App]: Written in C#, and runs transactions on the above database. Application-2 [New application being developed]: Written in Python, ...
0
votes
1answer
33 views

BEGIN / COMMIT atomic update

I try to generate unique identifiers for the ticket table's records. A ticket belongs to a file record, which also have an identifier. If the file's identifier is 'SOMEPREFIX/F01' then its tickets ...