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

learn more… | top users | synonyms

0
votes
0answers
28 views

transactions and dblink

I have 2 dbs, let's call them writedb and readdb. Readdb has a dblink to writedb and a view to a table over that dblink. I do an update on writedb to that table which view points to and then read ...
3
votes
1answer
79 views

Effects of turning the transaction safety OFF

I am considering to turn off the transaction safety to maximize the performance. Before that i am interested in finding the current status of transaction safety. Is there any command that we can get ...
2
votes
0answers
72 views

Phantom vs. Non-repeatable read [closed]

In Martin Fowler's book 'Patterns of Enterprise Application Architecture' on page 74, the authors say It's easier for databases to spot unrepeatable reads than phantoms, so the repeatable read ...
0
votes
0answers
5 views

Percona XtraDB and innodb_flush_log_at_trx_commit=2

I'm running Percona XtraDB on a three nodes cluster. Each one has this piece of configuration: innodb_flush_log_at_trx_commit=2 sync_binlog=0 I perfectly know that this is not the safest ...
0
votes
0answers
23 views

Many transactions are getting stuck in the “cleaning up” state - what could cause that?

I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. ...
-1
votes
0answers
23 views

How will concurrent transactions improve speed?

In operating systems multiprogramming improves speed because when a process waits in the waiting queue the other process can use the CPU but, in case of concurrent transaction why can't simple FCFS ...
-3
votes
0answers
33 views

Open tran for Select query [duplicate]

I have seen BEGIN TRAN on queries in my production db, but it is just a SELECT query with NOLOCK option in the transaction. Why would a simple SELECT query be placed inside of a transaction?
1
vote
1answer
36 views

MySQL/Percona Server is taking long and I'd prefer to skip all this waiting even if it means data loss

Originally posted to https://www.facebook.com/groups/GNUAndLinux/permalink/10155020539805019/ but no replies received. I run Percona Server 5.6.21-70.0-688 x64 on Ubuntu Server 12.04 LTS x64, 64GB ...
1
vote
2answers
71 views

Writing a large update/delete script that doesn't provoke locks

While running a T-SQL script that is performing massive amounts of deletes, I had the unpleasant experience of causing some sort of lock contention that prevented inserts of data in some tables for ...
2
votes
2answers
90 views

Error on DDL statement never invokes CATCH

When I try to close the SS Management Studio window below is the error I understand that DDL statements are implicit COMMIT and the below WARNING message is because BEGIN TRAN never COMMIT or ...
1
vote
0answers
20 views

MySQL - Transactions and causality

I'm new to MySQL's LOCK and TRANSACTION and have a question about different isolation levels of a transaction: Given two transactions executing the same code (auto-commit is false): (1) get data of ...
1
vote
1answer
34 views

Why “SET LOCAL statement_timeout” does not work as expected with PostgreSQL functions?

My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works ...
4
votes
2answers
227 views

How to rollback when 3 stored procedures are started from one stored procedure

I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful. If the first stored procedure works fine in the ...
0
votes
0answers
62 views

How can I lock an INSERT operation

OK, I know the title is confusing, let me rephrase with a description of the problem. The environment is SQL Server 2012. I have a table with Customers Id 1 And a table with their transactions ...
1
vote
2answers
82 views

Truncating and inserting in a safe transaction on MySQL

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table. I wanted to make sure that if any statement in the transaction failed, the data will be untouched. I can't have an empty or ...
1
vote
1answer
63 views

Deadlock detected on CREATE TABLE in PostgreSQL

I'm creating multiple tables asynchronously when I get deadlock detected. Both tables (Table A & B) have FK to one table (Table C) and I get deadlock on relation to Table C. Scripts CREATE TABLE ...
3
votes
2answers
60 views

Transactions within a Transaction

What behaviour would PostgreSQL display if for example the script below were called BEGIN; SELECT * FROM foo; INSERT INTO foo(name) VALUES ('bar'); BEGIN; <- The point of interest END; Would ...
2
votes
3answers
165 views

Transaction Isolation level different threads massive number of Writes

I have a lot of threads that writing to DB some data in two tables. tbl_raw_data and tbl_parsed_data where tbl_parsed_data have foreign key to tbl_raw_data. I also need the writing to be superfast. ...
1
vote
2answers
132 views

Using transactions in sqlplus

I'm coming to oracle from postgresql. In postgresql, I can use the command-line tool, psql, to execute a delete or update in a transaction: dbpool.production=> begin; BEGIN ...
-2
votes
2answers
45 views

Explicit transactions for each DML query are necessary or not? [closed]

My question is simple: Which mechanism should I use in databases to ensure that if device shuts down in middle, the process is unified. transactions?
1
vote
3answers
35 views

Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM [closed]

