September 12, 2012 at 9:39 am
Experts,
One of the interviewer asked me something like below question. It was not clear to me anyway.
"When we get an SQL query how do we determine if it meets ACID properties or not ?". Since it was telephonic I din't hear it properly as well.
Can anyone tell me What should be the answer ?.
Thanks in advance.
San.
September 12, 2012 at 10:05 am
Joy Smith San (9/12/2012)
Experts,One of the interviewer asked me something like below question. It was not clear to me anyway.
"When we get an SQL query how do we determine if it meets ACID properties or not ?". Since it was telephonic I din't hear it properly as well.
Can anyone tell me What should be the answer ?.
Thanks in advance.
San.
There is no "right" answer. The interviewer was trying to determine more than that question. They wanted you to explain your understanding of ACID and how it relates to a query. Do you know what ACID is? If you don't then start there.
If I were asked this question I would have responded with a question for clarification because the question as you posted it is entirely too vague to provide a decent answer. What type of query is it? What is the context it is being used?
Honestly I don't really quite understand how a query can be held up against the ACID test.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2012 at 10:10 am
Here is one place to start:
September 12, 2012 at 10:31 am
Thanks for the quick responses. As I told even I was not clear.
Yes, I know ACID properties. Just confused with the question.
However would it be ideal if I say,
1. A transaction should be opened with "Begin Tran".
2. Ensure that correct values are updated in the tables.
Thanks again.
San.
September 12, 2012 at 2:15 pm
Joy Smith San (9/12/2012)
Thanks for the quick responses. As I told even I was not clear.Yes, I know ACID properties. Just confused with the question.
However would it be ideal if I say,
1. A transaction should be opened with "Begin Tran".
2. Ensure that correct values are updated in the tables.
Thanks again.
San.
No. Begin Tran is not necessarily required for ACID properties especially when "implicit transactionns" is turned on (the default in SqL Server). And even incorrect values beinng updated in tables can meet "ACID" properties.
Look up "ACID Properties" in Books Online and read all 3 articles that are returned on the subject.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2012 at 9:35 pm
.. Thank you Jeff..
September 14, 2012 at 6:09 am
I would go as some said about the interviewer would like to hear about your ACID knowledge but in a case where he would absolutely like something:
Running some concurrent queries with NOLOCK or READ UNCOMMITTED isolation level could break the ACID property. (The I one)
From wikipedia:
The isolation property ensures that the concurrent execution of transactions results in a system state that could have been obtained if transactions are executed serially, i.e. one after the other.
Because of page split you might obtain a different result than having those requests done sequentially.
I'm sure there's other answers also but this one that came up my mind.
September 14, 2012 at 6:47 am
Hey thanks... We get a take off now 🙂
Anyone thinks anything else.. ?
Thanks agaian.
San.
September 19, 2012 at 2:59 pm
The question definitely has layers to it as Sean alluded to. They were hoping to learn various things about you from it.
I would have picked apart and addressed each part of the acronym.
For A: Unlike Oracle, in SQL Server if you want each statement to implicitly start a new transaction then you must SET IMPLICIT_TRANSACTIONS ON. The default in SQL Server for "implicit transactions" is actually OFF, i.e. "autocommit mode", meaning if you want to start a transaction you must issue a BEGIN TRAN to start it. All statements are still atomic (the A in ACID) regardless of the setting meaning they either succeed or fail completely. An example would be if you issue an UPDATE statement to set all values of a TINYINT column to itself plus 1 and one of the rows had a value of 255 the entire UPDATE would fail, i.e. no rows would be updated.
For I: Megistal nailed it.
For C & D: You could check the results of the query from different sessions, and multiple times to make sure the same results were returned, which you mentioned.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 3:17 pm
opc.three (9/19/2012)
For A: Unlike Oracle, in SQL Server if you want each statement to implicitly start a new transaction then you must SET IMPLICIT_TRANSACTIONS ON. The default in SQL Server for "implicit transactions" is actually OFF, i.e. "autocommit mode", meaning if you want to start a transaction you must issue a BEGIN TRAN to start it.
A data modification statement run without an explicit transaction and without implicit transactions on still runs in a transaction, it's just automatically started and committed (triggers run within the automatically created transaction). You can see this if you query @@trancount in a trigger, the transactions DMV while the query is running or by looking at the log records written.
Implicit transactions just means that SQL automatically starts the transaction (assuming there isn't one open), but does not commit it
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2012 at 3:22 pm
GilaMonster (9/19/2012)
opc.three (9/19/2012)
For A: Unlike Oracle, in SQL Server if you want each statement to implicitly start a new transaction then you must SET IMPLICIT_TRANSACTIONS ON. The default in SQL Server for "implicit transactions" is actually OFF, i.e. "autocommit mode", meaning if you want to start a transaction you must issue a BEGIN TRAN to start it.A data modification statement run without an explicit transaction and without implicit transactions on still runs in a transaction, it's just automatically started and committed (triggers run within the automatically created transaction). You can see this if you query @@trancount in a trigger, the transactions DMV while the query is running or by looking at the log records written.
Implicit transactions just means that SQL automatically starts the transaction (assuming there isn't one open), but does not commit it
Right, it is autocommited by default.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 3:55 pm
@gilamonster, I came back to re-read because I was thinking I may have missed something you were picking up in my description. I think see what you were expanding on. It is illustrative to think from the perspective of being in a trigger where we are able to operate while 'inside the transaction' in a sense, and that ability is unchanged by implicit or explicit transactions in terms of the view of the database we have access to.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 20, 2012 at 1:34 am
When I initially read your reply, it seemed to say that without implicit transactions or begin tran, there was no transaction, which is of course not true, so just wanted to clarify in case anyone else read it that way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2012 at 12:09 pm
The other possible query I can think of is SELECT INTO. This wont guarantee Atomicity and Consistency as the INTO table might still be created even if the transaction was rolled back. I think this is a SQL Server design fault (correct me if I am wrong) rather than a choice we have with NOLOCK to violate Isolation.
Other than the above, because of implicit transactions, write ahead logging and the locking manager, all queries should always conform to the ACID rules when they are commited.
I have read in Paul NielNielsen Server bible 2008 that in the simple recovery model transactions wont guarantee the durability principle?
"The simple recovery model is suitable for databases that require each transaction to be atomic, but not necessarily durable. The simple recovery model directs SQL server to truncate the transaction on checkpoints" - p956
Can someone please tell me if I am missing something? Is wirte ahead (and eager write in the case of bulk logged transactions) not implemented to guarantee durability in all recovery models? I know point in time recovery will not be possible, but in the case of the simple recovery model, is hardened transactions not only truncated for committed transctions?
September 20, 2012 at 12:15 pm
The other possible query I can think of is SELECT INTO. This wont guarantee Atomicity and Consistency as the INTO table might still be created even if the transaction was rolled back
Where did you find this? I don't think that is true at all. The rollback would remove the table.
begin transaction
select 5 as Col1 into SomeSillyTable
rollback transaction
select * from SomeSillyTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply