Voice of the DBA

Tag: syndicated

  • ACID

    What’s ACID in databases? I was asked this in an interview a long time ago and I couldn’t remember the exact meaning. For a (supposedly) senior DBA, that’s bad. I could, however, tell what it means and what it enforces in the database, I couldn’t remember the actual words.

    In building a presentation recently, I needed this, so I looked it up on Wikipedia. Isn’t everything on Wikipedia? And isn’t it true?

    In this case, they do a good job of explaining the various terms. ACID stands for

    • Atomicity
    • Consistency
    • Integrity
    • Durable

    I won’t repeat their explanations, but try to give my own take on this.

    Atomicity – If you have a transaction that makes some change, it has to all succeed or all fail. No partial transactions. That doesn’t mean that each statement succeeds, but everything wrapped in a transaction, whether 1 or 100 statements, all get completed or all rolled back.

    You can appear to muck with this by nesting transactions, but not really. SQL Server follows this rule of enforcing transactions as a complete unit or work. Either all committed, or all rolled back.
    SQLdependencytracker[1] 
    Consistency – This means that the database essentially enforces consistent change to the database from transactions, and that the database is logically consistent at all times. So references are enforced, cascades take place as part of transactions, etc. It’s a strange concept, but it really means that the database enforces all rules defined.
    Note that one funny thing here is the internal sysreferences aren’t always consistent. That one bugs me, though my employer, Red Gate Software, has a tool that finds these: SQL Dependency Tracker

    Isolation – I always get this one wrong, thinking it’s integrity, but really that’s the consistency piece. Isolation means  you cannot access data that is changed but still in an uncommitted transaction state. This essentially ensures that you get a consistent, accurate view of data. SQL Server allows you to bypass this with dirty reads, and lots of people do this to improve performance, but I think it’s a bad idea in general.

    Durability – If you have any type of failure, usually hardware, this ensures that all committed transactions can be restored. Or that uncommitted transactions are rolled back.

    SQL Server enforces this with its roll back/roll forward process when a database is started. This uses the write-ahead log to ensure that the database is in a durable state when users access the data.

  • Rights for one table

    I ran across a thread that was asking how to grant rights to a person for one table only, and not other tables.

    My response is simple:

    CREATE ROLE MySingleTableRole
    GO
    GRANT SELECT ON     dbo.MyCustomers TO MySingleTableRole
    GO
    EXEC     sp_addrolemember 'MySingleTableRole', 'Steve'

    That’s it. By default users do not have rights to any tables even if they have rights to the database. If you have a user that needs rights to one table, just grant them rights to that table.

    If the user is a member of a group that has rights to other tables, you should probably remove them from the other group/role. Then build another group for them, or for the other users. If this is the case, then your group is incorrectly being used as you have people in the group needing different permissions.

  • Top 5

    I’m back in the top 5 for the first time in a long time. I’ve been spending too much time in the forums lately, avoiding other work, but trying to answer some questions for people and help them out.

    top5

    I used to always be in the top 5, but this year it seems we’ll get a bunch of people that are working on the Question of the Day, and they’ll do a lot in a short period of time. Since those can be 1, 2, or 3 points, they can quickly amass hundreds of points in a 30 day period.

    Still, this is a good reminder to spend some time on other things, like my upcoming presentations.

  • Reading Recommendations (Twitter is cool)

    Twitter is an amazing beast. More and more I find myself getting useful information and ideas from Twitter. For someone that works at home, it’s a great window into the world outside.

    A couple days ago, a friend posted that they were looking for a science fiction book to read, and were there any recommendations. I dropped one back and didn’t think about it.

    The next day the person posted back that they liked book x, y, and z, and did anyone else have a recommendation. Four or five people chimed in and before long there was a science fiction list going back and forth in a number of tweets. A few people recommended books back to me, and I even picked up one of my own recommendations that I had read as a kid, and grabbed a sample on the Kindle App to remind me to get to it again.

    So now I have these samples on my Kindle to start reading again:

    • Daemon – Danial Suarez, and the Freedom as the next book.
    • Titan – John Varley
    • The Gunslinger (Dark Tower Series) – Stephen King, I read this a long time ago, but the recommendations from friends will have me try it again.
    • Foundation – I read this in high school or college and I recommended it, but I only read 3. There are 7, so I’ll try this one again.
    • I, Robot – Never read it. Recommended to me.
    • The Second Ship – found on an Amazon recommendation
    • Star Soldier – Another Amazon recommendation found while looking at those above.

    I’ll get to all these this year, but it’s cool to get a few recommendations.