A mechanism for committing a consistent set of changes into a database atomically.
2
votes
2answers
68 views
How to rollback the identity seed after deadlock
Now that's an approximate sequence of operations Im performing:
SET IDENTITY_INSERT <table-name> ON;
INSERT SOMETHING to <table-name> with explicitly specifyed id
DECLARE @oldID bigint
...
3
votes
3answers
96 views
sleeping SPID blocking other transactions
I'm really having trouble tracking down some blocking we are experiencing.
The root blocking SPID's status is 'sleeping', the cmd is 'AWAITING COMMAND', and the sqltext is 'SET TRANSACTION ISOLATION ...
0
votes
1answer
60 views
Lock wait time out exceed restart transaction
I had got error on the lock wait time out so below I got 3 samples taken. One I took before the increase innodb_lock_wait_timeout to 120. So is there anything else I must tweak based on the logs ...
1
vote
2answers
43 views
Read Committed Isolation Level
Quote from docs:
Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data ...
1
vote
0answers
32 views
Does SQL Server place shared locks on scanned records when using REPEATABLE READ [duplicate]
Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ.
In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. ...
0
votes
1answer
50 views
How do I stop a query from running if the row is locked?
I have a section of code that locks the database, updates it and then confirms.
This is all working fine, if another user attempts to update the same row they cannot and their changes are discarded.
...
0
votes
1answer
39 views
postgres deadlock without explicit locking
I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock ...
0
votes
1answer
59 views
Database atomic operations implementation
The question is about queries that are not wrapped in 'begin-commit' block, but about plain inserts and updates that are atomic in PostgreSQL, MySQL (innodb engine at least). So how is this ...
4
votes
1answer
90 views
Transactions, references and how to enforce double entry bookkeeping? (PG)
Double entry bookkeeping is
a set of rules for recording financial information in a financial
accounting system in which every transaction or event changes at least
two different nominal ...
2
votes
1answer
196 views
unique constraint violated (ORA-00001) in concurrent insert [closed]
I have a procedure that is called from concurrent transactions:
//Some actions here
INSERT INTO table1
(table1_id, table1_val1, table1_val2, table1_val3)
VALUES
...
1
vote
1answer
83 views
Does PostgreSQL optimize queries in transaction?
In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, ...
0
votes
1answer
91 views
Query notifications and connection options
I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition.
.NET errors show a problem reported from SqlNotificationInfo ...
3
votes
1answer
180 views
Trigger in combination with transaction
Suppose we have the following situation:
We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A.
...
0
votes
0answers
29 views
Examples of a database with no consistency measures that ensures consistency through schema alone
To clarify, the database can be any type RDBMS, NoSQL/BigTable. However, it must not employ any usual consistency measures such as locks, or synchronization, but the inconsistency-invincibility must ...
0
votes
0answers
40 views
What would this non-'unique index with a unique search condition` query lock?
I am not sure about this part of the transaction manual
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
0
votes
1answer
52 views
Do I need a locking read?
I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The ...
1
vote
1answer
74 views
Postgresql Two-phase Commit Transaction Identifier
Is it possible to perform a two-phase commit in Postgresql when the databases exist on the same server?
As a simple test, I would connect to the first database and run:
BEGIN;
PREPARE TRANSACTION ...
0
votes
1answer
57 views
Are MySQL transactions under-utilised?
I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related.
Scenario : Suppose I have a series of queries which creates a user ...
0
votes
1answer
147 views
Does TRUNCATE TABLE affects isolation level in Sql Server?
As I am working on the project which has SQL SERVER as a database....I'm maintaining isolation level in my project where I enabled snapshot isolation level for my project....
As I have to make some ...
1
vote
1answer
43 views
Does a transaction cover the subroutines?
When I begin transaction and exec a stored procedure, does that transaction cover the changes made by the stored procedure I executed? Would a rollback transaction cancel the changes made by the ...
1
vote
1answer
108 views
Commit Insert but Hold Lock
I have implemented what is effectively a reliable queue through the following:
CREATE TABLE Queue (MessageID UNIQUEIDENTIFIER, Message varchar(255))
--Enqueue Command
DECLARE @MessageID ...
2
votes
1answer
128 views
How to scale MySQL transaction wrapped queries?
So I'm working on an app that wraps many of it's SQL queries in a transaction for it's SQL usage and I'm trying to think of some ways to scale the DB layer up....
Up until now we've been throwing ...
1
vote
1answer
212 views
SQL Server - how transactions and transaction log work (simplified)
I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model.
Now, everything ...
3
votes
1answer
107 views
SQL Server 2008 - Adding a column and a filtered index within a single transaction?
This is more of a general question. Why can you not add a filtered index within the same transaction scope as adding the new column in the first place? The solution is of course simple, just make two ...
3
votes
2answers
314 views
Transaction and Try-catch in SQL Server Job
We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH ...
4
votes
2answers
166 views
Delete all rows within transaction: what would happen to other transactions?
I currently have a function in PostgreSQL that creates a temp table, fills it, performs some tricky selects and returns results. postgresql's temp tables are local to functions, so this helps me avoid ...
3
votes
1answer
375 views
Deleting MySQL table with pending transactions
Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)?
What happened:
I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
9
votes
1answer
601 views
SQL query for combinations without repetition
I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n.
Extended sample input and result so input ...
2
votes
1answer
94 views
Transaction level difference between using a large IN filter VS. “BEGIN TRAN/COMMIT”
I would like to delete about 100,000 records with minimal server overhead. I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here. Which would ...
0
votes
1answer
108 views
jdbc: does setAutoCommit(true) commits past executions?
I would like to execute some statements, and commit the entire batch at the end. I've tried the following approach:
connection.setAutoCommit(false); // Don't commit for each statement
...
2
votes
0answers
618 views
Table Lock on INSERT - How to optimize
we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is ...
2
votes
1answer
92 views
Add user to Server Role in a transaction
Situation
One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning ...
3
votes
2answers
370 views
How do I implement insert-if-not-found for transactions at serializable isolation level?
I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following.
We have a table called artist with 2 columns, (name, id) where name is the ...
2
votes
1answer
283 views
Rollback generate scripts
During the development of applications users often want to add new modules, business rules etc. So I've often used SQL Server 2008 option to create scripts from the development DB to generate new ...
0
votes
1answer
216 views
MYSQL MyISAM data recovery implementation
I am creating integration into eshop script, shop is using MyISAM tables and I need to implement something like transactions in InnoDB, what I actually need is full recovery of data after modifying or ...
1
vote
0answers
65 views
migrations within transactions
I have a bash script to run migrations on our MySQL databases.
Is it possible to wrap migrations within a single transaction when running multiple migration scripts from the command line?
...
1
vote
1answer
216 views
Does TRANSACTION/COMMIT/ROLLBACK only work on certain MySQL engines such as InnoDB?
Are transaction functions only relevant for particular database engines on MySQL, or are transactions supported under all MySQL engines, including MyISAM and InnoDB?
someone says here that ...
4
votes
3answers
227 views
Can I change table structure in a transaction and then roll it back if there is an error?
I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
1
vote
1answer
812 views
MySQL Insert into two tables using new IDs
I'm trying to select data already in the database and insert it into two tables using the generated ID from table one.
table1 contains configuration options, table2 contains pricing data. Table2 uses ...
0
votes
0answers
85 views
Being careful about additional records being added in a duplicate record merge?
We have a multi-user MS-Access 2003 database. Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit. I've ...
1
vote
2answers
426 views
mysql transaction vs lock
I have a question regarding MySQL locking and transaction management.
My Question is ..does mysql takes lock on the the tuple/table on which I am doing select/update in a transaction ?
3
votes
1answer
135 views
SQL Server : KEY lock prevents FK on nearby row, but not SELECT [closed]
I have the following table structure (simplified, pseudocode):
TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY)
TABLE dbo.Transfers (
Id int NOT NULL IDENTITY PRIMARY KEY,
...
3
votes
2answers
1k views
Will a delete then insert within a transaction cascade?
I had a question about what the expected and actual behaviour for the following scenario is.
The scenario is one where a database table ([table1]) is cleared and reloaded every day. The table has an ...
0
votes
1answer
193 views
Can I rollback/abort a transaction if no rows were inserted?
I have a transaction in which I want to do something like this:
START TRANSACTION;
INSERT INTO table1 (...)
SELECT ... FROM table 2;
... if select count(*) from table1 returns 0 abort/rollback ...
...
3
votes
1answer
1k views
MySQL transaction size - how big is too big?
I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.
There are many aspects, and the imports may yield anywhere between ...
0
votes
0answers
54 views
Biztalk + BI = where? [closed]
I have heard that you can use Biztalk in to transfer data to the Business intelligence.
Exactly where do you put the Biztalk's integration in relation to the architecture of Business Intelligence?
9
votes
1answer
265 views
Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state
Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
2
votes
1answer
277 views
In InnoDB, does a Transaction imply any implicit locking of a table?
The Isolation level is REPEATABLE_READ.
The logic is as under:
Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction and exit
If Table A has No Data, Proceed further
...
1
vote
1answer
106 views
timestamp based concurrency
I am wondering if someone could help me out with a question I have about time stamp based concurrency.
I found the following practice problem on a website
It basically asks which of the following ...
1
vote
1answer
526 views
Transaction Lock Timeouts When Updating a Row
I'm trying to determine the cause of an InnoDB transaction lock timeout. This one is very strange in that it appears to be a deadlock error instead - nonetheless, here's the output of my slow log ...
