transactions interview questions
Top transactions frequently asked interview questions
I'm using CHASE bank. (Also Bank of America)
and I want to get my bank transactions and my balance if I can.
Is there an API for that? in PHP or JAVA?
If so, please let me know how to get them.
Thank you!
Source: (StackOverflow)
I am currently getting this error:
System.Data.SqlClient.SqlException: New transaction is not allowed because there are other threads running in the session.
while running this code:
public class ProductManager : IProductManager
{
#region Declare Models
private RivWorks.Model.Negotiation.RIV_Entities _dbRiv = RivWorks.Model.Stores.RivEntities(AppSettings.RivWorkEntities_connString);
private RivWorks.Model.NegotiationAutos.RivFeedsEntities _dbFeed = RivWorks.Model.Stores.FeedEntities(AppSettings.FeedAutosEntities_connString);
#endregion
public IProduct GetProductById(Guid productId)
{
// Do a quick sync of the feeds...
SyncFeeds();
...
// get a product...
...
return product;
}
private void SyncFeeds()
{
bool found = false;
string feedSource = "AUTO";
switch (feedSource) // companyFeedDetail.FeedSourceTable.ToUpper())
{
case "AUTO":
var clientList = from a in _dbFeed.Client.Include("Auto") select a;
foreach (RivWorks.Model.NegotiationAutos.Client client in clientList)
{
var companyFeedDetailList = from a in _dbRiv.AutoNegotiationDetails where a.ClientID == client.ClientID select a;
foreach (RivWorks.Model.Negotiation.AutoNegotiationDetails companyFeedDetail in companyFeedDetailList)
{
if (companyFeedDetail.FeedSourceTable.ToUpper() == "AUTO")
{
var company = (from a in _dbRiv.Company.Include("Product") where a.CompanyId == companyFeedDetail.CompanyId select a).First();
foreach (RivWorks.Model.NegotiationAutos.Auto sourceProduct in client.Auto)
{
foreach (RivWorks.Model.Negotiation.Product targetProduct in company.Product)
{
if (targetProduct.alternateProductID == sourceProduct.AutoID)
{
found = true;
break;
}
}
if (!found)
{
var newProduct = new RivWorks.Model.Negotiation.Product();
newProduct.alternateProductID = sourceProduct.AutoID;
newProduct.isFromFeed = true;
newProduct.isDeleted = false;
newProduct.SKU = sourceProduct.StockNumber;
company.Product.Add(newProduct);
}
}
_dbRiv.SaveChanges(); // ### THIS BREAKS ### //
}
}
}
break;
}
}
}
Model #1 - This model sits in a database on our Dev Server.
Model #2 - This model sits in a database on our Prod Server and is updated each day by automatic feeds. 
Note - The red circled items in Model #1 are the fields I use to "map" to Model #2. Please ignore the red circles in Model #2: that is from another question I had which is now answered.
Note: I still need to put in an isDeleted check so I can soft delete it from DB1 if it has gone out of our client's inventory.
All I want to do, with this particular code, is connect a company in DB1 with a client in DB2, get their product list from DB2 and INSERT it in DB1 if it is not already there. First time through should be a full pull of inventory. Each time it is run there after nothing should happen unless new inventory came in on the feed over night.
So the big question - how to I solve the transaction error I am getting? Do I need to drop and recreate my context each time through the loops (does not make sense to me)?
Source: (StackOverflow)
What are the differences between the classic transaction pattern in LINQ to SQL like:
using(var context = Domain.Instance.GetContext())
{
try
{
context.Connection.Open();
context.Transaction = context.Connection.BeginTransaction();
/*code*/
context.Transaction.Commit();
}
catch
{
context.Transaction.Rollback();
}
}
vs the TransactionScope object
using (var context = Domain.Instance.GetContext())
using (var scope = new TransactionScope())
{
try
{
/*code*/
scope.Complete();
}
catch
{
}
}
Source: (StackOverflow)
Should you place the @Transactional in the DAO classes and/or their methods or is it better to annotate the Service classes which are calling using the DAO objects? Or does it make sense to annotate both "layers"?
Source: (StackOverflow)
In our project we're using TransactionScope's to ensure our data access layer performs it's actions in a transaction. We're aiming to not require the MSDTC service to be enabled on our end-user's machines.
Trouble is, on half of our developers machines, we can run with MSDTC disabled. The other half must have it enabled or they get the "MSDTC on [SERVER] is unavailable" error message.
It's really got me scratching my head and has me seriously considering rolling back to a home-spun TransactionScope-like solution based on ADO.NET transaction objects. It's seemingly insane - the same code that works (and does not escalate) on half of our developer's does escalate on the other developer's.
I was hoping for a better answer to http://stackoverflow.com/questions/506733/ but unfortunately it doesn't.
Here's a sample bit of code that will cause the trouble, on the machines that try to escalate, it tries to escalate on the second connection.Open() (and yes, there is no other connection open at the time.)
using (TransactionScope transactionScope = new TransactionScope() {
using (SqlConnection connection = new SqlConnection(_ConStr)) {
using (SqlCommand command = connection.CreateCommand()) {
// prep the command
connection.Open();
using (SqlDataReader reader = command.ExecuteReader()) {
// use the reader
connection.Close();
}
}
}
// Do other stuff here that may or may not involve enlisting
// in the ambient transaction
using (SqlConnection connection = new SqlConnection(_ConStr)) {
using (SqlCommand command = connection.CreateCommand()) {
// prep the command
connection.Open(); // Throws "MSDTC on [SERVER] is unavailable" on some...
// gets here on only half of the developer machines.
}
connection.Close();
}
transactionScope.Complete();
}
We've really dug in and tried to figure this out. Here's some info on the machines that it works on:
- Dev 1: Windows 7 x64 SQL2008
- Dev 2: Windows 7 x86 SQL2008
- Dev 3: Windows 7 x64
SQL2005 SQL2008
Developers it doesn't work on:
- Dev 4: Windows 7 x64,
SQL2008 SQL2005
- Dev 5: Windows Vista x86, SQL2005
- Dev 6: Windows XP X86, SQL2005
- My Home PC : Windows Vista Home Premium, x86, SQL2005
I should add that all machines, in an effort to hunt down the problem, have been fully patched with everything that's available from Microsoft Update.
Update 1:
That MSDN transaction-escalation page states that the following conditions will cause a transaction to escalate to DTC:
- At least one durable resource that does not support single-phase notifications is enlisted in the transaction.
- At least two durable resources that support single-phase notifications are enlisted in the transaction. For example, enlisting a single connection with does not cause a transaction to be promoted. However, whenever you open a second connection to a database causing the database to enlist, the System.Transactions infrastructure detects that it is the second durable resource in the transaction, and escalates it to an MSDTC transaction.
- A request to "marshal" the transaction to a different application domain or different process is invoked. For example, the serialization of the transaction object across an application domain boundary. The transaction object is marshaled-by-value, meaning that any attempt to pass it across an application domain boundary (even in the same process) results in serialization of the transaction object. You can pass the transaction objects by making a call on a remote method that takes a Transaction as a parameter or you can try to access a remote transactional-serviced component. This serializes the transaction object and results in an escalation, as when a transaction is serialized across an application domain. It is being distributed and the local transaction manager is no longer adequate.
We're not experiencing #3. #2 is not happening because there is only ever one connection at a time, and it's also to a single 'durable resource'. Is there any way that #1 could be happening? Some SQL2005/8 configuration that causes it to not support single-phase notifications?
Update 2:
Re-investigated, personally, everyone's SQL Server versions - "Dev 3" actually has SQL2008, and "Dev 4" is actually SQL2005. That'll teach me to never trust my coworkers again. ;) Because of this change in data, I'm pretty sure we've found our problem. Our SQL2008 developers weren't experiencing the problem because SQL2008 has copious amounts of awesome included that SQL2005 does not have.
It also tells me that because we're going to be supporting SQL2005 that we can't use TransactionScope like we have been, and if we want to use TransactionScope we're going to need to be passing a single SqlConnection object around...which seems problematic in situations where the SqlConnection can't easily be passed around...it just smells of global-SqlConnection instance. Pew!
Update 3
Just to clarify up here in the question:
SQL2008:
- Allows multiple connections within a single TransactionScope (as demonstrated in the above sample code.)
- Caveat #1: If those multiple SqlConnections are nested, that is, two or more SqlConnections are opened at the same time, TransactionScope will immediately escalate to DTC.
- Caveat #2: If an additional SqlConnection is opened to a different 'durable resource' (ie: a different SQL Server,) it will immediately escalate to DTC
SQL2005:
- Does not allow multiple connections within a single TransactionScope, period. It will escalate when/if a second SqlConnection is opened.
Update 4
In the interest of making this question even more of a mess useful, and just for more clarity's sake, here's how you can get SQL2005 to escalate to DTC with a single SqlConnection:
using (TransactionScope transactionScope = new TransactionScope()) {
using (SqlConnection connection = new SqlConnection(connectionString)) {
connection.Open();
connection.Close();
connection.Open(); // escalates to DTC
}
}
This just seems broken to me, but I guess I can understand if every call to SqlConnection.Open() is grabbing from the connection pool.
"Why might this happen, though?" Well, if you use a SqlTableAdapter against that connection before it's opened, the SqlTableAdapter will open and close the connection, effectively finishing the transaction for you because you now can't re-open it.
So, basically, in order to successfully use TransactionScope with SQL2005 you need to have some sort of global connection object that remains open from the point of the first TransactionScope is instantiated until it's no longer needed. Besides the code-smell of a global connection object, opening the connection first and closing it last is at odds against the logic of opening a connection as late as possible and closing it as soon as possible.
Source: (StackOverflow)
In my production error logs I occasionally see:
SQLSTATE[HY000]: General error: 1205
Lock wait timeout exceeded; try
restarting transaction
I know which query is trying to access the database at that moment but is there a way to find out which query had the lock at that precise moment?
Source: (StackOverflow)
I know there are similar questions here but they are either telling me to switch back to regular RDBMS systems if I need transactions or use atomic operations or two-phase commit. The second solution seems the best choice. The third I don't wish to follow because it seems that many things could go wrong and I can't test it in every aspect. I'm having a hard time refactoring my project to perform atomic operations. I don't know whether this comes from my limited viewpoint (I have only worked with SQL databases so far), or whether it actually can't be done.
We would like to pilot test MongoDB at our company. We have chosen a relatively simple project - an SMS gateway. It allows our software to send SMS messages to the cellular network and the gateway does the dirty work: actually communicating with the providers via different communication protocols. The gateway also manages the billing of the messages. Every customer who applies for the service has to buy some credits. The system automatically decreases the user's balance when a message is sent and denies the access if the balance is insufficient. Also because we are customers of third party SMS providers, we may also have our own balances with them. We have to keep track of those as well.
I started thinking about how I can store the required data with MongoDB if I cut down some complexity (external billing, queued SMS sending). Coming from the SQL world, I would create a separate table for users, another one for SMS messages, and one for storing the transactions regarding the users' balance. Let's say I create separate collections for all of those in MongoDB.
Imagine an SMS sending task with the following steps in this simplified system:
check if the user has sufficient balance; deny access if there's not enough credit
send and store the message in the SMS collection with the details and cost (in the live system the message would have a status attribute and a task would pick up it for delivery and set the price of the SMS according to its current state)
decrease the users's balance by the cost of the sent message
log the transaction in the transaction collection
Now what's the problem with that? MongoDB can do atomic updates only on one document. In the previous flow it could happen that some kind of error creeps in and the message gets stored in the database but the user's balance is not updated and/or the transaction is not logged.
I came up with two ideas:
Create a single collection for the users, and store the balance as a field, user related transactions and messages as sub documents in the user's document. Because we can update documents atomically, this actually solves the transaction problem. Disadvantages: if the user sends many SMS messages, the size of the document could become large and the 4MB document limit could be reached. Maybe I can create history documents in such scenarios, but I don't think this would be a good idea. Also I don't know how fast the system would be if I push more and more data to the same big document.
Create one collection for users, and one for transactions. There can be two kinds of transactions: credit purchase with positive balance change and messages sent with negative balance change. Transaction may have a subdocument; for example in messages sent the details of the SMS can be embedded in the transaction. Disadvantages: I don't store the current user balance so I have to calculate it every time a user tries to send a message to tell if the message could go through or not. I'm afraid this calculation can became slow as the number of stored transactions grows.
I'm a little bit confused about which method to pick. Are there other solutions? I couldn't find any best practices online about how to work around these kinds of problems. I guess many programmers who are trying to become familiar with the NoSQL world are facing similar problems in the beginning.
Source: (StackOverflow)
I have a .net transaction with a SQL insert to a MS SQL 2005 DB. The table has an identity primary key.
When an error occurs within the transaction, Rollback() is called. The row inserts are rolled back correctly, however the next time I insert data to the table, the identity is incremented as if the rollback never occurred. So essentially there are gaps in the identity sequence. Is there any way to have the Rollback() method reclaim the missing identity?
Am I not approaching this the right way?
Source: (StackOverflow)
What are the best practices to do transactions in C# .Net 2.0. What are the classes that should be used? What are the pitfalls to look out for etc. All that commit and rollback stuff. I'm just starting a project where I might need to do some transactions while inserting data into the DB. Any responses or links for even basic stuff about transactions are welcome.
Source: (StackOverflow)
Does anyone know of any way to list open transactions on SQL Server 2000 database?
I am aware that I can query the view sys.dm_tran_session_transactions on SQL 2005 (and later) database versions, however this is not available on SQL 2000.
Source: (StackOverflow)
Can someone provide a really simple (but not simpler than possible) explanation of a transaction as applied to computing (even if copied from Wikipedia)?
Source: (StackOverflow)
I have been investigating transactions and it appears that they take care of themselves in EF as long as I pass false to SaveChanges() and then call AcceptAllChanges() if there are no errors:
SaveChanges(false);
// ...
AcceptAllChanges();
What if something goes bad? don't I have to rollback or, as soon as my method goes out of scope, is the transaction ended?
What happens to any indentiy columns that were assigned half way through the transaction? I presume if somebody else added a record after mine before mine went bad then this means there will be a missing Identity value.
Is there any reason to use the standard TransactionScope class in my code?
Source: (StackOverflow)
I really haven't found normal example of PHP file where MySQL transactions are being used. Can you show me simple example of that?
And one more question. I've already done a lot of programming and didn't use transactions. Can I put a PHP function or something in header.php that if one mysql_query fails, then the others fail too?
I think I have figured it out, is it right?:
mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");
$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");
$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");
if ($a1 and $a2) {
mysql_query("COMMIT");
} else {
mysql_query("ROLLBACK");
}
Source: (StackOverflow)
We have client app that is running some SQL on a SQL Server 2005 such as the following:
BEGIN TRAN;
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN;
It is sent by one long string command.
If one of the inserts fail, or any part of the command fails, does SQL Server roll back the transaction? If it does not rollback, do I have to send a second command to roll it back?
I can give specifics about the api and language I'm using, but I would think SQL Server should respond the same for any language.
Source: (StackOverflow)
I have 2 commands and need both of them executed correctly or none of them execute.
So I think I need a Transaction but I don't know how to use it correctly.
What's the problem with the following script?
BEGIN TRANSACTION [Tran1]
INSERT INTO [Test].[dbo].[T1]
([Title], [AVG])
VALUES ('Tidd130', 130), ('Tidd230', 230)
UPDATE [Test].[dbo].[T1]
SET [Title] = N'az2' ,[AVG] = 1
WHERE [dbo].[T1].[Title] = N'az'
COMMIT TRANSACTION [Tran1]
GO
The insert command executed but the update command has a problem. How can I implement this to rollback both commands if any of them have an error in execution?
Source: (StackOverflow)