For my next module in my masters, I am planning on studying Advanced Databases. (Long story short, the masters is designed for people with NON-CS degrees, so I have never studied databases, therefore ...
0
votes
2answers
29 views

Best way to update the content of large lookups tables

In our application we have a about 20 tables that contain lookup information (read only data that the application doesn't modify). Nightly we'd like to update this information by deleting all the ...
1
vote
1answer
23 views

Does trigger “see” all changes done in the transaction it was fired from?

Let me elaborate on the title. Suppose we have two tables, t1 and t2. t2 has a trigger t2_trgon it. Same transaction inserts/updates data in t1, then does insert/update in t2. t2_trg is fired and ...
0
votes
1answer
18 views

Oracle dataguard - Redo apply

In a low bandwidth environment when a transaction commits that generates a large amount of redo data will cause other independent transactions to slow down. This is because the redo data of small ...
1
vote
2answers
158 views

Using MySQL/PHP PDO, is there a 'simulate' or dry run option?

Does MySQL (or, ideally, PHP PDO) have some way of simulating transactions without actually committing them? (Can I get MySQL or PDO to act as if data is inserted/deleted/updated, without actually ...
2
votes
2answers
274 views

Separate table for 'scheduled payments' and 'completed payments'?

In a previous financial payments related database I'd worked on, it just had one large transactions table. We would insert scheduled transactions for the future, and when that time came, we'd execute ...
2
votes
1answer
88 views

How to find the active transactions in VLF?

Is there a way to find the T-SQL statement that is part of the active transaction that is blocking the reuse of the transaction log file VLFs? I am performing tlog backups on every 15 minutes, but ...
1
vote
1answer
48 views

Print, transaction and cursor performance in Transact-sql

I'm having some performance issues when I run a script. I was reading about the problem with print and its performance, but I cannot figure out how to reduce the impact. The algorithm is O(n^2). The ...
2
votes
2answers
144 views

Transaction Log Backups

I am working on backup+recovery scenario and I came across a situation where I could not figure out how I would have to deal with it. We have couple databases where sometimes there may not be any ...
4
votes
1answer
436 views

Sql Server - Select while insert on another transaction gives unexpected results

I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it. Let's say I have a table like this: ...
1
vote
1answer
42 views

Transaction Rollback

I have a purging process that runs to delete old data from table to avoid table growth. My delete statement would be a simple delete based on join something like below Delete FROM TABLE_A A JOIN ...
6
votes
1answer
84 views

In Postgres, how to get a list of current defined savepoint?

I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection. To be more precise: I'd ...
1
vote
1answer
282 views

PostgreSQL 9.3: Primary key violated by a trigger INSERT

My problem Consider a table t with many frequent updates from users, from which only the last few are relevant. In order to keep the table size reasonable, whenever a new row is inserted old rows ...
0
votes
2answers
27 views

transaction not processing?

I'm trying to test out an update prior to running it on our production server, however when running my transaction in sqlplus, I don't seem to be able to commit the transaction or get any data back ...
0
votes
1answer
86 views

Is is possible to handle a UserAbort or ClientTimeOut within a Stored Procedure?

An example: create proc [dbo].[usp_SlowProc] as begin try set xact_abort on; -- Ensures rollback on some errors that bypass the try/catch mechanism raiserror('Waiting...', 0, 1) ...
1
vote
0answers
70 views

firebird update very slow

I am writing a stored procedure for Firebird 2.5 in which I sometimes need to update a single row in a data table more than once in the same transaction. However, I have noticed that the performance ...
3
votes
1answer
76 views

Use of Trigger After a Transaction

I have a scenario where there is a transaction being executed to update a few tables. On one of these tables there is a trigger that accesses the data on those tables, but is only planned to be on ...
4
votes
2answers
910 views

Is it possible for SQL Server to miss rows on a select * where PK = “value” query

We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment. I have a very strange error in our application and I was ...
1
vote
1answer
90 views

How to log within a stored procedure that is encapsulated within a transaction

I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I ...
1
vote
1answer
114 views

Postgres : Executing SELECT within a transaction does not return most recent rows

Given a table layout that looks like this inv_items ========= * id bigserial sku character varying(22) name character varying(32) ... inv_items_stock =============== item_id bigint (inv_items) qty ...
2
votes
1answer
38 views

MySQL InnoDB transactions with lot of delete instructions

We have a process that runs frequently and does the following steps on a mysql database of type innodb. SET autocommit = 0; DELETE FROM table WHERE category='something'; -- every 300 rows or whatever ...
1
vote
1answer
38 views

SQL Server: affect other transactions?

I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example: BEGIN TRY BEGIN TRAN ...
2
votes
0answers
283 views

Postgresql trigger - get all changed rows after transaction

I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction. CREATE ...
3
votes
2answers
274 views

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE ...
1
vote
0answers
48 views

Transactions using Isolation Level on databases with FileStream

We have a highly transactional application. We have a requirment to upload huge data into our database that can happen for a duration of 5-10 minutes. During our upload time, the tables are getting ...
0
votes
1answer
106 views

Is UDPLOCK using Row-locks?

I have a UPDATE statement that is using a UPDLOCK inside a DTCTransaction to avoid deadlocks. Now I was wondering. is it locking on a Row-level or on a Table-level?
0
votes
0answers
73 views

Transaction log backups during index maintenance

We have a database with large and complex files and fragmented indexes. The environment is SQL 2008 Full recovery model and Mirrored which limits me to reorganizing. The backup strategy is Full ...
2
votes
1answer
41 views

Reading from subscriber during Transactional Replication

When using Transaction Replication, are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it? i.e. can selects be made against tables to which ...
7
votes
1answer
662 views

Synchronization using triggers

I have a requirement similar to previous discussions at: Writing a simple bank schema: How should I keep my balances in sync with their transaction history? Trigger in combination with transaction ...
0
votes
0answers
137 views

Avoid transaction rollback when Deadlock is encountered (mysql)

I'm currently trying to handle concurrent inserts correctly using MySQL, I'm basically doing the following: CREATE TABLE foo (id INT NOT NULL PRIMARY KEY) ENGINE=INNODB; CREATE TABLE bar (id INT NOT ...