comparing databases and their locks

Question

I have heavy transaction stuff going on and would like to gain information about how locks are implemented in current databases. Working on zero budget my choise is limited to mysql 5.5 and postgres 9.0.

Is there any site where the locks are compared?

From literature I know that you can have read-only and read-write locks and that a good way of handling locks is to block the the path to the data. That means blocking parts of the btree. But I can't find specifics on how these to databases do their work.

Thanks alot.

score 2 · Answer 1 · 2011-02-28T21:49:24.463

2

Here is an overview for PostgreSQL

http://www.postgresql.org/docs/current/static/explicit-locking.html
http://www.postgresql.org/docs/current/static/locking-indexes.html

Not sure what you mean with "read-only lock", but in PostgreSQL the only way to "lock" a table from being read is to manually lock it using ACCESS EXCLUSIVE mode which is not something that happens with regular DML statements. Only a DDL statement (such as ALTER TABLE) would achieve this.

edited Feb 28 '11 at 21:49

answered Feb 28 '11 at 19:00

What you have said is a bit ambiguous because according to your first link `lock table` can "guarantee that the holder is the only transaction accessing the table in any way" - I guess you mean there is no row-level lock that will block reads? – Feb 28 '11 at 21:31
@JackPDouglas: you are right. I was referring to row level locks. – Feb 28 '11 at 21:47
Here's the MySQL manual on innodb table locking: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html Note that how myisam locks it largely uninteresting since it uses the "one big lock" method. For PostgreSQL and Innodb locking, the transaction isolation level is important, which brings up the question of just what about locking are you interested in? Performance, isolation, predicate locking? – Scott Marlowe Jan 18 '13 at 06:38

score 0 · Answer 2 · edited Jan 18 '13 at 06:14

0

Maybe you'll find this book useful: Inside Microsoft SQL Server 2005: The Storage Engine. Read chapters "logging and recovery" and "locking and concurrency". A lot of information from this book is applied to many of today's database systems. It's really good book.

I suggest you to read about concurrency control. You can start from Concurrency_control wiki especially as of section |Database transaction and the ACID rules"

If you wanna compare locks, first I would read about problems occuring in transactions and about transaction isolation levels.

edited Jan 18 '13 at 06:14

Chuck Burgess

11,600
5
41
74

answered Feb 28 '11 at 18:16

Marcin Krupowicz

536
6
16

Thanks alot for this hint. I've already got that knowlegde (or if not I've got an awesom book lying right next to me) but I can't find any specifics on these two databases. I only need a blank list what kind of locks are supported, no explaining needed... – hans.g Feb 28 '11 at 18:23

score 0 · Answer 3 · edited Jan 18 '13 at 06:14

0

Check this: SQL Server 2008 Locking.

edited Jan 18 '13 at 06:14

lmcanavals

2,339
1
24
35

answered Feb 28 '11 at 19:08

The Dag

1,811
16
22

comparing databases and their locks

3 Answers3