Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
738
questions
0
votes
2
answers
1k
views
Guaranteed rollback in SSIS
I'm trying to create a job in SSIS to truncate a table, read from a data source and rewrite the table, so only 2 steps (1 Truncate and 2 Select + Insert). I need to guarantee a Truncate's Rollback in ...
1
vote
2
answers
2k
views
What happens to an open transaction when the session window is closed?
I executed the following query in SQL Server 2012:
create table testTransaction
(
id int
name varchar(100)
)
insert into testTransaction values (1,'ABC'),(2,'XYZ')
/* Query1 */
begin try
begin ...
- 437
0
votes
1
answer
431
views
Aggregate each row and create new monthly column for the weeks to a month
I appreciate all your help. Below is my query and request.
Here is my staging table:
CREATE TABLE MyTable (
[WC] nvarchar(255),
[Requestor] nvarchar(255),
[MTR#] nvarchar (255),
[Date Added] ...
- 3
0
votes
2
answers
754
views
how long should an sql-server transaction run at most [closed]
our Teamcenter (Siemens' software) oftens runs transactions for hours, even days. As if they were forgotten.
There easily are even 20+ transactions running longer than an hour, even 20 hours.
They ...
- 127
0
votes
2
answers
100
views
Holding transactions as long as a user edits
As an application developer I'm used to using databases transactions only as a way to play in modifications after a user has clicked "save".
That's the way most database servers I'm familiar ...
- 755
3
votes
1
answer
330
views
The finer points of writers locking each other out under the snapshot isolation levels
The read committed snapshot and snapshot isolation levels in SQL Server do away with most locking except one: A writer still locks out other writers.
The documentation tiptoes around saying as much ...
- 755
0
votes
1
answer
3k
views
Run both of "DROP DATABASE" and "CREAT DATABASE" in CLI with "-c" option of psql of Postgresql
When debugging programs, I need to frequently drop a database of Postgresql, and then re-create it again.
To convenient, I want to execute this in a single command line like following:
psql -c "...
- 305
0
votes
1
answer
816
views
pg_restore error when using the --single-transaction: options c/--clean and -a/--data-only cannot be used together (even so the -a flag was not used)
I met a strange PostgreSQL (v.13.8) on a Windows server 2019 machine today when trying to restore a custom dump file.
The following command worked:
pg_restore -d postgres://postgres@localhost:5432/...
- 342
0
votes
1
answer
242
views
PostgreSQL: does the WAL group commit affect synchronous replication performance?
I'm curious if PostgreSQL's Group Commit mechanism (controlled by commit_delay and commit_siblings parameters) only affects disk fsync calls.
Or does it also allow multiple on-the-fly transactions to ...
- 103
0
votes
0
answers
124
views
Behavior Transactions PDO::beginTransaction() and "BEGIN"
I have a strange behavior in a php script (PHP 8.1) using MariaDB 10.7.5 database.
This one with native SQL works:
$pdo->query("BEGIN");
[ ... some database stuff ... ]
$pdo->query(&...
- 97
0
votes
1
answer
161
views
Debug application performing transaction
I have an application to debug which performs a few dozen operations against a MySQL database. These operations are performed in a single transaction. I need to know the state of the database while ...
- 1,075
0
votes
0
answers
63
views
Locking for secondary B-tree
I implemented a heap with MVCC; that is, each tuple in the heap has a history list so that updating the tuple does not block reading. I also implemented a secondary B+ tree; that is, it stores (...
- 15
0
votes
2
answers
795
views
Concurrently update counter with least amount of locking
Using Sql Server I need to have concurrent transactions update a counter, when they commit - but they must not block each other from updating the counter.
Let's say the first started transaction takes ...
- 225
0
votes
1
answer
162
views
Does B-tree in InnoDB use mark-delete?
I learnt that all secondary B-trees in InnoDB store pairs of the form (search key, TID) where TID is the primary keys of the records. I have the following questions:
(1) In InnoDB, does the secondary ...
- 15
1
vote
2
answers
57
views
Concurrency Control B-Tree between Hash
I am new to transaction. When I read the book "Transactional Information Systems", it is mentioned that B-tree uses key-range locking to guarantee serializability. Is that true all accesses ...
- 15
1
vote
1
answer
273
views
A query, safe against concurrent writes with READ COMMITTED, also safe with SERIALIZABLE isolation level?
I have asked this question with a wonderfully informative answer:
How to have uniqueness constraints for structures across multiple tables?
The answer from Erwin Brandstetter suggests this:
WITH ...
- 189
0
votes
1
answer
920
views
Find an open transaction across any session and any database in SQL Server
I know that we can use select @@trancount and select * from sys.sysprocesses where open_tran = 1 to find if a session has an open transaction in a given database. The problem with these statements is ...
- 437
0
votes
1
answer
1k
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/...
- 13
0
votes
1
answer
263
views
What happens to the n tables you insert into during a transaction?
With a database structure sort of like this:
create table objects {
uuid id;
string type;
}
create table object_properties {
uuid id;
uuid source_id; // the object which has this property
...
- 189
1
vote
1
answer
862
views
How to have uniqueness constraints for structures across multiple tables?
Say I have a schema system something like this:
create table objects {
uuid id;
string type;
}
create table object_properties {
uuid id;
uuid source_id; // the object which has this property
...
- 189
0
votes
0
answers
61
views
When gets transaction result visible?
I have a transaction that inserts a row into table, and then, in the JTA Synchronization afterCommit (on success) fires an event. The event can be later read in another thread and when this happens ...
- 101
0
votes
1
answer
126
views
Can you create SQL database transactions, and run things outside of those transactions while the transaction is opened?
I am sort of a newbie when it comes to transactions in databases, so don't have much experience with them. I am in the situation where I have a jobs table, which is a job queue, and I want to process ...
- 189
1
vote
1
answer
29
views
The approach to take when creating/editing many interrelated records in a transactional way?
So I have a fairly complex system I would think, that is starting to come about. It is too involved to write out all the tables, but here is a brief description.
Basically I am creating a badging ...
- 189
0
votes
1
answer
285
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 ...
- 101
2
votes
1
answer
78
views
MySQL InnoDB migrations custom implementation: How to deal with DML statements which trigger a commit in the background?
After some discussion on that topic I can assume that there is a quite frustrating fact about MySQL InnoDB: It does not support (atomic) transactions when it comes to DML.
If you have a database ...
- 215
2
votes
1
answer
532
views
How do databases guarantee that two transactions with different isolation levels run concurrently correctly
I wonder how databases guarantee that two transactions with different isolation levels run concurrently correctly. That is, different sessions are allowed to use different isolation levels. For ...
- 29
0
votes
3
answers
578
views
Rollback a transaction using single thread/multi thread
Would it be possible to rollback a transaction using a single thread?
I tried to find an answer to this question, but I couldn't find one that provided references. I read about rollback mechanisms. ...
- 216
0
votes
0
answers
82
views
What does OPEN means in LAST_REQUEST_TYPE in DB2?
In Db2 v11.5.7 on Linux I would like to monitor what is last request made by application. I assume one of application does not closes transaction properly.
To simplify the data I collected, I have the ...
- 325
2
votes
1
answer
715
views
Reading from table being created in transaction
I would like to view data in a table that is undergoing a transaction. I will use this super simple temporary table as an example:
SELECT 5 AS NUMBER INTO ##temptable
I will create a transaction to ...
- 31
1
vote
1
answer
283
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 ...
- 111
1
vote
0
answers
289
views
Can two simultaneous DB transactions interfere with each other? ACID vs SELECT ... FOR UPDATE?
This question may be considered as a follow-up question to this one.
Imagine:
a platform
that provides a service X
to book a service X, customer Y has to pay in advance
every payment is authorized ...
- 163
0
votes
0
answers
19
views
when should we use the read uncommited
I am reading the database isolation level, and I found the read uncommited isolation level. my question is: what the purpose of the read uncommited isolation level? when should we use the read ...
- 755
1
vote
0
answers
26
views
Consistency in serializable concurrent transactions
My DBS textbook says that if each transaction individually maintains consistency in the system, in the case of concurrent transactions, serializability ensures consistency. I am not able to understand ...
- 11
3
votes
1
answer
542
views
Does the SQL Transaction Log clear at the end of a loop?
I'm running SQL Server 2019 Enterprise Edition. Every month, we run a stored procedure that loads millions of records with service dates going back five years. I have 400GB of space allocated across 4 ...
- 161
2
votes
2
answers
2k
views
Is there any way to get current transaction id in SQL SERVER 2008?
I want to get current transaction id to log something in another process. The code is actually run in .net, but in SQL Server it will be similar to the following pseudo code:
BEGIN TRAN
<GET ...
- 21
1
vote
0
answers
83
views
Do the PostgreSQL pg_* catalog tables respect transactions?
Do the pg_* catalog tables and the has_table_privilege function respect transaction isolation levels? Say in a default READ COMMITTED transaction, does the below have a race condition?
SELECT
...
- 171
1
vote
3
answers
346
views
SQL or/with Nosql
I am designing a mobile app with basic functionality such as sign-up, Auth, posts, real time messages etc. I think for this kind of work Nosql should work better. But over a long period of time I want ...
- 23
15
votes
3
answers
6k
views
What is the point of TRY CATCH block when XACT_ABORT is turned ON?
Code sample with XACT_ABORT_ON:
SET XACT_ABORT_ON;
BEGIN TRY
BEGIN TRANSACTION
//do multiple lines of sql here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0) ROLLBACK;
//...
- 3,430
3
votes
1
answer
719
views
Making an index a unique index in very large MySQL table within one transaction - is the following approach safe?
For the purpose of optimizing SELECT statements I am trying to make an index UNIQUE with the following SQL statement in MySQL:
ALTER TABLE credentials DROP INDEX special_credential_id, ADD UNIQUE KEY ...
- 215
0
votes
0
answers
201
views
After a restore my transaction is slow, so I stop it, do a roll back, then re-run and it's fast...why?
On my development server I delete all data from two tables then pull fresh data from the production server via Delete From then Select Into. It's only about 100,000 rows total. On dev I run a routine ...
- 109
0
votes
0
answers
57
views
Transactional Inserts and updates in NoSQL
Let's say I'm using Database X which is NoSQL and doesn't support transactions.
1. Update A
[error] server goes down
2. Insert B
In a scenario, A is updated and suddenly our server goes down or the ...
- 57
1
vote
2
answers
999
views
InnoDB's undo log start growing after simple SELECT with READ-UNCOMMITTED level
I have a percona mysql (5.7) server with 20K QPS (lots of inserts/updates/deletes).
My question is: why issuing a simple, but long, select query (to any table) with trx isolation=READ-UNCOMMITTED ...
- 53
0
votes
1
answer
475
views
LOCK TABLES is locking current transaction
While I do some changes to a table in innodb I want to lock the table. But I can't make it work. After locking the table I can't alter the table in the same transaction
SQLSTATE[HY000]: General error:...
- 147
4
votes
1
answer
48k
views
ERROR: current transaction is aborted, commands ignored until end of transaction block SQL state: 25P02
I am trying to create a deadlock in my postgreSQL-database but the error I'm getting is not really the one I would expect:
ERROR: current transaction is aborted, commands ignored until end of ...
- 153
0
votes
2
answers
2k
views
Do select statements start a transaction in auto commit mode?
I knew that update, insert, delete start a transaction in auto commit mode, I would like to ask if select statements make their own transaction also in auto commit mode or there is no need for that ?
...
1
vote
2
answers
284
views
After server is restarted, the database and the backups are destroyed by the system crash. What ACID properties are broken in this situation?
Let's assume that two transactions execute on a database, both reading, and writing. At some time, the system crashes. After the server is restarted, the database and the backups were destroyed by the ...
- 11
0
votes
1
answer
307
views
Taking many row level locks on a rows with little contention - how does that affect PostgreSQL scalability (and RDMS in general)
I use transactions to lock on rows in my database. Generally, the number of 'things' accessing the same rows at a single time is small, however, for correctness I would still like to use transactions ...
- 101
1
vote
1
answer
1k
views
How to return values from psql to bash with custom session settings
I need to set the datestyle to "dd/mm/yyyy" before calling a procedure and getting it's return value back to bash.
I tried the following:
read return <<< $(psql \
-...
- 111
0
votes
1
answer
213
views
Insufficient privilege for read only transaction
I am using Oracle and SQL Developer and here is a problem I ran into. I have created a table called T_TEST and I am trying to create a read only transaction to it from another user. This is my ...
-1
votes
1
answer
1k
views
Perform a transaction across multiple databases at the same time
I have a system which performs many actions frequently and am looking at splitting databases up a bit to spread out the load and speed up the system hopefully.
The fear is that by splitting up these ...
- 127