A mechanism for committing a consistent set of changes into a database atomically.
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 ...
2
votes
1answer
369 views
Rotate a table in PostgreSQL
Problem description
I need to rotate (with DROP and CREATE) a table which is heavily used by other clients.
At present, I have a program which replaces (DROP + CREATE) this table.
Sometimes, just ...
2
votes
1answer
170 views
select for update gives error on indexed column
here is my code:
mysql_query("start transaction");
$q="select max(id) from test for update";
$r=mysql_query($q);
$row=mysql_fetch_array($r);
for ($k=0;$k<100000;$k++)
{
$q="insert into test values ...
2
votes
1answer
354 views
Locking, deadlocking workaround while process data at the same table
My database has a table: tableX.
Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate.
At the ...
2
votes
1answer
354 views
SQL Server Logon Trigger causing problems with the Distributed Transaction Coordinator
I've created the following simple logon trigger on my SQL Server 2005:
CREATE TRIGGER LOGON_RESTRICTION ON ALL SERVER
FOR LOGON
AS
BEGIN
PRINT 'Hello World'
END
GO
Which seems to be working fine ...
2
votes
2answers
217 views
What is the correct term to describe a “persistent transaction”?
I'm kind of a the administrator of a zero-administration OODBMS, but mostly I do programming.
I remember reading years ago about some SQL database that had a concept of something like a permanent ...
2
votes
1answer
199 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
...
2
votes
0answers
623 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 ...
1
vote
1answer
214 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 ...
1
vote
1answer
252 views
Difference between Flashback Query and Serializable Transaction Mode?
Is there any conceptual difference between a SERIALIZABLE Transaction and a Flashback Query?
1
vote
2answers
44 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
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
110 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 ...
1
vote
2answers
667 views
SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed?
We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.)
Both databases are identical with many foreign-key constraints on tables.
...
1
vote
1answer
407 views
Is it possible to write an SQL query that automatically reconciles/“transactionalizes” payments and charges
I'm still working on the project mentioned here (http://dba.stackexchange.com/questions/2428/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system).
The system is to give the ...
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, ...
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 ...
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
527 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 ...
1
vote
1answer
84 views
Overlapping Transactions
Apolgies in advance if this has been discussed before, or is even fundamental enough for it to be outlined on, say, Wikipedia. I just felt that asking would be more efficient!
Anyway, I want to ask ...
1
vote
1answer
75 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 ...
1
vote
1answer
222 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 ...
1
vote
2answers
427 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 ?
1
vote
1answer
110 views
Unit testing with InnoDB transactions
So, I'm running some unit tests against some InnoDB tables in MySQL.
Each unit test is wrapped in a transaction:
setUp:
SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
...
1
vote
2answers
135 views
INSERT statements in a transaction and locking a range of rows
I have a table that is concurrently accessed by many users.
Typically, INSERTs to the table are made one at a time. Occassionaly, users will add a "batch" of rows at the same time. The batch is ...
1
vote
2answers
848 views
Distributed Transactions between SQL Server 2000 & MySQL Stopped Working
We have been successfully copying data from a MySQL Slave database into a SQL Server 2000 database. The MySQL server is a linked server. I have tried using v3.51 and v5.1.8 of the ODBC connector ...
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. ...
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?
...
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 ...
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 ...
0
votes
1answer
110 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
...
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
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
72 views
Transaction question
I have a question.
c stands for Commit, a stands for Abort, T is a transaction (db Transaction)
Is the following sentence true?
either c or a belong to T
In my opinion the sentence is true since ...
0
votes
1answer
1k views
how to make a table unlocked within transaction block
I am using SQL Server 2008. I have several stored procedures that process different reports. Each stored procedure makes use of Begin Transaction' and 'Commit Transaction, using the default ...
0
votes
1answer
62 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 ...
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 ...
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 ...
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 ...
...
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
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 ...
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
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 ...
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?
