Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Question on ACID properties Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 9:39 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 9:50 AM
Points: 1,458, Visits: 2,435
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.
Post #1358066
Posted Wednesday, September 12, 2012 10:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1358092
Posted Wednesday, September 12, 2012 10:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:55 PM
Points: 22,492, Visits: 30,199
Here is one place to start:

http://databases.about.com/od/specificproducts/a/acid.htm



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1358096
Posted Wednesday, September 12, 2012 10:31 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 9:50 AM
Points: 1,458, Visits: 2,435
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.
Post #1358107
Posted Wednesday, September 12, 2012 2:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:30 PM
Points: 35,959, Visits: 30,251
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1358202
Posted Wednesday, September 12, 2012 9:35 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 9:50 AM
Points: 1,458, Visits: 2,435
.. Thank you Jeff..
Post #1358318
Posted Friday, September 14, 2012 6:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 1,381, Visits: 2,004
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.
Post #1359203
Posted Friday, September 14, 2012 6:47 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 9:50 AM
Points: 1,458, Visits: 2,435
Hey thanks... We get a take off now :)

Anyone thinks anything else.. ?

Thanks agaian.
San.
Post #1359232
Posted Wednesday, September 19, 2012 2:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1361631
Posted Wednesday, September 19, 2012 3:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1361648
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse